GaussDB(DWS)實踐系列-低效業(yè)務腳本檢測指導

華為云GaussDB(DWS)-低效業(yè)務腳本檢測指導

為保障業(yè)務系統(tǒng)高效運行，降低額外的資源損耗，建議定期對運行在GaussDB(DWS)集群上的業(yè)務腳本進行排查，并及時對低效業(yè)務腳本進行識別和優(yōu)化，避免系統(tǒng)運行卡頓或資源過度使用。因此，需要增強低效業(yè)務腳本的排查和校驗，及時識別出耗時、高頻等需優(yōu)化的低效SQL，并進行整改，為生產業(yè)務運行增加一道防護網。

一、 開啟Active SQL統(tǒng)計

1.??? 操作前準備

在華為云管控面配置參數開啟active SQL統(tǒng)計功能，數據庫后臺會自動記錄業(yè)務SQL的相關運行信息，包括數據庫名、訪問IP、執(zhí)行時間、執(zhí)行計劃等信息，定期查詢active SQL統(tǒng)計視圖獲取SQL運行信息，針對執(zhí)行時間較長且頻次較高的SQL進行重點分析優(yōu)化。

登錄華為云網址，進行Active SQL統(tǒng)計功能配置，網址鏈接：

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

搜索數據倉庫服務GaussDB(DWS)服務。

點擊選擇指定集群，跳轉到對應頁面進行配置。

在參數修改頁簽設置enable_resource_record（開啟資源監(jiān)控記錄歸檔功能）參數值為on,默認值為off。

2.??? 統(tǒng)計視圖介紹

PGXC_WLM_SESSION_INFO視圖顯示在所有CN上執(zhí)行作業(yè)結束后的負載管理記錄（系統(tǒng)中運行的業(yè)務SQL每隔3分鐘會被歸檔），該視圖需管理員權限用戶執(zhí)行，普通用戶沒有權限。

【備注】查詢pgxc_wlm_session_info視圖需登錄到postgres數據庫下。

pgxc_wlm_session_info視圖信息

序號

名稱

類型

描述

1

datid

oid

連接后端的數據庫OID。

2

dbname

text

連接后端的數據庫名稱。

3

schemaname

text

模式名。

4

nodename

text

語句執(zhí)行的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

用于標示作業(yè)類型，默認為空字符串。

11

block_time

bigint

語句執(zhí)行前的阻塞時間，包含語句解析和優(yōu)化時間，單位ms。

12

start_time

timestamp with time zone

語句執(zhí)行的開始時間。

13

finish_time

timestamp with time zone

語句執(zhí)行的結束時間。

14

duration

bigint

語句實際執(zhí)行的時間，單位ms。

15

estimate_total_time

bigint

語句預估執(zhí)行時間，單位ms。

16

status

text

語句執(zhí)行結束狀態(tài)：正常為finished，異常為aborted。

17

abort_info

text

語句執(zhí)行結束狀態(tài)為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

語句執(zhí)行過程中的內存使用平均值，單位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

若發(fā)生下盤，所有DN上下盤的最小數據量，單位MB，默認為0。

26

max_spill_size

integer

若發(fā)生下盤，所有DN上下盤的最大數據量，單位MB，默認為0。

27

average_spill_size

integer

若發(fā)生下盤，所有DN上下盤的平均數據量，單位MB，默認為0。

28

spill_skew_percent

integer

若發(fā)生下盤，DN間下盤傾斜率。

29

min_dn_time

bigint

語句在所有DN上的最小執(zhí)行時間，單位ms。

30

max_dn_time

bigint

語句在所有DN上的最大執(zhí)行時間，單位ms。

31

average_dn_time

bigint

語句在所有DN上的平均執(zhí)行時間，單位ms。

32

dntime_skew_percent

integer

語句在各DN間的執(zhí)行時間傾斜率。

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自診斷調優(yōu)相關告警：

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

語句執(zhí)行使用的內部query id。

43

query

text

執(zhí)行的語句。

44

query_plan

text

語句的執(zhí)行計劃。

45

node_group

text

語句所屬用戶對應的邏輯集群。

二、 低效SQL檢測

1、手工查詢過濾

過濾執(zhí)行耗時長、高頻的業(yè)務SQL，識別并進行優(yōu)化，提升SQL執(zhí)行效率。

1.1檢查SQL耗時

通過如下模板SQL獲取檢測期間總耗時最高的Top 20個SQL，進行統(tǒng)計分析，實際使用過程中可按照備注進行調整。

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'? --語句開始執(zhí)行時間

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

and username<> 'Ruby'???? --排除掉系統(tǒng)用戶

and dbname = 'postgres'??? -- postgres按需替換為指定數據庫

group by sub_query??????? -- 按照SQL語句匯聚

order by sum desc???????? -- 根據sum倒排

limit 20;

查詢結果如下，圖中：

1.2檢測高頻SQL

通過如下模板SQL獲取檢測期間執(zhí)行頻率最高的Top 20個SQL，進行統(tǒng)計分析，實際使用過程中可按照備注進行調整。

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'? --語句開始執(zhí)行時間

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

and username<> 'Ruby'???? --排除掉系統(tǒng)用戶

and dbname = 'postgres'??? -- postgres按需替換為指定數據庫

group by query???? -- 按照SQL語句匯聚

order by cnt desc? -- 根據cnt倒排

limit 20;

2、函數過濾篩選

GaussDB(DWS)是分布式數據庫架構，適合大規(guī)模并行處理提升性能，在編寫SQL過程中應盡量降低執(zhí)行時不同節(jié)點間的數據流動，例如節(jié)點間數據的重分布或者廣播，對應到執(zhí)行計劃中的stream算子和subplan算子（可以通過調整分布鍵，維度表改復制表，優(yōu)化子查詢等方式進行SQL優(yōu)化改寫）。

2.1 腳本準備

（1）篩選subplan算子

登陸postgres數據庫創(chuàng)建如下函數，統(tǒng)計執(zhí)行計劃中的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數據庫創(chuàng)建如下函數，統(tǒng)計執(zhí)行計劃中的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 執(zhí)行檢查

調用函數檢查統(tǒng)計業(yè)務腳本中的subplan或Stream算子。

登陸postgres數據庫，執(zhí)行如下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'? --語句開始執(zhí)行時間

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列統(tǒng)計對應數據庫名，count列統(tǒng)計業(yè)務腳本調用頻次，avg_duration列統(tǒng)計業(yè)務腳本的平均執(zhí)行時間,stream_count列統(tǒng)計單條業(yè)務腳本的stream算子數量，subplan_count列統(tǒng)計單條業(yè)務腳本的subplan數量。

基于以上的統(tǒng)計結果查詢完整的業(yè)務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 結果分析

理想情況下，執(zhí)行檢測腳本，Stream算子和subplan統(tǒng)計數量均為0。對于集群中運行的業(yè)務腳本，需要盡量消減Stream和Subplan總數，減少節(jié)點間數據的重分布和廣播，從而提升整體運行效率。

EI企業(yè)智能 Gauss AP 數據倉庫服務 GaussDB(DWS) 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。