教你处理数仓慢SQL常见定位问题

摘要：通常在运维监控出现CPU使用率较高、P80/P95指标较高、慢SQL数量上升等现象，或者业务出现超时报错时，优先应排查是否出现慢SQL。

本文分享自华为云社区《GaussDB慢SQL常见定位处理手段》，作者：酷哥。
关键指标

通常在运维监控出现CPU使用率较高、P80/P95指标较高、慢SQL数量上升等现象，或者业务出现超时报错时，优先应排查是否出现慢SQL。
定位慢SQL手段

实时慢SQL查询

查询当前执行时间TOP10的SQL，识别长时间未结束的SQL后可以手动中止。

1. select
2.     a.pid,
3.     a.sessionid,
4.     a.datname,
5.     a.usename,
6.     a.application_name,
7.     a.client_addr,
8.     a.xact_start,
9.     a.query_start,
10.     (now() - a.query_start)::text as query_runtime,
11.     a.unique_sql_id,
12.     w.wait_status,
13.     w.wait_event,
14.     w.locktag,
15.     w.lockmode,
16.     w.block_sessionid,
17.     a.query
18. from
19.     pg_stat_activity a join
20.     pg_thread_wait_status w on
21.     a.sessionid = w.sessionid
22. where
23.     a.pid <> pg_backend_pid()
24.     and a.state = 'active'
25.     and a.client_addr is not null
26. order by
27.     query_runtime desc;

复制代码

根据查询结果，如果是等待锁，可以结合锁等待信息进一步分析，其他情况可以根据unique_query_id关联WDR报告、statement视图进一步分析慢的根因。
历史慢SQL查询

思路：根据CPU、慢SQL等监控指标，定位慢SQL出现的时间范围，通过以下几种方式进一步分析。
整体运行情况分析：WDR报告

通过导出对应时间段的WDR报告，可以分析耗时较长的SQL，WDR报告生成方法参见产品文档。
单次执行情况分析：statement_history

statement_history记录了执行时间超过阈值(log_min_duration_statement，默认3 s)的详细SQL信息，包含计划生成时间、执行时间、锁等待时间等信息，其中部分信息与参数track_stmt_stat_level设置的级别（默认为'OFF,L0'）有关。 设置参数track_stmt_stat_level='OFF,L1'后，statement_history中可以记录计划信息、锁等待时间等信息。 必须在postgres库内查询，根据时间段查询慢SQL（按照执行时间排序）

1. SELECT
2.   *,
3.   finish_time - start_time as run_time
4. FROM
5.   dbe_perf.statement_history
6. WHERE
7.   start_time > '2022-07-08 18:00:00'
8.    AND start_time < '2022-07-08 19:00:00'
9.    -- 根据unique_query_id可以过滤出特定的查询
10.    -- AND unique_query_id = 123456
11. ORDER BY
12.   run_time desc;

复制代码

单个Query运行情况分析：statement

statement记录了SQL按照unique_sql_id归一化的执行信息，包括执行次数、总的执行时间、访问数据量、内存使用等信息。 根据unique_sql_id查询历史执行信息

1. SELECT
2.   *,
3.   total_elapse_time / n_calls as avg_elapse_time
4. FROM
5.   dbe_perf.statement
6. WHERE
7.   unique_query_id = 123456;

复制代码

动态抓取执行信息（计划、锁等待时间等）

为了避免对生产环境产生影响，可以动态抓取SQL执行信息

1. -- 抓取指定unique_sql_id的全量SQL信息
2. -- 示例：unique_sql_id为3267119089，全量SQL级别为L2，相当于track_stmt_stat_level='L2,off'
3. select * from dynamic_func_control('LOCAL', 'STMT', 'TRACK', '{"3267119089", "L2"}');
4. -- 打开之后，查询statement_history
5. -- 关闭抓取，清理
6. select * from dynamic_func_control('LOCAL', 'STMT', 'UNTRACK', '{"3267119089"}');
7. select * from dynamic_func_control('LOCAL', 'STMT', 'LIST', '{}');
8. select * from dynamic_func_control('LOCAL', 'STMT', 'CLEAN', '{}');

复制代码

查看会话快照信息

1. SELECT
2. *
3. FROM
4. dbe_perf.local_active_session
5. WHERE
6. query_start_time > '2022-07-08 18:00:00'
7. AND query_start_time < '2022-07-08 19:00:00'
8. AND unique_query ilike '%%';

复制代码

常用处理手段

中止慢SQL

根据查询结果中的pid和sessionid，使用函数中止查询

1. select pg_terminate_session(pid,sessionid);

复制代码

优化SQL

更新统计信息

查看统计信息

1. select * from pg_stats where tablename = '表名';
2. select * from pg_stats where tablename = '表名' and attname = '列名';

复制代码

更新统计信息

1. analyze tablename;

复制代码

手动设置列的distinct值（该字段不同值的数量，选择率 ~ 总行数/distinct值）

1. ALTER TABLE tablename ALTER COLUMN colname SET (n_distinct = 实际值);
2. analyze tablename; -- analyze执行后生效
3. ​
4. -- 取消设置
5. ALTER TABLE tablename ALTER COLUMN colname RESET (n_distinct);
6. analyze tablename; -- analyze执行后生效

复制代码

使用hint优化计划

• 通过分析慢SQL的计划，可以使用hint进行调整，openGaussc常用的hint包括：
  
• Join顺序的Hint，语法示例：/+ leading((t1 t2))/
  
• Join方式的Hint，语法示例：/+ nestloop(t1 t2)/
  
• Scan方式的Hint，语法示例：/+ indexscan(t1 index1)/
  
• 优化器GUC参数的Hint，语法示例：/+ set(param value)/
  
• Custom Plan和Generic Plan选择的Hint，语法示例：/+ use_cplan/
  
• ....
  

修改参数

根据慢SQL分析结论，可以考虑修改GUC参数，但是修改参数同时也会影响其他查询的计划，属于高风险操作。
其他

对于整体执行慢，可以通过分析WDR报告中TOP等待事件，进一步优化。
 
点击关注，第一时间了解华为云新鲜技术~

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！