写点什么

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

  • 2022-10-28
    中国香港
  • 本文字数:2023 字

    阅读完需:约 7 分钟

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

本文分享自华为云社区《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
用户头像

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

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

评论

发布
暂无评论
教你处理数仓慢SQL常见定位问题_数据库_华为云开发者联盟_InfoQ写作社区