本篇文章為大家展示了GaussDB for DWS是如何識(shí)別壞味道的SQL，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。
創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),樟樹(shù)企業(yè)網(wǎng)站建設(shè),樟樹(shù)品牌網(wǎng)站建設(shè),網(wǎng)站定制,樟樹(shù)網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷(xiāo),網(wǎng)絡(luò)優(yōu)化,樟樹(shù)網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專(zhuān)業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。
SQL語(yǔ)言是關(guān)系型數(shù)據(jù)庫(kù)（RDB）的標(biāo)準(zhǔn)語(yǔ)言，其作用是將使用者的意圖翻譯成數(shù)據(jù)庫(kù)能夠理解的語(yǔ)言來(lái)執(zhí)行。人類(lèi)之間進(jìn)行交流時(shí)，同樣的意思用不同的措辭會(huì)產(chǎn)生不同的效果。
類(lèi)似地，人類(lèi)與數(shù)據(jù)庫(kù)交流信息時(shí)，同樣的操作用不同的SQL語(yǔ)句來(lái)表達(dá)，也會(huì)導(dǎo)致不同的效率。而有時(shí)同樣的SQL語(yǔ)句，數(shù)據(jù)庫(kù)采用不同的方式來(lái)執(zhí)行，效率也會(huì)不同。那些會(huì)導(dǎo)致執(zhí)行效率低下的SQL語(yǔ)句及其執(zhí)行方式，我們稱(chēng)之為SQL中的“壞味道”。
下面這個(gè)簡(jiǎn)單的例子，可以說(shuō)明什么是SQL中的壞味道。
圖1-a 用union合并集合
在上面的查詢語(yǔ)句中，由于使用了union來(lái)合并兩個(gè)結(jié)果集，在合并后需要排序和去重，增加了開(kāi)銷(xiāo)。實(shí)際上符合dept_id = 1和dept_id > 2的結(jié)果間不會(huì)有重疊，所以完全可以用union all來(lái)合并，如下圖所示。
圖1-b 用union all合并集合
而更高效的做法是用or條件，在掃描的時(shí)候直接過(guò)濾出所需的結(jié)果，不但節(jié)省了運(yùn)算，也節(jié)省了保存中間結(jié)果所需的內(nèi)存開(kāi)銷(xiāo)，如下圖所示。
圖1-c 用or條件來(lái)過(guò)濾結(jié)果
可見(jiàn)完成同樣的操作，用不同的SQL語(yǔ)句，效率卻大相徑庭。前兩條SQL語(yǔ)句都不同程度地存在著“壞味道”。
對(duì)于這種簡(jiǎn)單的例子，用戶可以很容易發(fā)現(xiàn)問(wèn)題并選出最佳方案。但對(duì)于一些復(fù)雜的SQL語(yǔ)句，其性能缺陷可能很隱蔽，需要深入分析才有可能挖掘出來(lái)。這對(duì)數(shù)據(jù)庫(kù)的使用者提出了很高的要求。即便是資深的數(shù)據(jù)庫(kù)專(zhuān)家，有時(shí)也很難找出性能劣化的原因。
GaussDB在執(zhí)行SQL語(yǔ)句時(shí)，會(huì)對(duì)其性能表現(xiàn)進(jìn)行分析和記錄，通過(guò)視圖和函數(shù)等手段呈現(xiàn)給用戶。本文將簡(jiǎn)要介紹如何利用GaussDB提供的這些“第一手”數(shù)據(jù)，分析和定位SQL語(yǔ)句中存在的性能問(wèn)題，識(shí)別和消除SQL中的“壞味道”。
GaussDB在執(zhí)行SQL時(shí)，會(huì)對(duì)執(zhí)行計(jì)劃以及執(zhí)行過(guò)程中的資源消耗進(jìn)行記錄和分析，如果發(fā)現(xiàn)異常情況還會(huì)記錄告警信息，用于對(duì)原因進(jìn)行“自診斷”。用戶可以通過(guò)下面的視圖查詢這些信息：
? gs_wlm_session_info
? pgxc_wlm_session_info
? gs_wlm_session_history
? pgxc_wlm_session_history
其中g(shù)s_wlm_session_info是基本表，其余3個(gè)都是視圖。gs_開(kāi)頭的用于查看當(dāng)前CN節(jié)點(diǎn)上收集的信息，pgxc_開(kāi)頭的則包含集群中所有CN收集的信息。各表格和視圖的定義基本相同，如下表所示。
表1-1 自診斷表格&函數(shù)字段定義
表1-2 自診斷表格&函數(shù)字段定義
其中的query字段就是執(zhí)行的SQL語(yǔ)句。通過(guò)分析每個(gè)query對(duì)應(yīng)的各字段，例如執(zhí)行時(shí)間，內(nèi)存，IO，下盤(pán)量和傾斜率等等，可以發(fā)現(xiàn)疑似有問(wèn)題的SQL語(yǔ)句，然后結(jié)合query_plan（執(zhí)行計(jì)劃）字段，進(jìn)一步地加以分析。特別地，對(duì)于一些在執(zhí)行過(guò)程中發(fā)現(xiàn)的異常情況，warning字段還會(huì)以human-readable的形式給出告警信息。目前能夠提供的自診斷信息如下：
優(yōu)化器依賴于表的統(tǒng)計(jì)信息來(lái)生成合理的執(zhí)行計(jì)劃。如果沒(méi)有及時(shí)對(duì)表中各列收集統(tǒng)計(jì)信息，可能會(huì)影響優(yōu)化器的判斷，從而生成較差的執(zhí)行計(jì)劃。如果生成計(jì)劃時(shí)發(fā)現(xiàn)某個(gè)表的單列或多列統(tǒng)計(jì)信息未收集，warning字段會(huì)給出如下告警信息：
Statistic Not Collect: schemaname.tablename(column name list)
此外，如果表格的統(tǒng)計(jì)信息已收集過(guò)（執(zhí)行過(guò)analyze），但是距離上次analyze時(shí)間較遠(yuǎn)，表格內(nèi)容發(fā)生了很大變化，可能使優(yōu)化器依賴的統(tǒng)計(jì)信息不準(zhǔn)，無(wú)法生成最優(yōu)的查詢計(jì)劃。針對(duì)這種情況，可以用pg_total_autovac_tuples系統(tǒng)函數(shù)查詢表格中自從上次分析以來(lái)發(fā)生變化的元組的數(shù)量。如果數(shù)量較大，最好執(zhí)行一下analyze以使優(yōu)化器獲得最新的統(tǒng)計(jì)信息。
執(zhí)行計(jì)劃中的算子，如果能下推到DN節(jié)點(diǎn)執(zhí)行，則只能在CN上執(zhí)行。因?yàn)镃N的數(shù)量遠(yuǎn)小于DN，大量操作堆積在CN上執(zhí)行，會(huì)影響整體性能。如果遇到不能下推的函數(shù)或語(yǔ)法，warning字段會(huì)給出如下告警信息：
SQL is not plan-shipping, reason : %s
如果發(fā)現(xiàn)在進(jìn)行Hash連接時(shí)使用了大表作為內(nèi)表，會(huì)給出如下告警信息：
PlanNode[%d] Large Table is INNER in HashJoin \"%s\"
目前“大表”的標(biāo)準(zhǔn)是平均每個(gè)DN上的行數(shù)大于100，000，并且內(nèi)表行數(shù)是外表行數(shù)的10倍以上。
如果發(fā)現(xiàn)對(duì)大表做等值連接時(shí)使用了NestLoop方式，會(huì)給出如下告警信息：
PlanNode[%d] Large Table with Equal-Condition use Nestloop\"%s\"
目前大表等值連接的判斷標(biāo)準(zhǔn)是內(nèi)表和外表中行數(shù)最大者大于DN的數(shù)量乘以100，000。
數(shù)據(jù)在DN之間分布不均勻，可導(dǎo)致數(shù)據(jù)較多的節(jié)點(diǎn)成為性能瓶頸。如果發(fā)現(xiàn)數(shù)據(jù)傾斜嚴(yán)重，會(huì)給出如下告警信息：
PlanNode[%d] DataSkew:\"%s\", min_dn_tuples:%.0f, max_dn_tuples:%.0f
目前數(shù)據(jù)傾斜的判斷標(biāo)準(zhǔn)是DN中行數(shù)最多者是最少者的10倍以上，且最多者大于100,000。
GaussDB在執(zhí)行SQL語(yǔ)句過(guò)程中會(huì)統(tǒng)計(jì)實(shí)際付出的代價(jià)，并與之前估計(jì)的代價(jià)比較。如果優(yōu)化器對(duì)代價(jià)的估算與實(shí)際的偏差很大，則很可能生成一個(gè)非最優(yōu)化的計(jì)劃。如果發(fā)現(xiàn)代價(jià)估計(jì)不準(zhǔn)確，會(huì)給出如下告警信息：
"PlanNode[%d] Inaccurate Estimation-Rows: \"%s\" A-Rows:%.0f, E-Rows:%.0f
目前的代價(jià)由計(jì)劃節(jié)點(diǎn)返回行數(shù)來(lái)衡量，如果平均每個(gè)DN上實(shí)際/估計(jì)返回行數(shù)大于100,000，并且二者相差10倍以上，則認(rèn)定為代價(jià)估算不準(zhǔn)。
Broadcast主要適合小表。對(duì)于大表來(lái)說(shuō)，通常采用Hash+重分布（redistribute）的方式效率更高。如果發(fā)現(xiàn)計(jì)劃中有大表被廣播的環(huán)節(jié)，會(huì)給出如下告警信息：
PlanNode[%d] Large Table in Broadcast \"%s\"
目前對(duì)大表廣播的認(rèn)定標(biāo)準(zhǔn)為平均廣播到每個(gè)DN上的數(shù)據(jù)行數(shù)大于100,000。
如果對(duì)索引的使用不合理，比如應(yīng)該采用索引掃描的地方卻采用了順序掃描，或者應(yīng)該采用順序掃描的地方卻采用了索引掃描，可能會(huì)導(dǎo)致性能低下。
索引掃描的價(jià)值在于減少數(shù)據(jù)讀取量，因此認(rèn)為索引掃描過(guò)濾掉的行數(shù)越多越好。如果采用索引掃描，但輸出行數(shù)/掃描總行數(shù)>1/1000，并且輸出行數(shù)>10000（對(duì)于行存表）或>100（對(duì)于列存表），則會(huì)給出如下告警信息：
PlanNode[%d] Indexscan is not properly used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f
順序掃描適用于過(guò)濾的行數(shù)占總行數(shù)比例不大的情形。如果采用順序掃描，但輸出行數(shù)/掃描總行數(shù)<=1/1000，并且輸出行數(shù)<=10000（對(duì)于行存表）或<=100（對(duì)于列存表），則會(huì)給出如下告警信息：
PlanNode[%d] Indexscan is ought to be used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f
SQL語(yǔ)句執(zhí)行過(guò)程中，因?yàn)閮?nèi)存不足等原因，可能需要將中間結(jié)果的全部或一部分轉(zhuǎn)儲(chǔ)的磁盤(pán)上。下盤(pán)可能導(dǎo)致性能低下，應(yīng)該盡量避免。如果監(jiān)測(cè)到下盤(pán)量過(guò)大或過(guò)早下盤(pán)等情況，會(huì)給出如下告警信息：
? 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
下盤(pán)可能是因?yàn)榫彌_區(qū)設(shè)置得過(guò)小，也可能是因?yàn)楸淼倪B接順序或連接方式不合理等原因，要結(jié)合具體的SQL進(jìn)行分析。可以通過(guò)改寫(xiě)SQL語(yǔ)句，或者HINT指定連接方式等手段來(lái)解決。
使用自診斷視圖功能，需要將以下變量設(shè)成合適的值：
▲ use_workload_manager（設(shè)成on，默認(rèn)為on）
▲ enable_resource_check（設(shè)成on，默認(rèn)為on）
▲ resource_track_level（如果設(shè)成query，則收集query級(jí)別的信息，如果設(shè)成operator，則收集所有信息，如果設(shè)成none，則以用戶默認(rèn)的log級(jí)別為準(zhǔn)）
▲ resource_track_cost（設(shè)成合適的正整數(shù)。為了不影響性能，只有執(zhí)行代價(jià)大于resource_track_cost語(yǔ)句才會(huì)被收集。該值越大，收集的語(yǔ)句越少，對(duì)性能影響越??；反之越小，收集的語(yǔ)句越多，對(duì)性能的影響越大。）
執(zhí)行完一條代價(jià)大于resource_track_cost后，診斷信息會(huì)存放在內(nèi)存hash表中，可通過(guò)pgxc_wlm_session_history或gs_wlm_session_history視圖查看。
視圖中記錄的有效期是3分鐘，過(guò)期的記錄會(huì)被系統(tǒng)清理。如果設(shè)置enable_resource_record=on，視圖中的記錄每隔3分鐘會(huì)被轉(zhuǎn)儲(chǔ)到gs_wlm_session_info表中，因此3分鐘之前的歷史記錄可以通過(guò)gs_wlm_session_info表或pgxc_wlm_session_info視圖查看。
上一節(jié)提到的自診斷視圖可以顯示已完成SQL的信息。如果要查看正在運(yùn)行的SQL的情況，可以使用下面的視圖：
? gs_wlm_session_statistics
? pgxc_wlm_session_statistics
類(lèi)似地，gs_開(kāi)頭的用于查看當(dāng)前CN節(jié)點(diǎn)上收集的信息，pgxc_開(kāi)頭的則包含集群中所有CN收集的信息。兩個(gè)視圖的定義與上一節(jié)的自診斷視圖基本相同，使用方法也基本一致。通過(guò)觀察其中的字段，可以發(fā)現(xiàn)正在運(yùn)行的SQL中存在的性能問(wèn)題。
例如，通過(guò)“select queryid, duration from gs_wlm_session_statistics order by duration desc limit 10;”可以查詢當(dāng)前運(yùn)行的SQL中，已經(jīng)執(zhí)行時(shí)間最長(zhǎng)的10個(gè)SQL。如果時(shí)間過(guò)長(zhǎng)，可能有必要分析一下原因。
圖2-a 通過(guò)gs_wlm_session_statistics視圖發(fā)現(xiàn)可能hang住SQL
查到queryid后，可以通過(guò)query_plan字段查看該SQL的執(zhí)行計(jì)劃，分析其中可能存在的性能瓶頸和異常點(diǎn)。
圖2-b 通過(guò)gs_wlm_session_statistics視圖查看當(dāng)前SQL的執(zhí)行計(jì)劃
再下一步，可以結(jié)合等待視圖等其他手段定位性能劣化的原因。
圖2-c 通過(guò)gs_wlm_session_statistics視圖結(jié)合等待視圖定位性能問(wèn)題
另外，活動(dòng)視圖pg_stat_activity也能提供一些當(dāng)前執(zhí)行SQL的信息。
除了針對(duì)逐條SQL進(jìn)行分析，還可以利用統(tǒng)計(jì)信息發(fā)現(xiàn)SQL中的壞味道。另一篇文章“Unique SQL特性原理與應(yīng)用”中提到的Unique SQL特性，能夠針對(duì)執(zhí)行計(jì)劃相同的一類(lèi)SQL進(jìn)行了性能統(tǒng)計(jì)。與自診斷視圖不同的是，如果同一個(gè)SQL被多次執(zhí)行，或者多個(gè)SQL語(yǔ)句的結(jié)構(gòu)相同，只有條件中的常量值不同。這些SQL在Unique SQL視圖中會(huì)合并為一條記錄。因此使用Unique SQL視圖能更容易看出那些類(lèi)型的SQL語(yǔ)句存在性能問(wèn)題。
利用這一特性，可以找出某一指標(biāo)或者某一資源占用量最高/最差的那些SQL類(lèi)型。這樣的SQL被稱(chēng)為“Top SQL”。例如，查找占用CPU時(shí)間最長(zhǎng)的SQL語(yǔ)句，可以用如下SQL：
select unique_sql_id,query,cpu_time from pgxc_instr_unique_sql order by cpu_time desc limit 10。
發(fā)現(xiàn)SQL中的壞味道是性能調(diào)優(yōu)的前提。GaussDB對(duì)數(shù)據(jù)庫(kù)的運(yùn)行狀況進(jìn)行了SQL級(jí)別的監(jiān)控和記錄。這些打點(diǎn)記錄的數(shù)據(jù)可以幫助用戶發(fā)現(xiàn)可能存在的異常情況，“嗅”出潛在的壞味道。從這些數(shù)據(jù)和提示信息出發(fā)，結(jié)合其他視圖和工具，可以定位出壞味道的來(lái)源，進(jìn)而有針對(duì)性地進(jìn)行優(yōu)化。
上述內(nèi)容就是GaussDB for DWS是如何識(shí)別壞味道的SQL，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
重慶分公司
重慶分公司
