This topic describes how to use the information_schema.kepler_slow_sql_merged slow query table to find SQL queries that consume large amounts of resources such as CPU and memory. This helps you identify the causes of slow SQL queries.

Description

  • The slow query table and its details are stored in the local disks of a controller node. The maximum number of slow queries that can be stored in the slow query table varies based on the cluster size of AnalyticDB for MySQL and SQL statement complexity. The larger the cluster size or the more complex the SQL statements, the more the information generated and stored in the table after a query is executed, and the fewer the slow queries that can be stored.
  • The information_schema.kepler_slow_sql_merged slow query table of AnalyticDB for MySQL records some statistics for SQL statements that take longer than 1 second to execute. One second is the default execution duration.
    Note If you want to change the default execution duration, submit a ticket for technical support.

Common columns

Column Description
start_time The start time of the query execution.
time The total amount of time consumed by the query. Unit: milliseconds.
user The name of the user that submits the query.
db The name of the database to be connected.
peak_mem The maximum memory of the query. Unit: byte. This column can be used to determine whether an SQL statement has consumed a large amount of memory resources.
state The state of the query. Valid values:
  • SUCCESS: The query was executed.
  • FAILED: The query failed to be executed.
  • RUNNING: The query is being executed.
scan_rows The number of rows scanned from the source table. This column can be used to determine whether an SQL statement has scanned a large amount of data.
scan_size The amount of data scanned from the source table. Unit: byte.
scan_time The total amount of time consumed when data is scanned from the underlying storage. Unit: milliseconds.

This includes the amount of time spent by indexes to filter data as well as the amount of time spent scanning the detailed data of slow queries that meet search conditions. This column can be used to determine whether the filter conditions are appropriate, whether the filter conditions have been pushed down, and whether the indexes have taken effect.

return_row_counts The number of rows returned from the query. This column can be used to determine the total amount of data returned from a query.
planning_time The amount of time consumed by the query optimizer of AnalyticDB for MySQL to determine an optimal execution plan. Unit: milliseconds.
wall_time The total amount of CPU time consumed by physical operators when an SQL statement is executed. This column can be used to determine the SQL statements that require large amounts of data to compute. Unit: milliseconds.
sql The SQL query statement submitted by the user.
queued_time The amount of time that the query spends on the queue. Unit: milliseconds.
access_ip The IP address of the client that is used to submit the query.

Common queries

  • Execute the following statement to view the number of queries that are stored in the slow query table:
    SELECT count(*) FROM information_schema.kepler_slow_sql_merged;
  • Execute the following statement to view the earliest start time of the stored SQL statement in the slow query table:
    SELECT MIN(start_time) from information_schema.kepler_slow_sql_merged;
  • Execute the following statement to view the properties of a slow query within a specific period of time. These properties include the maximum memory, number of scanned rows, amount of scanned data, related SQL statement, duration, and execution start time.
    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};
    Execute the following statement to view the properties of a slow query that is executed from 2021-08-20 01:41:33 to 2021-08-20 02:41:33:
    SELECT peak_mem, scan_rows, scan_size, sql, time, start_time
    FROM information_schema.kepler_slow_sql_merged
    WHERE start_time > '2021-08-20 01:41:33'
      AND start_time < '2021-08-20 02:41:33';
  • Execute the following statement to view the properties of a slow query that meet fuzzy search conditions. These properties include the maximum memory, number of scanned rows, amount of scanned data, duration, and execution start time.
    SELECT peak_mem, scan_rows, scan_size, time, start_time
    FROM information_schema.kepler_slow_sql_merged
    WHERE sql LIKE '%date_test%';
  • Execute the following statement to view the properties of a slow query whose execution duration falls within a specific range. These properties include the maximum memory, number of scanned rows, amount of scanned data, duration, execution start time, and SQL statement details.
    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};
    Note By default, the slow query table retains only SQL statements that take longer than 1 second to execute. Therefore, the value of min_time must be greater than or equal to 1 second.
    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;
  • Execute the following statement to view the properties of a slow query whose maximum memory falls within a specific range. These properties include the maximum memory, number of scanned rows, amount of scanned data, duration, execution start time, and SQL statements details.
    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.
    Execute the following statement to group and aggregate data based on the access_ip column and sort the data in descending order of the average maximum memory:
    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;