- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

GaussDB(DWS)实践系列-低效业务脚本检测指导

四叶草发表于 2021/08/31 11:42:01 2021/08/31

【摘要】为保障业务系统高效运行，降低额外的资源损耗，建议定期对运行在GaussDB(DWS)集群上的业务脚本进行排查，并及时对低效业务脚本进行识别和优化，避免系统运行卡顿或资源过度使用。因此，需要增强低效业务脚本的排查和校验，及时识别出耗时、高频等需优化的低效SQL，并进行整改，为生产业务运行增加一道防护网。

华为云GaussDB(DWS)-低效业务脚本检测指导

为保障业务系统高效运行，降低额外的资源损耗，建议定期对运行在GaussDB(DWS)集群上的业务脚本进行排查，并及时对低效业务脚本进行识别和优化，避免系统运行卡顿或资源过度使用。因此，需要增强低效业务脚本的排查和校验，及时识别出耗时、高频等需优化的低效SQL，并进行整改，为生产业务运行增加一道防护网。

一、开启Active SQL统计

1. 操作前准备

在华为云管控面配置参数开启active SQL统计功能，数据库后台会自动记录业务SQL的相关运行信息，包括数据库名、访问IP、执行时间、执行计划等信息，定期查询active SQL统计视图获取SQL运行信息，针对执行时间较长且频次较高的SQL进行重点分析优化。

登录华为云网址，进行Active SQL统计功能配置，网址链接：

https://auth.huaweicloud.com/authui/login.html。

搜索数据仓库服务GaussDB(DWS)服务。

点击选择指定集群，跳转到对应页面进行配置。

在参数修改页签设置enable_resource_record（开启资源监控记录归档功能）参数值为on,默认值为off。

2. 统计视图介绍

PGXC_WLM_SESSION_INFO视图显示在所有CN上执行作业结束后的负载管理记录（系统中运行的业务SQL每隔3分钟会被归档），该视图需管理员权限用户执行，普通用户没有权限。

【备注】查询pgxc_wlm_session_info视图需登录到postgres数据库下。

pgxc_wlm_session_info视图信息
序号	名称	类型	描述
1	datid	oid	连接后端的数据库OID。
2	dbname	text	连接后端的数据库名称。
3	schemaname	text	模式名。
4	nodename	text	语句执行的CN名称。
5	username	text	连接到后端的用户名。
6	application_name	text	连接到后端的应用名。
7	client_addr	inet	连接到后端的客户端的IP地址。如果此字段是null，它表明通过服务器机器上UNIX套接字连接客户端或者这是内部进程，如autovacuum。
8	client_hostname	text	客户端的主机名，这个字段是通过client_addr的反向DNS查找得到。这个字段只有在启动log_hostname且使用IP连接时才非空。
9	client_port	integer	客户端用于与后端通讯的TCP端口号，如果使用Unix套接字，则为-1。
10	query_band	text	用于标示作业类型，默认为空字符串。
11	block_time	bigint	语句执行前的阻塞时间，包含语句解析和优化时间，单位ms。
12	start_time	timestamp with time zone	语句执行的开始时间。
13	finish_time	timestamp with time zone	语句执行的结束时间。
14	duration	bigint	语句实际执行的时间，单位ms。
15	estimate_total_time	bigint	语句预估执行时间，单位ms。
16	status	text	语句执行结束状态：正常为finished，异常为aborted。
17	abort_info	text	语句执行结束状态为aborted时显示异常信息。
18	resource_pool	text	用户使用的资源池。
19	control_group	text	语句所使用的Cgroup。
20	min_peak_memory	integer	语句在所有DN上的最小内存峰值，单位MB。
21	max_peak_memory	integer	语句在所有DN上的最大内存峰值，单位MB。
22	average_peak_memory	integer	语句执行过程中的内存使用平均值，单位MB。
23	memory_skew_percent	integer	语句各DN间的内存使用倾斜率。
24	spill_info	text	语句在所有DN上的下盘信息： None：所有DN均未下盘。 All: 所有DN均下盘。 [a:b]: 数量为b个DN中有a个DN下盘。
25	min_spill_size	integer	若发生下盘，所有DN上下盘的最小数据量，单位MB，默认为0。
26	max_spill_size	integer	若发生下盘，所有DN上下盘的最大数据量，单位MB，默认为0。
27	average_spill_size	integer	若发生下盘，所有DN上下盘的平均数据量，单位MB，默认为0。
28	spill_skew_percent	integer	若发生下盘，DN间下盘倾斜率。
29	min_dn_time	bigint	语句在所有DN上的最小执行时间，单位ms。
30	max_dn_time	bigint	语句在所有DN上的最大执行时间，单位ms。
31	average_dn_time	bigint	语句在所有DN上的平均执行时间，单位ms。
32	dntime_skew_percent	integer	语句在各DN间的执行时间倾斜率。
33	min_cpu_time	bigint	语句在所有DN上的最小CPU时间，单位ms。
34	max_cpu_time	bigint	语句在所有DN上的最大CPU时间，单位ms。
35	total_cpu_time	bigint	语句在所有DN上的CPU总时间，单位ms。
36	cpu_skew_percent	integer	语句在DN间的CPU时间倾斜率。
37	min_peak_iops	integer	语句在所有DN上的每秒最小IO峰值（列存单位是次/s，行存单位是万次/s）。
38	max_peak_iops	integer	语句在所有DN上的每秒最大IO峰值（列存单位是次/s，行存单位是万次/s）。
39	average_peak_iops	integer	语句在所有DN上的每秒平均IO峰值（列存单位是次/s，行存单位是万次/s）。
40	iops_skew_percent	integer	语句在DN间的IO倾斜率。
41	warning	text	主要显示如下几类告警信息以及SQL自诊断调优相关告警： 1. Spill file size large than 256MB 2. Broadcast size large than 100MB 3. Early spill 4. Spill times is greater than 3 5. Spill on memory adaptive 6. Hash table conflict
42	queryid	bigint	语句执行使用的内部query id。
43	query	text	执行的语句。
44	query_plan	text	语句的执行计划。
45	node_group	text	语句所属用户对应的逻辑集群。

二、低效SQL检测

1、手工查询过滤

过滤执行耗时长、高频的业务SQL，识别并进行优化，提升SQL执行效率。

1.1检查SQL耗时

通过如下模板SQL获取检测期间总耗时最高的Top 20个SQL，进行统计分析，实际使用过程中可按照备注进行调整。

select

sum(duration) as sum ,

round(avg(duration),2) as avg, --保留小数点后2位有效数字

count(duration) as count,

substr(query,1,100) as sub_query --根据SQL特征截取做聚合。

from pgxc_wlm_session_info

where start_time > '2021-08-19 05:38:11' --语句开始执行时间

and start_time < '2021-08-19 11:38:11'

and username<> 'Ruby' --排除掉系统用户

and dbname = 'postgres' -- postgres按需替换为指定数据库

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

order by sum desc -- 根据sum倒排

limit 20;

查询结果如下，图中：

1.2检测高频SQL

通过如下模板SQL获取检测期间执行频率最高的Top 20个SQL，进行统计分析，实际使用过程中可按照备注进行调整。

select

sum(duration) as sum ,

round(avg(duration),2) as avg,

count(duration) as cnt,

substr(query,1,100) as sub_query --根据SQL特征截取做聚合。

from pgxc_wlm_session_info

where start_time > '2021-08-19 05:38:11' --语句开始执行时间

and start_time < '2021-08-19 11:38:11'

and username<> 'Ruby' --排除掉系统用户

and dbname = 'postgres' -- postgres按需替换为指定数据库

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

order by cnt desc -- 根据cnt倒排

limit 20;

2、函数过滤筛选

GaussDB(DWS)是分布式数据库架构，适合大规模并行处理提升性能，在编写SQL过程中应尽量降低执行时不同节点间的数据流动，例如节点间数据的重分布或者广播，对应到执行计划中的stream算子和subplan算子（可以通过调整分布键，维度表改复制表，优化子查询等方式进行SQL优化改写）。

2.1 脚本准备

（1）筛选subplan算子

登陆postgres数据库创建如下函数，统计执行计划中的subplan数量。

CREATE OR REPLACE FUNCTION public.subplan_count(text)

RETURNS integer

LANGUAGE sql

IMMUTABLE STRICT NOT FENCED

AS $function$

select ((length($1) - length(replace($1, 'SubPlan', '')) )::int / length('SubPlan'))::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算子。

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

select

substr(query,1,60) as sub_query,

dbname,

count(1) as count,

round(avg(duration),2) as avg_duration,

public.stream_count(query_plan) as stream_count,

public.subplan_count(query_plan) as subplan_count

from pgxc_wlm_session_info

where start_time > '2021-08-19 05:38:11' --语句开始执行时间

and start_time < '2021-08-19 11:38:11'

and username <> 'Ruby'

--and dbname = 'postgres' --按需替换为指定数据库,例如postgres

group by 1,2,5,6

having stream_count > 0 or subplan_count > 0

order by stream_count desc

limit 20;

备注：结果集中dbname列统计对应数据库名，count列统计业务脚本调用频次，avg_duration列统计业务脚本的平均执行时间,stream_count列统计单条业务脚本的stream算子数量，subplan_count列统计单条业务脚本的subplan数量。

基于以上的统计结果查询完整的业务SQL如下。

查询模板：

select query from pgxc_wlm_session_info

where trim(query) like '%subquery%'

and dbname = 'postgres'

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;

2.3 结果分析

理想情况下，执行检测脚本，Stream算子和subplan统计数量均为0。对于集群中运行的业务脚本，需要尽量消减Stream和Subplan总数，减少节点间数据的重分布和广播，从而提升整体运行效率。

点赞
收藏
关注作者

0/1000

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

全部回复

上滑加载中

设置昵称

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

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

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

GaussDB(DWS)实践系列-低效业务脚本检测指导

华为云GaussDB(DWS)-低效业务脚本检测指导

一、开启Active SQL统计

1. 操作前准备

2. 统计视图介绍

二、低效SQL检测