Use a slow query table

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

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;