并发场景低效SQL检测方法

举报
梅菜扣肉 发表于 2020/06/10 15:58:52 2020/06/10
【摘要】 在交互式分析、报表查询等场景中,往往在业务高峰存在较高并发时发现DWS上业务性能优明显下降,观察后台sys CPU占比大于20%,同时IO、内存、网络没有明显资源瓶颈。后台瞬时抓取stream大于1000,SQL执行计划大量耗时开销在STREAM算子。实验室验证stream数在500以内性能基本稳定,超出后在stream线程启停上的开销会明显影响stream算子的执行效率。因此对该场景的优化需要检

并发场景低效SQL检测方法

 

在交互式分析、报表查询等场景中,往往在业务高峰存在较高并发时发现DWS上业务性能优明显下降,观察后台sys CPU占比大于20%,同时IO、内存、网络没有明显资源瓶颈。后台瞬时抓取stream大于1000,SQL执行计划大量耗时开销在STREAM算子。实验室验证stream数在500以内性能基本稳定,超出后在stream线程启停上的开销会明显影响stream算子的执行效率。因此对该场景的优化需要检测stream、subplan算子多的SQL语句,进行针对性优化,可有效提升系统性能。

一、开启Active SQL功能

开启active SQL功能,然后进行业务连跑,数据库后台会自动记录SQL执行信息,业务连跑结束之后,查询active SQL视图,获取SQL执行信息,查找执行时间较长且频次较高的SQL进行重点优化分析。

登陆任一数据节点,切换到omm用户,执行如下命令开启active SQL统计功能。

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "enable_resource_track = on"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "enable_resource_record = on"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_level = query"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_cost = 100"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_duration = 0"

二、业务侧低效SQL检测

1.   操作前准备

1.1 更新统计信息

在数据库中,统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化,从而导致性能问题。检测前需要进行全库统计信息收集。通过执行ANALYZE语句可收集与数据库中表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC中,查询优化器会使用这些统计数据,以生成最有效的执行计划,以对postgres库执行analyze操作为例执行如下命令,其余数据库仅需修改-d后面的库名即可。

gsql -d postgres -p 25308 -c ‘analyze’

1.2 统计表初始化

如果在检测前active SQL功能已经打开,需要执行以下动作清理历史SQL统计信息。

gs_ssh -c “gsql -d postgres -p 25308 -c ‘delete from gs_wlm_session_info’”

gsql -d postgres -p 25308 -c ‘vacuum full gs_wlm_session_info’

2.   自动化检查脚本

按照本章第1小节完成操作前准备,执行如下函数进行SQL检测,统计出低效SQL。

2.1 脚本准备

(1)筛选subplan

登陆postgres数据库创建如下存储过程,统计执行计划中的subplan数量。

CREATE OR REPLACE FUNCTION   public.stream_count(text)

 RETURNS integer

 LANGUAGE sql

 IMMUTABLE STRICT NOT FENCED

AS $function$

      select ((length($1) - length(replace(replace($1, 'Streaming(type: B',   ''), 'Streaming(type: R', ''))) / length('Streaming(type: B'))::int

$function$

;

(2)筛选Stream算子

登陆postgres数据库创建如下存储过程,统计执行计划中的Stream算子数量。

CREATE OR REPLACE FUNCTION   public.stream_count(text)

 RETURNS integer

 LANGUAGE sql

 IMMUTABLE STRICT NOT FENCED

AS $function$

      select ((length($1) - length(replace(replace($1, 'Streaming(type: B',   ''), 'Streaming(type: R', ''))) / length('Streaming(type: B'))::int

$function$

;

2.2 执行自动化检查

 执行自动化检查统计subplan或Stream算子大于1的业务脚本。

  1. 查询整个数据库中的低效业务脚本

登陆postgres数据库,执行如下SQL:

select

distinct substr(query,1,60) as   sub_query,

dbname, 

  public.stream_count(query_plan) as   stream_count,                                                                                        public.subplan_count(query_plan) as subplan_count

from   pgxc_wlm_session_info

where   stream_count > 1 or stream_count > 1

order by stream_count desc;

查询结果显示数据库中含有11条低效业务脚本,详细如下

1.png

 

  1. 查询指定数据库中低效业务脚本,例如tran_province_cz。

登陆postgres数据库,执行如下SQL:

select

distinct substr(query,1,60) as   sub_query,

dbname, 

  public.stream_count(query_plan) as   stream_count,                                                                                        public.subplan_count(query_plan) as subplan_count

from   pgxc_wlm_session_info

where   dbname = 'tran_province_cz'

and   (stream_count > 1 or stream_count > 1)

order   by stream_count desc;

查询结果显示tran_province_cz库中有2条低效业务脚本需要优化,详细如下。

2.png

 

(3)基于(1)、(2)的统计结果查询完整的低效业务脚本如下。

查询模板:

select   query from pgxc_wlm_session_info

where   trim(query) like '%sub_query%'

and   dbname = ‘dbname’

limit   1;

例如,

select   query from pgxc_wlm_session_info

where

trim(query)   like '% select * from EPAY_VW_PLAN_VOUCHER    where guid = $1 and prov%'

and   dbname = ‘tran_province_cz’ limit 1;

查询结果如下:

3.png

 

2.3 结果分析

理想情况下,执行检测脚本,Stream算子和subplan统计数量均为0,如下图所示:

4.png 


对于含有低效SQL的场景,执行检测脚本后,查询到某条SQL执行计划中含有Stream算子11个,并且含有8个subplan,需要进行调优。执行检测脚本查询结果如下:

5.png


 

对于集群中运行的业务应用系统,控制Stream和Subplan总数在1000以下,并且保证集群内瞬时Stream线程数小于1000,如果不满足需要进行业务代码调优,消减Stream和Subplan,以满足上线标准。

执行如下命令可以查询到集群内瞬时Stream线程数,其中tlevel!=0就代表stream线程,详细如下:

select   node_name,count(*) from pgxc_thread_wait_status where tlevel!=0 group by 1;

3.   手工获取Top SQL

3.1 检测总耗时Top SQL

         通过如下模板SQL获取检测期间总耗时最高的SQL,进行统计分析。

select

    sum(duration) as sum ,

    avg(duration) as avg,

    count(duration) as count,

    query

from pgxc_wlm_session_info

where start_time > 'XXXX-XX-XX XX:XX:XX'  -- XXXX-XX-XX XX:XX:XX为业务试跑开始时间

and dbname = 'YY' -- YY为测试数据库名称

and duration > ZZ -- 分析执行时间大于ZZ(单位ms)SQL

group by query  -- 按照SQL语句汇聚

order by sum desc  -- 根据sum倒排

limit 20;

3.2 检测高频Top SQL

通过如下模板SQL获取检测期间执行频率最高的SQL,进行统计分析。

select

    sum(duration) as sum ,

    avg(duration) as avg,

    count(duration) as count,

    query

from pgxc_wlm_session_info

where start_time > 'XXXX-XX-XX XX:XX:XX'  -- XXXX-XX-XX XX:XX:XX为业务试跑开始时间

and dbname = 'YY' -- YY为测试数据库名称

and duration > ZZ -- 分析执行时间大于ZZ(单位ms)SQL

group by query  -- 按照SQL语句汇聚

order by cnt desc  -- 根据cnt倒排

limit 20;

3.3 检测单个耗时Top SQL

通过如下模板SQL获取检测期间单个执行耗时最高的SQL,进行统计分析。

select

    sum(duration) as sum ,

    avg(duration) as avg,

    count(duration) as count,

    query

from pgxc_wlm_session_info

where start_time > 'XXXX-XX-XX XX:XX:XX'  -- XXXX-XX-XX XX:XX:XX为业务试跑开始时间

and dbname = 'YY' -- YY为测试数据库名称

and duration > ZZ -- 分析执行时间大于ZZ(单位ms)SQL

group by query  -- 按照SQL语句汇聚

order by avg desc  -- 根据avg倒排

limit 20;

3.4 实时检测观察stream线程数

执行如下命令可以查询到集群内瞬时Stream线程数,其中tlevel!=0就代表stream线程,详细如下:
select node_name,count(*) from pgxc_thread_wait_status where tlevel!=0 group by 1;

优化后的瞬时steam线程数控制在500以下,整体并发运行性能没有明显下降。



 

 


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。