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

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

网友投稿 809 2022-05-28

华为云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视图信息

序号

名称

类型

描述

datid

oid

连接后端的数据库OID。

dbname

text

连接后端的数据库名称。

schemaname

text

模式名。

nodename

text

语句执行的CN名称。

username

text

连接到后端的用户名。

application_name

text

连接到后端的应用名。

client_addr

inet

连接到后端的客户端的IP地址。如果此字段是null，它表明通过服务器机器上UNIX套接字连接客户端或者这是内部进程，如autovacuum。

client_hostname

text

客户端的主机名，这个字段是通过client_addr的反向DNS查找得到。这个字段只有在启动log_hostname且使用IP连接时才非空。

client_port

integer

客户端用于与后端通讯的TCP端口号，如果使用Unix套接字，则为-1。

query_band

text

用于标示作业类型，默认为空字符串。

block_time

bigint

语句执行前的阻塞时间，包含语句解析和优化时间，单位ms。

start_time

timestamp with time zone

语句执行的开始时间。

finish_time

timestamp with time zone

语句执行的结束时间。

duration

bigint

语句实际执行的时间，单位ms。

estimate_total_time

bigint

语句预估执行时间，单位ms。

status

text

语句执行结束状态：正常为finished，异常为aborted。

abort_info

text

语句执行结束状态为aborted时显示异常信息。

resource_pool

text

用户使用的资源池。

control_group

text

语句所使用的Cgroup。

min_peak_memory

integer

语句在所有DN上的最小内存峰值，单位MB。

max_peak_memory

integer

语句在所有DN上的最大内存峰值，单位MB。

average_peak_memory

integer

语句执行过程中的内存使用平均值，单位MB。

memory_skew_percent

integer

语句各DN间的内存使用倾斜率。

spill_info

text

语句在所有DN上的下盘信息：

None：所有DN均未下盘。

All: 所有DN均下盘。

[a:b]: 数量为b个DN中有a个DN下盘。

min_spill_size

integer

若发生下盘，所有DN上下盘的最小数据量，单位MB，默认为0。

max_spill_size

integer

若发生下盘，所有DN上下盘的最大数据量，单位MB，默认为0。

average_spill_size

integer

若发生下盘，所有DN上下盘的平均数据量，单位MB，默认为0。

spill_skew_percent

integer

若发生下盘，DN间下盘倾斜率。

min_dn_time

bigint

语句在所有DN上的最小执行时间，单位ms。

max_dn_time

bigint

语句在所有DN上的最大执行时间，单位ms。

average_dn_time

bigint

语句在所有DN上的平均执行时间，单位ms。

dntime_skew_percent

integer

语句在各DN间的执行时间倾斜率。

min_cpu_time

bigint

语句在所有DN上的最小CPU时间，单位ms。

max_cpu_time

bigint

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

语句在所有DN上的最大CPU时间，单位ms。

total_cpu_time

bigint

语句在所有DN上的CPU总时间，单位ms。

cpu_skew_percent

integer

语句在DN间的CPU时间倾斜率。

min_peak_iops

integer

语句在所有DN上的每秒最小IO峰值（列存单位是次/s，行存单位是万次/s）。

max_peak_iops

integer

语句在所有DN上的每秒最大IO峰值（列存单位是次/s，行存单位是万次/s）。

average_peak_iops

integer

语句在所有DN上的每秒平均IO峰值（列存单位是次/s，行存单位是万次/s）。

iops_skew_percent

integer

语句在DN间的IO倾斜率。

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

queryid

bigint

语句执行使用的内部query id。

query

text

执行的语句。

query_plan

text

语句的执行计划。

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) 数据库

云数据管理如何助力企业实现数据驱动的决策

809 2022-05-28

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

探索BI系统搭建的必要性与AI技术的应用潜力

云数据管理如何助力企业实现数据驱动的决策

BI系统实施步骤全面解析——从需求分析到用户培训

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

连锁餐饮管理系统的功能有哪些？餐饮服务系统的构成及工

家居定制平台是什么？

友情链接