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]:
数量为bDN中有aDN下盘。

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 20SQL,进行统计分析,实际使用过程中可按照备注进行调整。

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 20SQL,进行统计分析,实际使用过程中可按照备注进行调整。

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 执行检查

调用函数检查统计业务脚本中的subplanStream算子。

登陆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。对于集群中运行的业务脚本,需要尽量消减StreamSubplan总数,减少节点间数据的重分布和广播,从而提升整体运行效率。

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

评论(0

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

全部回复

上滑加载中

设置昵称

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

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

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