GaussDB(DWS)性能調(diào)優(yōu)系列實戰(zhàn)篇二：十八般武藝之壞味道SQL識別

SQL語言是關(guān)系型數(shù)據(jù)庫（RDB）的標準語言，其作用是將使用者的意圖翻譯成數(shù)據(jù)庫能夠理解的語言來執(zhí)行。人類之間進行交流時，同樣的意思用不同的措辭會產(chǎn)生不同的效果。類似地，人類與數(shù)據(jù)庫交流信息時，同樣的操作用不同的SQL語句來表達，也會導致不同的效率。而有時同樣的SQL語句，數(shù)據(jù)庫采用不同的方式來執(zhí)行，效率也會不同。那些會導致執(zhí)行效率低下的SQL語句及其執(zhí)行方式，我們稱之為SQL中的“壞味道”。

下面這個簡單的例子，可以說明什么是SQL中的壞味道。

圖1-a 用union合并集合

在上面的查詢語句中，由于使用了union來合并兩個結(jié)果集，在合并后需要排序和去重，增加了開銷。實際上符合dept_id = 1和dept_id > 2的結(jié)果間不會有重疊，所以完全可以用union all來合并，如下圖所示。

圖1-b 用union all合并集合

而更高效的做法是用or條件，在掃描的時候直接過濾出所需的結(jié)果，不但節(jié)省了運算，也節(jié)省了保存中間結(jié)果所需的內(nèi)存開銷，如下圖所示。

圖1-c 用or條件來過濾結(jié)果

可見完成同樣的操作，用不同的SQL語句，效率卻大相徑庭。前兩條SQL語句都不同程度地存在著“壞味道”。

對于這種簡單的例子，用戶可以很容易發(fā)現(xiàn)問題并選出最佳方案。但對于一些復雜的SQL語句，其性能缺陷可能很隱蔽，需要深入分析才有可能挖掘出來。這對數(shù)據(jù)庫的使用者提出了很高的要求。即便是資深的數(shù)據(jù)庫專家，有時也很難找出性能劣化的原因。

GaussDB在執(zhí)行SQL語句時，會對其性能表現(xiàn)進行分析和記錄，通過視圖和函數(shù)等手段呈現(xiàn)給用戶。本文將簡要介紹如何利用GaussDB提供的這些“第一手”數(shù)據(jù)，分析和定位SQL語句中存在的性能問題，識別和消除SQL中的“壞味道”。

◆ 識別SQL壞味道之自診斷視圖

GaussDB在執(zhí)行SQL時，會對執(zhí)行計劃以及執(zhí)行過程中的資源消耗進行記錄和分析，如果發(fā)現(xiàn)異常情況還會記錄告警信息，用于對原因進行“自診斷”。用戶可以通過下面的視圖查詢這些信息：

??????? gs_wlm_session_info

??????? pgxc_wlm_session_info

??????? gs_wlm_session_history

??????? pgxc_wlm_session_history

其中g(shù)s_wlm_session_info是基本表，其余3個都是視圖。gs_開頭的用于查看當前CN節(jié)點上收集的信息，pgxc_開頭的則包含集群中所有CN收集的信息。各表格和視圖的定義基本相同，如下表所示。

表1 自診斷表格&函數(shù)字段定義

名稱

類型

描述

datid

oid

連接后端的數(shù)據(jù)庫OID。

dbname

text

連接后端的數(shù)據(jù)庫名稱。

schemaname

text

模式的名字。

nodename

text

語句執(zhí)行的CN名稱。

username

text

連接到后端的用戶名。

application_name

text

連接到后端的應用名。

client_addr

inet

連接到后端的客戶端的IP地址。 如果此字段是null，它表明通過服務器機器上UNIX套接字連接客戶端或者這是內(nèi)部進程，如autovacuum。

client_hostname

text

客戶端的主機名，這個字段是通過client_addr的反向DNS查找得到。這個字段只有在啟動log_hostname且使用IP連接時才非空。

client_port

integer

客戶端用于與后端通訊的TCP端口號，如果使用Unix套接字，則為-1。

query_band

text

用于標示作業(yè)類型，可通過GUC參數(shù)query_band進行設置，默認為空字符串。

block_time

bigint

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

start_time

timestamp with time zone

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

finish_time

timestamp with time zone

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

duration

bigint

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

estimate_total_time

bigint

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

status

text

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

abort_info

text

語句執(zhí)行結(jié)束狀態(tài)為aborted時顯示異常信息。

resource_pool

text

用戶使用的資源池。

control_group

text

語句所使用的Cgroup。

min_peak_memory

integer

語句在所有DN上的最小內(nèi)存峰值，單位MB。

max_peak_memory

integer

語句在所有DN上的最大內(nèi)存峰值，單位MB。

average_peak_memory

integer

語句執(zhí)行過程中的內(nèi)存使用平均值，單位MB。

memory_skew_percent

integer

語句各DN間的內(nèi)存使用傾斜率。

spill_info

text

語句在所有DN上的下盤信息：

None：所有DN均未下盤。

All: 所有DN均下盤。

[a:b]: 數(shù)量為b個DN中有a個DN下盤。

min_spill_size

integer

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

max_spill_size

integer

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

average_spill_size

integer

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

spill_skew_percent

integer

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

min_dn_time

bigint

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

max_dn_time

bigint

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

average_dn_time

bigint

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

dntime_skew_percent

integer

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

min_cpu_time

bigint

語句在所有DN上的最小CPU時間，單位ms。

max_cpu_time

bigint

語句在所有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

顯示告警信息。

queryid

bigint

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

query

text

執(zhí)行的語句。

query_plan

text

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

node_group

text

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

其中的query字段就是執(zhí)行的SQL語句。通過分析每個query對應的各字段，例如執(zhí)行時間，內(nèi)存，IO，下盤量和傾斜率等等，可以發(fā)現(xiàn)疑似有問題的SQL語句，然后結(jié)合query_plan（執(zhí)行計劃）字段，進一步地加以分析。特別地，對于一些在執(zhí)行過程中發(fā)現(xiàn)的異常情況，warning字段還會以human-readable的形式給出告警信息。目前能夠提供的自診斷信息如下：

◇多列/單列統(tǒng)計信息未收集

優(yōu)化器依賴于表的統(tǒng)計信息來生成合理的執(zhí)行計劃。如果沒有及時對表中各列收集統(tǒng)計信息，可能會影響優(yōu)化器的判斷，從而生成較差的執(zhí)行計劃。如果生成計劃時發(fā)現(xiàn)某個表的單列或多列統(tǒng)計信息未收集，warning字段會給出如下告警信息：

Statistic Not Collect:

schemaname.tablename(column name list)

此外，如果表格的統(tǒng)計信息已收集過（執(zhí)行過analyze），但是距離上次analyze時間較遠，表格內(nèi)容發(fā)生了很大變化，可能使優(yōu)化器依賴的統(tǒng)計信息不準，無法生成最優(yōu)的查詢計劃。針對這種情況，可以用pg_total_autovac_tuples系統(tǒng)函數(shù)查詢表格中自從上次分析以來發(fā)生變化的元組的數(shù)量。如果數(shù)量較大，最好執(zhí)行一下analyze以使優(yōu)化器獲得最新的統(tǒng)計信息。

◇SQL未下推

執(zhí)行計劃中的算子，如果能下推到DN節(jié)點執(zhí)行，則只能在CN上執(zhí)行。因為CN的數(shù)量遠小于DN，大量操作堆積在CN上執(zhí)行，會影響整體性能。如果遇到不能下推的函數(shù)或語法，warning字段會給出如下告警信息：

SQL is not plan-shipping, reason : %s

◇Hash連接大表做內(nèi)表

如果發(fā)現(xiàn)在進行Hash連接時使用了大表作為內(nèi)表，會給出如下告警信息：

PlanNode[%d] Large Table is INNER in HashJoin \"%s\"

目前“大表”的標準是平均每個DN上的行數(shù)大于100，000，并且內(nèi)表行數(shù)是外表行數(shù)的10倍以上。

◇大表等值連接使用NestLoop

如果發(fā)現(xiàn)對大表做等值連接時使用了NestLoop方式，會給出如下告警信息：

PlanNode[%d] Large Table with Equal-Condition use Nestloop\"%s\"

目前大表等值連接的判斷標準是內(nèi)表和外表中行數(shù)最大者大于DN的數(shù)量乘以100，000。

◇數(shù)據(jù)傾斜

數(shù)據(jù)在DN之間分布不均勻，可導致數(shù)據(jù)較多的節(jié)點成為性能瓶頸。如果發(fā)現(xiàn)數(shù)據(jù)傾斜嚴重，會給出如下告警信息：

PlanNode[%d] DataSkew:\"%s\", min_dn_tuples:%.0f, max_dn_tuples:%.0f

目前數(shù)據(jù)傾斜的判斷標準是DN中行數(shù)最多者是最少者的10倍以上，且最多者大于100,000。

◇代價估算不準確

GaussDB在執(zhí)行SQL語句過程中會統(tǒng)計實際付出的代價，并與之前估計的代價比較。如果優(yōu)化器對代價的估算與實際的偏差很大，則很可能生成一個非最優(yōu)化的計劃。如果發(fā)現(xiàn)代價估計不準確，會給出如下告警信息：

"PlanNode[%d] Inaccurate Estimation-Rows: \"%s\" A-Rows:%.0f, E-Rows:%.0f

目前的代價由計劃節(jié)點返回行數(shù)來衡量，如果平均每個DN上實際/估計返回行數(shù)大于100,000，并且二者相差10倍以上，則認定為代價估算不準。

◇Broadcast量過大

Broadcast主要適合小表。對于大表來說，通常采用Hash+重分布（Redistribute）的方式效率更高。如果發(fā)現(xiàn)計劃中有大表被廣播的環(huán)節(jié)，會給出如下告警信息：

PlanNode[%d] Large Table in Broadcast \"%s\"

目前對大表廣播的認定標準為平均廣播到每個DN上的數(shù)據(jù)行數(shù)大于100,000。

◇索引設置不合理

如果對索引的使用不合理，比如應該采用索引掃描的地方卻采用了順序掃描，或者應該采用順序掃描的地方卻采用了索引掃描，可能會導致性能低下。

索引掃描的價值在于減少數(shù)據(jù)讀取量，因此認為索引掃描過濾掉的行數(shù)越多越好。如果采用索引掃描，但輸出行數(shù)/掃描總行數(shù)>1/1000，并且輸出行數(shù)>10000（對于行存表）或>100（對于列存表），則會給出如下告警信息：

PlanNode[%d] Indexscan is not properly used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f

順序掃描適用于過濾的行數(shù)占總行數(shù)比例不大的情形。如果采用順序掃描，但輸出行數(shù)/掃描總行數(shù)<=1/1000，并且輸出行數(shù)<=10000（對于行存表）或<=100（對于列存表），則會給出如下告警信息：

PlanNode[%d] Indexscan is ought to be used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f

◇下盤量過大或過早下盤

SQL語句執(zhí)行過程中，因為內(nèi)存不足等原因，可能需要將中間結(jié)果的全部或一部分轉(zhuǎn)儲的磁盤上。下盤可能導致性能低下，應該盡量避免。如果監(jiān)測到下盤量過大或過早下盤等情況，會給出如下告警信息：

??????? Spill file size large than 256MB

??????? Broadcast size large than 100MB

??????? Early spill

??????? Spill times is greater than 3

??????? Spill on memory adaptive

??????? Hash table conflict

下盤可能是因為緩沖區(qū)設置得過小，也可能是因為表的連接順序或連接方式不合理等原因，要結(jié)合具體的SQL進行分析。可以通過改寫SQL語句，或者HINT指定連接方式等手段來解決。

使用自診斷視圖功能，需要將以下變量設成合適的值：

▲ use_workload_manager（設成on，默認為on）

▲?enable_resource_check（設成on，默認為on）

▲?resource_track_level（如果設成query，則收集query級別的信息，如果設成operator，則收集所有信息，如果設成none，則以用戶默認的log級別為準）

▲?resource_track_cost（設成合適的正整數(shù)。為了不影響性能，只有執(zhí)行代價大于resource_track_cost語句才會被收集。該值越大，收集的語句越少，對性能影響越?。环粗叫。占恼Z句越多，對性能的影響越大。）

執(zhí)行完一條代價大于resource_track_cost后，診斷信息會存放在內(nèi)存hash表中，可通過pgxc_wlm_session_history或gs_wlm_session_history視圖查看。

視圖中記錄的有效期是3分鐘，過期的記錄會被系統(tǒng)清理。如果設置enable_resource_record=on，視圖中的記錄每隔3分鐘會被轉(zhuǎn)儲到gs_wlm_session_info表中，因此3分鐘之前的歷史記錄可以通過gs_wlm_session_info表或pgxc_wlm_session_info視圖查看。

◆?發(fā)現(xiàn)正在運行的SQL的壞味道

上一節(jié)提到的自診斷視圖可以顯示已完成SQL的信息。如果要查看正在運行的SQL的情況，可以使用下面的視圖：

??????? gs_wlm_session_statistics

??????? pgxc_wlm_session_statistics

類似地，gs_開頭的用于查看當前CN節(jié)點上收集的信息，pgxc_開頭的則包含集群中所有CN收集的信息。兩個視圖的定義與上一節(jié)的自診斷視圖基本相同，使用方法也基本一致。 通過觀察其中的字段，可以發(fā)現(xiàn)正在運行的SQL中存在的性能問題。

例如，通過“select queryid, duration from gs_wlm_session_statistics order by duration desc limit 10;”可以查詢當前運行的SQL中，已經(jīng)執(zhí)行時間最長的10個SQL。如果時間過長，可能有必要分析一下原因。

圖2-a 通過gs_wlm_session_statistics視圖發(fā)現(xiàn)可能hang住SQL

查到queryid后，可以通過query_plan字段查看該SQL的執(zhí)行計劃，分析其中可能存在的性能瓶頸和異常點。

圖2-b 通過gs_wlm_session_statistics視圖查看當前SQL的執(zhí)行計劃

再下一步，可以結(jié)合等待視圖等其他手段定位性能劣化的原因。

圖2-c?通過gs_wlm_session_statistics視圖結(jié)合等待視圖定位性能問題

另外，活動視圖pg_stat_activity也能提供一些當前執(zhí)行SQL的信息。

◆?Top SQL——利用統(tǒng)計信息發(fā)現(xiàn)SQL壞味道

除了針對逐條SQL進行分析，還可以利用統(tǒng)計信息發(fā)現(xiàn)SQL中的壞味道。另一篇文章“Unique SQL特性原理與應用”中提到的Unique SQL特性，能夠針對執(zhí)行計劃相同的一類SQL進行了性能統(tǒng)計。與自診斷視圖不同的是，如果同一個SQL被多次執(zhí)行，或者多個SQL語句的結(jié)構(gòu)相同，只有條件中的常量值不同。這些SQL在Unique SQL視圖中會合并為一條記錄。因此使用Unique SQL視圖能更容易看出那些類型的SQL語句存在性能問題。

利用這一特性，可以找出某一指標或者某一資源占用量最高/最差的那些SQL類型。這樣的SQL被稱為“Top SQL”。???????? 例如，查找占用CPU時間最長的SQL語句，可以用如下SQL：

select unique_sql_id,query,cpu_time from pgxc_instr_unique_sql order by cpu_time desc limit 10。

Unique SQL的使用方式詳見https://bbs.huaweicloud.com/blogs/197299。

◆?結(jié)論

發(fā)現(xiàn)SQL中的壞味道是性能調(diào)優(yōu)的前提。GaussDB對數(shù)據(jù)庫的運行狀況進行了SQL級別的監(jiān)控和記錄。這些打點記錄的數(shù)據(jù)可以幫助用戶發(fā)現(xiàn)可能存在的異常情況，“嗅”出潛在的壞味道。從這些數(shù)據(jù)和提示信息出發(fā)，結(jié)合其他視圖和工具，可以定位出壞味道的來源，進而有針對性地進行優(yōu)化。

數(shù)據(jù)倉庫服務 GaussDB(DWS) EI企業(yè)智能 Gauss AP 應用性能調(diào)優(yōu)

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

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