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

作者：华为云开发者联盟

2022-10-28
中国香港
本文字数：2023 字
阅读完需：约 7 分钟

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

关键指标

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

定位慢 SQL 手段

实时慢 SQL 查询

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

select    a.pid,    a.sessionid,    a.datname,    a.usename,    a.application_name,    a.client_addr,    a.xact_start,    a.query_start,    (now() - a.query_start)::text as query_runtime,    a.unique_sql_id,    w.wait_status,    w.wait_event,    w.locktag,    w.lockmode,    w.block_sessionid,    a.queryfrom    pg_stat_activity a join    pg_thread_wait_status w on    a.sessionid = w.sessionidwhere    a.pid <> pg_backend_pid()    and a.state = 'active'    and a.client_addr is not nullorder by    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（按照执行时间排序）

SELECT  *,  finish_time - start_time as run_timeFROM  dbe_perf.statement_historyWHERE  start_time > '2022-07-08 18:00:00'   AND start_time < '2022-07-08 19:00:00'   -- 根据unique_query_id可以过滤出特定的查询   -- AND unique_query_id = 123456ORDER BY  run_time desc;

复制代码

单个 Query 运行情况分析：statement

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

SELECT  *,  total_elapse_time / n_calls as avg_elapse_timeFROM  dbe_perf.statementWHERE  unique_query_id = 123456;

复制代码

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

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

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

复制代码

查看会话快照信息

SELECT*FROMdbe_perf.local_active_sessionWHEREquery_start_time > '2022-07-08 18:00:00'AND query_start_time < '2022-07-08 19:00:00'AND unique_query ilike '%%';

复制代码

常用处理手段

中止慢 SQL

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

select pg_terminate_session(pid,sessionid);

复制代码

优化 SQL

更新统计信息

查看统计信息

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

复制代码

更新统计信息

analyze tablename;

复制代码

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

ALTER TABLE tablename ALTER COLUMN colname SET (n_distinct = 实际值);analyze tablename; -- analyze执行后生效-- 取消设置ALTER TABLE tablename ALTER COLUMN colname RESET (n_distinct);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 等待事件，进一步优化。

点击关注，第一时间了解华为云新鲜技术~

发布于: 刚刚阅读数: 3

原文链接:【http://xie.infoq.cn/article/fc5bca71321636a522e2b22b1】。文章转载请联系作者。

华为云开发者联盟

关注

提供全面深入的云计算技术干货 2020-07-14 加入

华为云开发者社区，提供全面深入的云计算前景分析、丰富的技术干货、程序样例，分享华为云前沿资讯动态，方便开发者快速成长与发展，欢迎提问、互动，多方位了解云计算！传送门：https://bbs.huaweicloud.com/

发布

暂无评论

创作场景

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

关键指标

定位慢 SQL 手段

实时慢 SQL 查询

历史慢 SQL 查询

整体运行情况分析：WDR 报告

单次执行情况分析：statement_history

单个 Query 运行情况分析：statement

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

查看会话快照信息

常用处理手段

中止慢 SQL

优化 SQL

更新统计信息

使用 hint 优化计划

修改参数

其他

华为云开发者联盟

评论