All Products
Search
Document Center

Slow SQL statements

Last Updated: Aug 14, 2020

Distributed Relational Database Service (DRDS) provides the following statements for you to query slow SQL statements:

SHOW [FULL] SLOW [WHERE expr] [limit expr]

The SQL statements that take more than 1 second to execute are slow SQL statements. Slow logical SQL statements are the slow SQL statements sent from an application to a DRDS instance.

  • SHOW SLOW: queries the 100 slowest logical SQL statements since the DRDS instance was started or CLEAR SLOW was last executed. Note that the records on the 100 slowest logical SQL statements are cached in the DRDS system and are lost when the DRDS instance is restarted or CLEAR SLOW is executed.
  • SHOW FULL SLOW: queries all the slow logical SQL statements that are recorded and persisted to the built-in database of the DRDS instance since the DRDS instance is started. The maximum number of records is specified in the specifications of the DRDS instance. The DRDS instance scrolls to delete the earliest slow SQL statements. If the specifications of the DRDS instance is 4-core 4 GB, a maximum of 10,000 slow SQL statements can be recorded. If the specifications of the DRDS instance is 8-core 8 GB, a maximum of 20,000 slow SQL statements can be recorded. Both slow logical SQL statements and slow physical SQL statements are counted. The same rule applies to other specifications.

Important columns:

  • HOST: the IP address of the host from which the SQL statement is sent.
  • START_TIME: the time when the SQL statement started to be executed.
  • EXECUTE_TIME: the time that the DRDS instance takes to execute the SQL statement.
  • AFFECT_ROW: the number of affected rows for DML statements or the number of returned records for query statements.
  1. mysql> show slow where execute_time > 1000 limit 1;
  2. +-----------+---------------------+--------------+------------+-----------+
  3. | HOST | START_TIME | EXECUTE_TIME | AFFECT_ROW | SQL |
  4. +-----------+---------------------+--------------+------------+-----------+
  5. | 127.0.0.1 | 2016-03-16 13:02:57 | 2785 | 7 | show rule |
  6. +-----------+---------------------+--------------+------------+-----------+
  7. 1 row in set (0.02 sec)

SHOW [FULL] PHYSICAL_SLOW [WHERE expr] [limit expr]

The SQL statements that take more than 1 second to execute are called slow SQL statements. Slow physical SQL statements are the slow SQL statements sent from a DRDS instance to an ApsaraDB RDS for MySQL instance.

  • SHOW PHYSICAL_SLOW: queries the 100 slowest physical SQL statements since the DRDS instance is started or CLEAR SLOW is last executed. Note that the records on the 100 slowest physical SQL statements are cached in the DRDS system and are lost when the DRDS instance is restarted or CLEAR SLOW is executed.
  • SHOW FULL PHYSICAL_SLOW: queries all the slow physical SQL statements that are recorded and persisted to the built-in database of the DRDS instance since the DRDS instance is started. The maximum number of records is specified in the specifications of the DRDS instance. The DRDS instance scrolls to delete the earliest slow SQL statements. If the specifications of the DRDS instance is 4-core 4 GB, a maximum of 10,000 slow SQL statements can be recorded. If the specifications of the DRDS instance is 8-core 8 GB, a maximum of 20,000 slow SQL statements can be recorded. Both slow logical SQL statements and slow physical SQL statements are counted. The same rule applies to other specifications.

Important columns:

  • GROUP_NAME: the name of the group to which the database that executes the SQL statement belongs.
  • START_TIME: the time when the SQL statement started to be executed.
  • EXECUTE_TIME: the time that the DRDS instance takes to execute the SQL statement.
  • AFFECT_ROW: the number of affected rows for DML statements or the number of returned records for query statements.
  1. mysql> show physical_slow;
  2. +----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
  3. | GROUP_NAME | DBKEY_NAME | START_TIME | EXECUTE_TIME | SQL_EXECUTE_TIME | GETLOCK_CONNECTION_TIME | CREATE_CONNECTION_TIME | AFFECT_ROW | SQL |
  4. +----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
  5. | TDDL5_00_GROUP | db218249098_sqa_zmf_tddl5_00_3309 | 2016-03-16 13:05:38 | 1057 | 1011 | 0 | 0 | 1 | select sleep(1) |
  6. +----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
  7. 1 row in set (0.01 sec)

CLEAR SLOW

You can execute this SQL statement to clear the 100 slowest logical SQL statements and the 100 slowest physical SQL statements that have been recorded since the DRDS instance was started or CLEAR SLOW was last executed.

Note: Both SHOW SLOW and SHOW PHYSICAL_SLOW can be executed to query the 100 slowest SQL statements. If CLEAR SLOW has not been executed for a long time, these SQL statements might have been recorded a long time ago. Therefore, we recommend that you execute CLEAR SLOW after SQL optimization statements are executed, and check the optimized results of slow SQL statements after the system runs for a while.

  1. mysql> clear slow;
  2. Query OK, 0 rows affected (0.00 sec)