This topic describes how to execute the SHOW statements to identify slow SQL queries.

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

SQL queries that take more than 1s are slow SQL queries.Logical slow SQL queries are sent from an application to a DRDS instance.

  • SHOW SLOW: queries the top 100 logical slow SQL queries since a DRDS instance is started or the last CLEAR SLOW statement is executed.
    Note The SHOW SLOW statement returns top 100 logical slow SQL queries. The returned data is stored in DRDS. If you restart the database instance or execute the CLEAR SLOW statement, the returned data is cleared.
  • SHOW FULL SLOW: queries all the logical slow SQL queries that are persistently stored in DRDS since the database instance is started. The system can retain a limited number of slow queries. The upper limit varies based on the instance type. DRDS dynamically deletes the oldest slow SQL queries when the maximum number of slow queries is exceeded. If the specifications of the DRDS instance include 4 cores and 4 GB memory, the system can retain a maximum of 10,000 slow SQL queries. If the specifications of the DRDS instance include 8 cores and 8 GB memory, the system can retain a maximum of 20,000 slow SQL queries. The slow SQL queries include logical slow SQL queries and physical slow SQL queries. A similar rule applies to other instance specifications.

Sample code

mysql> show slow where execute_time > 1000 limit 1;
+-----------+---------------------+--------------+------------+-----------+
| HOST      | START_TIME          | EXECUTE_TIME | AFFECT_ROW | SQL       |
+-----------+---------------------+--------------+------------+-----------+
| 127.0.0.1 | 2016-03-16 13:02:57 |         2785 |          7 | show rule |
+-----------+---------------------+--------------+------------+-----------+
1 row in set (0.02 sec)
            

Important columns:

  • HOST: the IP address of the server from which the SQL statement is sent.
  • START_TIME: the time when the SQL statement starts to be executed.
  • EXECUTE_TIME: the time that is spent executing the SQL statement.
  • AFFECT_ROW: the number of affected rows for a DML statement or the number of returned records for a data query language (DQL) statement.

SHOW [FULL] PHYSICAL\_SLOW [WHERE expr] [limit expr]

SQL queries that take more than 1s are slow SQL queries.Physical slow SQL queries are sent from a DRDS instance to an ApsaraDB RDS for MySQL instance.

  • SHOW PHYSICAL_SLOW: queries the top 100 physical slow SQL queries since a DRDS instance is started or the last CLEAR SLOW statement is executed. Take note that the SHOW PHYSICAL_SLOW statement returns the top 100 physical slow SQL queries.The returned data is stored in DRDS. If you restart the database instance or execute the CLEAR SLOW statement, the returned data is cleared.
  • SHOW FULL PHYSICAL_SLOW: queries all physical slow SQL queries that are persistently stored in DRDS since the database instance is started. The system can retain a limited number of slow queries. The upper limit varies based on the instance type. DRDS dynamically deletes the oldest slow SQL queries when the maximum number of slow queries is exceeded. If the specifications of the DRDS instance include 4 cores and 4 GB memory, the system can retain a maximum of 10,000 slow SQL queries. If the specifications of the DRDS instance include 8 cores and 8 GB memory, the system can retain a maximum of 20,000 slow SQL queries. The slow SQL queries include logical slow SQL queries and physical slow SQL queries. A similar rule applies to other instance specifications.

Sample code

mysql> show physical_slow;
+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
| GROUP_NAME     | DBKEY_NAME                        | START_TIME          | EXECUTE_TIME | SQL_EXECUTE_TIME | GETLOCK_CONNECTION_TIME | CREATE_CONNECTION_TIME | AFFECT_ROW | SQL             |
+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
| TDDL5_00_GROUP | db218249098_sqa_zmf_tddl5_00_3309 | 2016-03-16 13:05:38 |         1057 |             1011 |                       0 |                      0 |          1 | select sleep(1) |
+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
1 row in set (0.01 sec)
            

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 starts to be executed.
  • EXECUTE_TIME: the time that is spent executing the SQL statement.
  • AFFECT_ROW: the number of affected rows for a DML statement or the number of returned records for a DQL statement.

CLEAR SLOW

You can execute the CLEAR SLOW statement to clear the top 100 logical slow SQL queries and the top 100 physical slow SQL queries since a DRDS instance is started or the last CLEAR SLOW statement is executed.

Sample code

mysql> clear slow;
Query OK, 0 rows affected (0.00 sec)
            
Note You can execute the SHOW SLOW or SHOW PHYSICAL_SLOW statement to query the top 100 slow SQL queries. If you do not execute the CLEAR SLOW statement for a long period of time, the system may return some invalid slow SQL queries that are optimized. Therefore, we recommend that you execute CLEAR SLOW after you optimize slow SQL queries. Then, you can check whether the slow SQL queries are optimized after the system runs for a period of time.