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 and 2020-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