PolarDB: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 sent from an application to a PolarDB-X 1.0 instance.

• SHOW SLOW: queries the top 100 logical slow SQL queries since a PolarDB-X 1.0 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 PolarDB-X 1.0. 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 PolarDB-X 1.0 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. PolarDB-X 1.0 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

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 |
+-----------+---------------------+--------------+------------+-----------+

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 PolarDB-X 1.0 instance to an ApsaraDB RDS for MySQL instance.

• SHOW PHYSICAL_SLOW: queries the top 100 physical slow SQL queries since a PolarDB-X 1.0 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 PolarDB-X 1.0. 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 PolarDB-X 1.0 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. PolarDB-X 1.0 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

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) |
+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+

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 PolarDB-X 1.0 instance is started or the last CLEAR SLOW statement is executed.

Sample code

CLEAR SLOW;