This topic describes how to execute query statements on the information_schema.kepler_slow_sql_merged slow query table. This allows you to identify the causes of slow SQL queries and locate bad SQL queries that consume large amounts of resources such as CPU and memory.
Description
The information_schema.kepler_slow_sql_merged slow query table records statistics for SQL statements that take longer than one second to be executed by AnalyticDB for MySQL. One second is the default threshold. If you want to change the default time period, submit a ticket.
The slow query table and its details are stored in a file in the local disk of the frontend node. The number of slow SQL queries stored in the table varies between different cluster sizes or query complexities. The amount of information that must be stored is directly proportional to the cluster size or SQL query complexity and inversely proportional to the number of slow SQL queries that can be stored. You can invoke the count function on the slow query table to query the number of slow SQL queries or invoke the min(start_time) function to query the earliest slow SQL query stored in the table.
Common fields
Field | Description |
---|---|
start_time | The time when the query is submitted. |
time | The amount of time consumed by the query. Unit: ms. |
user | The name of the user who submits the query. |
db | The name of the database to which you are connected. |
peak_mem | The peak memory usage of the query. Unit: bytes. This field allows you to identify the SQL statements that consume large amounts of memory resources. |
state | The status of the query. Valid values: SUCCESS and FAILED. |
scan_rows | The number of rows scanned from the source table. This field allows you to identify whether an SQL statement has scanned a large amount of data. |
scan_size | The amount of data scanned from the source table. Unit: bytes. |
scan_time | The total amount of time consumed when data is scanned from the underlying storage. Unit: ms. This field allows you to identify whether the filter conditions are appropriate, whether they are pushed down, and whether the index is in effect. |
return_row_counts | The number of rows returned by the query. This field indicates the total amount of data generated by a query. |
planning_time | The amount of time it takes for the query optimizer of AnalyticDB for MySQL to determine the optimal execution plan. Unit: ms. |
wall_time | The total amount of CPU time consumed by physical operators when an SQL statement is executed. This field allows you to identify the SQL statements that require large amounts of computation. Unit: ms. |
sql | The SQL statements submitted by the user. |
queued_time | The amount of time that the query spends in the queue. Unit: ms. |
access_ip | The IP address of the client that is used to submit the query. |
Common queries
- Query information in a specific time period, including the memory usage, number of
scanned rows, amount of scanned data, executed SQL statements, time consumed, and
query submission time.
Template
SELECT peak_mem, scan_rows, scan_size, sql, time, start_time FROM information_schema.kepler_slow_sql_merged WHERE start_time > ${timeStart} AND start_time < ${timeEnd}
Example
The SQL statements that are submitted between
2020-09-16 01:41:33
and2020-09-16 02:41:33
are queried in the following example:SELECT peak_mem, scan_rows, scan_size, sql, time, start_time FROM information_schema.kepler_slow_sql_merged WHERE start_time > '2020-09-16 01:41:33' AND start_time < '2020-09-16 02:41:33'
- Query the memory usage, number of scanned rows, amount of scanned data, time consumed,
and query submission time based on SQL statements that match a specific pattern.
SELECT peak_mem, scan_rows, scan_size, time, start_time FROM information_schema.kepler_slow_sql_merged WHERE sql LIKE '%date_test%'
- Query information based on the time consumed.
Template
SELECT peak_mem, scan_rows, scan_size, time, start_time, sql FROM information_schema.kepler_slow_sql_merged WHERE time > ${min_time} AND time < ${max_time}
Example
SELECT peak_mem, scan_rows, scan_size, time, start_time, sql FROM information_schema.kepler_slow_sql_merged WHERE time > 20*1000 AND time < 30*1000
- Query information based on memory usage by using the
peak_mem
field.Template
SELECT peak_mem, scan_rows, scan_size, time, start_time, sql FROM information_schema.kepler_slow_sql_merged WHERE peak_mem > ${min_memory} AND peak_mem < ${max_memory}
Example
SELECT peak_mem, scan_rows, scan_size, time, start_time, sql FROM information_schema.kepler_slow_sql_merged WHERE peak_mem > 1 * 1024 * 1024 * 1024 AND peak_mem < 10 * 1024 * 1024 * 1024
- Perform aggregation analysis based on client IP addresses.
In the following example, SQL statements are grouped and aggregated based on the
access_ip
field. Then, the statements are sorted in descending order of average memory usage.SELECT max(peak_mem), avg(peak_mem) avg_peak_mem, count(*), max(scan_rows), avg(time), access_ip FROM information_schema.kepler_slow_sql_merged group by access_ip order by avg_peak_mem desc