并发场景低效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后面的库名即可。

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数量。

（2）筛选Stream算子

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

2.2 执行自动化检查

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

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

登陆postgres数据库，执行如下SQL：

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

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

登陆postgres数据库，执行如下SQL：

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

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

查询结果如下：

2.3 结果分析

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

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

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

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

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以下，整体并发运行性能没有明显下降。