Slow SQL queries

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

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

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

• CLEAR SLOW

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

SQL queries that take more than 1s are slow SQL queries. Logical slow SQL queries are slow SQL queries sent from applications to PolarDB-X instances.

• SHOW SLOW: queries the top 100 slowest logical slow SQL queries that are recorded since a PolarDB-X instance started or the last time the CLEAR SLOW statement is executed. Note The recorded top 100 slowest logical slow SQL queries are cached in the PolarDB-X instance. The records are cleared when the database instance is restarted or the CLEAR SLOW statement is executed.

• SHOW FULL SLOW: queries all logical slow SQL queries that are recorded since the database instance started. The records of these logical slow SQL queries are persisted to the built-in database of the PolarDB-X instance. A limited number of logical slow SQL queries can be recorded. The upper limit varies based on instance specifications. PolarDB-X dynamically deletes earlier slow SQL queries. If the instance specification is 4 cores and 4 GB memory, a maximum of 10,000 slow SQL queries can be recorded, including logical and physical slow SQL queries. If the instance specification is 8 cores and 8 GB memory, a maximum of 20,000 slow SQL queries can be recorded, including logical and physical slow SQL queries. A similar rule applies to other instance specifications.

Example:

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 started to be executed.

• EXECUTE_TIME : the time that was consumed to execute 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 slow SQL queries sent from PolarDB-X instances to ApsaraDB RDS for MySQL instances.

• SHOW PHYSICAL_SLOW: queries the top 100 slowest physical slow SQL queries that are recorded since a PolarDB-X instance started or the last time the CLEAR SLOW statement is executed. The recorded top 100 slowest physical slow SQL queries are cached in the PolarDB-X instance. The records are cleared when the database instance restarted or the CLEAR SLOW statement is executed.

• SHOW FULL PHYSICAL_SLOW: queries all physical slow SQL queries that are recorded since the database instance started. The records of these physical slow SQL queries are persisted to the built-in database of the PolarDB-X instance. A limited number of physical slow SQL queries can be recorded. The upper limit varies based on instance specifications. PolarDB-X dynamically deletes earlier slow SQL queries. If the instance specification is 4 cores and 4 GB memory, a maximum of 10,000 slow SQL queries can be recorded, including logical and physical slow SQL queries. If the instance specification is 8 cores and 8 GB memory, a maximum of 20,000 slow SQL queries can be recorded, including logical and physical slow SQL queries. A similar rule applies to other instance specifications.

Example:

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 belongs.

• START_TIME : the time when the SQL statement started to be executed.

• EXECUTE_TIME : the time that was consumed to execute 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 slowest logical slow SQL queries and the top 100 slowest physical slow SQL queries that are recorded since the PolarDB-X instance is started or the last time the CLEAR SLOW statement is executed.

Example:

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 slowest SQL queries. If the CLEAR SLOW statement remains unexecuted for a long period of time, some earlier slow SQL queries may be returned. We recommend that you execute the CLEAR SLOW statement 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.