草庐IT

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

华为云开发者社区 2023-03-28 原文
摘要：通常在运维监控出现CPU使用率较高、P80/P95指标较高、慢SQL数量上升等现象，或者业务出现超时报错时，优先应排查是否出现慢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.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等待事件，进一步优化。

 

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

SQL教你spancolorstyle

有关教你处理数仓慢SQL常见定位问题的更多相关文章

  1. ruby - 在 64 位 Snow Leopard 上使用 rvm、postgres 9.0、ruby 1.9.2-p136 安装 pg gem 时出现问题 - 2

    我想为Heroku构建一个Rails3应用程序。他们使用Postgres作为他们的数据库，所以我通过MacPorts安装了postgres9.0。现在我需要一个postgresgem并且共识是出于性能原因你想要pggem。但是我对我得到的错误感到非常困惑当我尝试在rvm下通过geminstall安装pg时。我已经非常明确地指定了所有postgres目录的位置可以找到但仍然无法完成安装:$envARCHFLAGS='-archx86_64'geminstallpg--\--with-pg-config=/opt/local/var/db/postgresql90/defaultdb/po

  2. ruby - 通过 rvm 升级 ruby​​gems 的问题 - 2

    尝试通过RVM将RubyGems升级到版本1.8.10并出现此错误:$rvmrubygemslatestRemovingoldRubygemsfiles...Installingrubygems-1.8.10forruby-1.9.2-p180...ERROR:Errorrunning'GEM_PATH="/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/ruby-1.9.2-p180@global:/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/rub

  3. ruby - 如何指定 Rack 处理程序 - 2

    Rackup通过Rack的默认处理程序成功运行任何Rack应用程序。例如:classRackAppdefcall(environment)['200',{'Content-Type'=>'text/html'},["Helloworld"]]endendrunRackApp.new但是当最后一行更改为使用Rack的内置CGI处理程序时，rackup给出“NoMethodErrorat/undefinedmethod`call'fornil:NilClass”:Rack::Handler::CGI.runRackApp.newRack的其他内置处理程序也提出了同样的反对意见。例如Rack

  4. ruby - 通过 RVM (OSX Mountain Lion) 安装 Ruby 2.0.0-p247 时遇到问题 - 2

    我的最终目标是安装当前版本的RubyonRails。我在OSXMountainLion上运行。到目前为止，这是我的过程:已安装的RVM$\curl-Lhttps://get.rvm.io|bash-sstable检查已知(我假设已批准)安装$rvmlistknown我看到当前的稳定版本可用[ruby-]2.0.0[-p247]输入命令安装$rvminstall2.0.0-p247注意:我也试过这些安装命令$rvminstallruby-2.0.0-p247$rvminstallruby=2.0.0-p247我很快就无处可去了。结果:$rvminstall2.0.0-p247Search

  5. ruby - Fast-stemmer 安装问题 - 2

    由于fast-stemmer的问题，我很难安装我想要的任何ruby​​gem。我把我得到的错误放在下面。Buildingnativeextensions.Thiscouldtakeawhile...ERROR:Errorinstallingfast-stemmer:ERROR:Failedtobuildgemnativeextension./System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/bin/rubyextconf.rbcreatingMakefilemake"DESTDIR="cleanmake"DESTDIR=

  6. ruby - 安装 Ruby 时遇到问题(无法下载资源 "readline--patch") - 2

    当我尝试安装Ruby时遇到此错误。我试过查看this和this但无济于事➜~brewinstallrubyWarning:YouareusingOSX10.12.Wedonotprovidesupportforthispre-releaseversion.Youmayencounterbuildfailuresorotherbreakages.Pleasecreatepull-requestsinsteadoffilingissues.==>Installingdependenciesforruby:readline,libyaml,makedepend==>Installingrub

  7. java - 从 JRuby 调用 Java 类的问题 - 2

    我正在尝试使用boilerpipe来自JRuby。我看过guide从JRuby调用Java，并成功地将它与另一个Java包一起使用，但无法弄清楚为什么同样的东西不能用于boilerpipe。我正在尝试基本上从JRuby中执行与此Java等效的操作:URLurl=newURL("http://www.example.com/some-location/index.html");Stringtext=ArticleExtractor.INSTANCE.getText(url);在JRuby中试过这个:require'java'url=java.net.URL.new("http://www

  8. ruby-on-rails - 简单的 Ruby on Rails 问题——如何将评论附加到用户和文章？ - 2

    我意识到这可能是一个非常基本的问题，但我现在已经花了几天时间回过头来解决这个问题，但出于某种原因，Google就是没有帮助我。(我认为部分问题在于我是一个初学者，我不知道该问什么......)我也看过O'Reilly的RubyCookbook和RailsAPI，但我仍然停留在这个问题上.我找到了一些关于多态关系的信息，但它似乎不是我需要的(尽管如果我错了请告诉我)。我正在尝试调整MichaelHartl'stutorial创建一个包含用户、文章和评论的博客应用程序(不使用脚手架)。我希望评论既属于用户又属于文章。我的主要问题是:我不知道如何将当前文章的ID放入评论Controller。

  9. 【高数】用拉格朗日中值定理解决极限问题 - 2

    首先回顾一下拉格朗日定理的内容：函数f(x)是在闭区间[a,b]上连续、开区间(a,b)上可导的函数，那么至少存在一个，使得:通过这个表达式我们可以知道，f(x)是函数的主体，a和b可以看作是主体函数f(x)中所取的两个值。那么可以有，  也就意味着我们可以用来替换 这种替换可以用在求某些多项式差的极限中。方法： 外层函数f(x)是一致的，并且h(x)和g(x)是等价无穷小。此时，利用拉格朗日定理，将原式替换为 ，再进行求解，往往会省去复合函数求极限的很多麻烦。使用要注意：1.要先找到主体函数f(x)，即外层函数必须相同。2.f(x)找到后，复合部分是等价无穷小。3.要满足作差的形式。如果是加

  10. 「Python｜Selenium｜场景案例」如何定位iframe中的元素？ - 2

    本文主要介绍在使用Selenium进行自动化测试或者任务时，对于使用了iframe的页面，如何定位iframe中的元素文章目录场景描述解决方案具体代码场景描述当我们在使用Selenium进行自动化测试的时候，可能会遇到一些界面或者窗体是使用HTML的iframe标签进行承载的。对于iframe中的标签，如果直接查找是无法找到的，会抛出没有找到元素的异常。比如近在咫尺的例子就是，CSDN的登录窗体就是使用的iframe，大家可以尝试通过F12开发者模式查看到的tag_name,class_name,id或者xpath来定位中的页面元素，会抛出NoSuchElementException异常。解决

随机推荐