Slow Query Log #

Background #

The slow query log feature is used to log SQL statements that takes longer to execute, which is easy for DBAs and developers to identify potentially problematic SQL statements and is an important reference for database and SQL management.

Parameters #

• slow_query_log: Whether to enable the slow query log function, the default value is true.
• long_query_time: The slow query time threshold at which SQL statements that take longer to execute are recorded in the slow query log. The configuration unit is milliseconds (ms) and the default value is 5000.

Requirements #

The slow query log feature is based on Agent technology and is only available for ShardingSphere-Proxy scenarios where Agent is enabled.

Sample #

Because the slow query log is agent-based, the following configuration is located in agent.yaml:

plugins:
  Logging:
    props:
      # Whether to enable general query log.
      # general_query_log: true
      # Whether to enable slow query log.
      slow_query_log: true
      # Long query threshold, in milliseconds.
      long_query_time: 5000

Where slow_query_log and long_query_time are configured with default values, meaning:

1. Enable slow query logs.
2. When SQL execution takes more than 5000 milliseconds, record slow query logs.

Slow Query Log Format #

The format of the slow query log is as follows:

timestamp: {time} db: {db database user: {user} host: {host} query_time: {query time}
{sql}

• timestamp: The time at which the log records were generated;
• db: Database name.
• user: The name of the user used in the current connection;
• host: Client access address;
• query_time: SQL execution time, the unit is ms;
• sql: An SQL statement that is sent by the client when a slow query occurs.

For Example:

timestamp: 2022-07-01 00:00:00.000 db: sharding_db user: root host: 127.0.0.1 query_time: 159
CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)

Related References #

Observability