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

华为云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总数，减少节点间数据的重分布和广播，从而提升整体运行效率。

EI企业智能 Gauss AP 数据仓库服务 GaussDB(DWS) 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。