本文介绍如何通过慢查询表information_schema.kepler_slow_sql_merged来查找资源(如CPU、内存的等)消耗较多的SQL查询,便于初步定位慢查询原因。

使用说明

  • 慢查询表及其详情信息保存在前端接入节点(即Controller节点)的本地磁盘文件中,该表存储的慢查询数量受AnalyticDB MySQL版集群规模和SQL复杂度的影响。集群规模越大或者SQL越复杂,执行一条查询所产生并保存在表里的信息也就越多,能保存的慢查询数量就越少。
  • AnalyticDB MySQL版的慢查询表information_schema.kepler_slow_sql_merged记录了执行耗时大于1秒(默认时间)的SQL语句的部分统计信息。
    说明 如果要修改默认时间,请提交工单联系技术支持。

常用的字段

字段 说明
start_time 查询的执行开始时间。
time 查询的总耗时。单位:ms。
user 提交查询的用户名。
db 建立连接的数据库名称。
peak_mem 查询的峰值内存。单位为Byte。可用于判断目标查询SQL是否消耗了较多内存资源。
state 查询的状态,支持如下取值:
  • SUCCESS:执行成功。
  • FAILED:执行失败。
  • RUNNING:执行中。
scan_rows 从数据源表扫描的行数。可用于判断目标查询SQL是否扫描了大量数据。
scan_size 从数据源表扫描的数据量。单位:Byte。
scan_time 从底层存储扫描数据消耗的时间累加值。单位:ms。

该时间包含了使用索引过滤的时间,以及扫描符合检索条件的慢查询明细数据的时间,因此可用于初步判断过滤条件是否合适、过滤条件是否下推、索引是否生效等。

return_row_counts 查询输出的总行数。可用于判断一条查询最终总的输出量。
planning_time 查询在AnalyticDB MySQL版的优化器中使用最优执行计划计算的耗时。单位:ms。
wall_time 执行一条SQL时使用的物理算子消耗的CPU时间总和。可用来判断计算量较大的SQL。单位:ms。
sql 用户提交的SQL查询语句。
queued_time 查询的排队时间。单位:ms。
access_ip 查询提交使用的客户端IP地址。

常用的查询

  • 查看慢查询表中保存的查询个数,语句如下:
    SELECT count(*) FROM information_schema.kepler_slow_sql_merged;
  • 查看慢查询表中已保存的查询SQL的最早执行开始时间,语句如下:
    SELECT MIN(start_time) from information_schema.kepler_slow_sql_merged;
  • 查看目标时间段内慢查询的相关属性,如峰值内存、扫描行数、扫描数据量、相关SQL、耗时以及执行开始时间等。
    SQL语句如下:
    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};
    查看执行开始时间在2021-08-20 01:41:332021-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';
  • 查看符合模糊检索条件的慢查询的相关属性,例如峰值内存、扫描行数、扫描数据量、耗时以及执行开始时间等。
    SQL命令如下:
    SELECT peak_mem, scan_rows, scan_size, time, start_time
    FROM information_schema.kepler_slow_sql_merged
    WHERE sql LIKE '%date_test%';
  • 查看执行耗时在目标范围内的慢查询相关属性,例如峰值内存、扫描行数、扫描数据量、耗时、执行开始时间,SQL语句详情等。
    SQL命令如下:
    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};
    说明 慢查询表默认仅保留执行时长超过1秒的SQL,因此min_time的取值需大于等于1秒。
    示例如下:
    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;
  • 查看峰值内存在目标范围内的慢查询相关属性,例如峰值内存、扫描行数、扫描数据量、耗时、执行开始时间,SQL语句详情等。
    SQL命令如下:
    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};
    语句示例如下:
    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;
  • 根据客户端IP进行聚合分析。
    根据access_ip字段进行分组聚合,然后根据平均峰值内存最多的access_ip进行降序排列,SQL命令如下:
    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;