VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > T-SQL >
  • 教你处理数仓慢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.query
from
    pg_stat_activity a join
    pg_thread_wait_status w on
    a.sessionid = w.sessionid
where
    a.pid <> pg_backend_pid()
    and a.state = 'active'
    and a.client_addr is not null
order 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_time
FROM
  dbe_perf.statement_history
WHERE
  start_time > '2022-07-08 18:00:00'
   AND start_time < '2022-07-08 19:00:00'
   -- 根据unique_query_id可以过滤出特定的查询
   -- AND unique_query_id = 123456
ORDER BY
  run_time desc;
复制代码

单个Query运行情况分析:statement

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

复制代码
SELECT
  *,
  total_elapse_time / n_calls as avg_elapse_time
FROM
  dbe_perf.statement
WHERE
  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
*
FROM
dbe_perf.local_active_session
WHERE
query_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等待事件,进一步优化。

出处:https://www.cnblogs.com/huaweiyun/p/16835126.html


相关教程