GaussDB(DWS)性能優化之業務降IO優化【這次高斯不是數學家】

IO高？業務慢？在DWS實際業務場景中因IO高、IO瓶頸導致的性能問題非常多，其中應用業務設計不合理導致的問題占大多數。本文從應用業務優化角度，以常見觸發IO慢的業務SQL場景為例，指導如何通過優化業務去提升IO效率和降低IO。

說明 ：因磁盤故障（如慢盤）、raid卡讀寫策略（如Write Through）、集群主備不均等非應用業務原因導致的IO高不在本次討論。

一、確定IO瓶頸&識別高IO的語句

1、查等待視圖確定IO瓶頸

SELECT wait_status,wait_event,count(*) AS cnt FROM pgxc_thread_wait_status WHERE wait_status <> 'wait cmd' AND wait_status <> 'synchronize quit' AND wait_status <> 'none' GROUP BY 1,2 ORDER BY 3 DESC limit 50;

IO瓶頸時常見等待狀態如下：

2、抓取高IO消耗的SQL

主要思路為先通過OS命令識別消耗高的線程，然后結合DWS的線程號信息找到消耗高的業務SQL，具體方法參見附件中iowatcher.py腳本和README使用介紹

3、SQL級IO問題分析基礎

在抓取到消耗IO高的業務SQL后怎么分析？主要掌握以下兩點基礎知識：

1）PGXC_THREAD_WAIT_STATUS視圖功能，詳細介紹參見：

https://support.huaweicloud.com/devg2-dws/dws_0402_0892.html

2）EXPLAIN功能，至少需掌握的知識點有Scan算子、A-time、A-rows、E- rows，詳細介紹參見：

https://bbs.huaweicloud.com/blogs/197945

二、常見觸發IO瓶頸的高頻業務場景

場景1：列存小CU膨脹

某業務SQL查詢出390871條數據需43248ms，分析計劃主要耗時在Cstore Scan

Cstore Scan的詳細信息中，每個DN掃描出2w左右的數據，但是掃描了有數據的CU(CUSome) 155079個，沒有數據的CU（CUNone） 156375個，說明當前小CU、未命中數據的CU極多，也即CU膨脹嚴重。

觸發因素：對列存表（分區表尤甚）進行高頻小批量導入會造成CU膨脹

處理方法：

1、列存表的數據入庫方式修改為攢批入庫，單分區單批次入庫數據量大于DN個數*6W為宜

2、如果確因業務原因無法攢批，則考慮次選方案，定期VACUUM FULL此類高頻小批量導入的列存表。

3、當小CU膨脹很快時，頻繁VACUUM FULL也會消耗大量IO，甚至加劇整個系統的IO瓶頸，這時需考慮整改為行存表（CU長期膨脹嚴重的情況下，列存的存儲空間優勢和順序掃描性能優勢將不復存在）。

場景2：臟數據&數據清理

某SQL總執行時間2.519s，其中Scan占了2.516s，同時該表的掃描最終只掃描到0條符合條件數據，過濾了20480條數據，也即總共掃描了20480+0條數據卻消耗了2s+，這種掃描時間與掃描數據量嚴重不符的情況，基本就是臟數據多影響掃描和IO效率。

查看表臟頁率為99%，Vacuum Full后性能優化到100ms左右

觸發因素：表頻繁執行update/delete導致臟數據過多，且長時間未VACUUM FULL清理

處理方法：

對頻繁update/delete產生臟數據的表，定期VACUUM FULL，因大表的VACUUM FULL也會消耗大量IO，因此需要在業務低峰時執行，避免加劇業務高峰期IO壓力。

當臟數據產生很快，頻繁VACUUM FULL也會消耗大量IO，甚至加劇整個系統的IO瓶頸，這時需要考慮臟數據的產生是否合理。針對頻繁delete的場景，可以考慮如下方案：1）全量delete修改為truncate或者使用臨時表替代 2）定期delete某時間段數據，設計成分區表并使用truncate&drop分區替代

場景3：表存儲傾斜

例如表Scan的A-time中，max time dn執行耗時6554ms，min time dn耗時0s，dn之間掃描差異超過10倍以上，這種集合Scan的詳細信息，基本可以確定為表存儲傾斜導致

通過table_distribution發現所有數據傾斜到了dn_6009單個dn，修改分布列使的表存儲分布均勻后，max dn time和min dn time基本維持在相同水平400ms左右，Scan時間從6554ms優化到431ms。

觸發因素：分布式場景，表分布列選擇不合理會導致存儲傾斜，同時導致DN間壓力失衡，單DN IO壓力大，整體IO效率下降。

解決辦法：修改表的分布列使表的存儲分布均勻，分布列選擇原則參《GaussDB 8.x.x 產品文檔》中“表設計最佳實踐”之“選擇分布列章節”。

場景4：無索引、有索引不走

例如某點查詢，Seq Scan掃描需要3767ms，因涉及從4096000條數據中獲取8240條數據，符合索引掃描的場景（海量數據中尋找少量數據），在對過濾條件列增加索引后，計劃依然是Seq Scan而沒有走Index Scan。

對目標表analyze后，計劃能夠自動選擇索引，性能從3s+優化到2ms+，極大降低IO消耗

常見場景：行存大表的查詢場景，從大量數據中訪問極少數據，沒走索引掃描而是走順序掃描，導致IO效率低，不走索引常見有兩種情況：

過濾條件列上沒建索引

有索引但是計劃沒選索引掃描

觸發因素：

常用過濾條件列沒有建索引

表中數據因DML產生數據特征變化后未及時ANALYZE導致優化器無法選擇索引掃描計劃，ANALYZE介紹參見https://bbs.huaweicloud.com/blogs/192029

處理方式：

1、對行存表常用過濾列增加索引，索引基本設計原則：

索引列選擇distinct值多，且常用于過濾條件，過濾條件多時可以考慮建組合索引，組合索引中distinct值多的列排在前面，索引個數不宜超過3個

大量數據帶索引導入會產生大量IO，如果該表涉及大量數據導入，需嚴格控制索引個數，建議導入前先將索引刪除，導數完畢后再重新建索引；

2、對頻繁做DML操作的表，業務中加入及時ANALYZE，主要場景：

表數據從無到有

表頻繁進行INSERT/UPDATE/DELETE

表數據即插即用，需要立即訪問且只訪問剛插入的數據

場景5：無分區、有分區不剪枝

例如某業務表進場使用createtime時間列作為過濾條件獲取特定時間數據，對該表設計為分區表后沒有走分區剪枝（Selected Partitions數量多），Scan花了701785ms，IO效率極低。

在增加分區鍵creattime作為過濾條件后，Partitioned scan走分區剪枝（Selected Partitions數量極少），性能從700s優化到10s，IO效率極大提升。

常見場景：按照時間存儲數據的大表，查詢特征大多為訪問當天或者某幾天的數據，這種情況應該通過分區鍵進行分區剪枝（只掃描對應少量分區）來極大提升IO效率，不走分區剪枝常見的情況有：

未設計成分區表

設計了分區沒使用分區鍵做過濾條件

分區鍵做過濾條件時，對列值有函數轉換

觸發因素：未合理使用分區表和分區剪枝功能，導致掃描效率低

處理方式：

對按照時間特征存儲和訪問的大表設計成分區表

分區鍵一般選離散度高、常用于查詢filter條件中的時間類型的字段

分區間隔一般參考高頻的查詢所使用的間隔，需要注意的是針對列存表，分區間隔過小（例如按小時）可能會導致小文件過多的問題，一般建議最小間隔為按天。

場景6：行存表求count值

例如某行存大表頻繁全表count（指不帶filter條件或者filter條件過濾很少數據的count），其中Scan花費43s，持續占用大量IO，此類作業并發起來后，整體系統IO持續100%，觸發IO瓶頸，導致整體性能慢。

對比相同數據量的列存表（A-rows均為40960000），列存的Scan只花費14ms，IO占用極低

觸發因素：行存表因其存儲方式的原因，全表scan的效率較低，頻繁的大表全表掃描，導致IO持續占用。

解決辦法：

業務側審視頻繁全表count的必要性，降低全表count的頻率和并發度

如果業務類型符合列存表，則將行存表修改為列存表，提高IO效率

場景7：行存表求max值

例如求某行存表某列的max值，花費了26772ms，此類作業并發起來后，整體系統IO持續100%，觸發IO瓶頸，導致整體性能慢。

針對max列增加索引后，語句耗時從26s優化到32ms，極大減少IO消耗

觸發因素：行存表max值逐個scan符合條件的值來計算max，當scan的數據量很大時，會持續消耗IO

解決辦法：給max列增加索引，依靠btree索引天然有序的特征，加速掃描過程，降低IO消耗。

場景8：大量數據帶索引導入

某客戶場景數據往DWS同步時，延遲嚴重，集群整體IO壓力大。

后臺查看等待視圖有大量wait wal sync和WALWriteLock狀態，均為xlog同步狀態

觸發因素：大量數據帶索引（一般超過3個）導入（insert/copy/merge into）會產生大量xlog，導致主備同步慢，備機長期Catchup，整體IO利用率飆高。歷史案例參考：https://bbs.huaweicloud.com/blogs/242269

解決方案：

嚴格控制每張表的索引個數，建議3個以內

大量數據導入前先將索引刪除，導數完畢后再重新建索引；

場景9：行存大表首次查詢

某客戶場景出現備DN持續Catcup，IO壓力大，觀察某個sql等待視圖在wait wal sync

排查業務發現某查詢語句執行時間較長，kill后恢復

觸發因素：行存表大量數據入庫后，首次查詢觸發page hint產生大量XLOG，觸發主備同步慢及大量IO消耗。

解決措施：

對該類一次性訪問大量新數據的場景，修改為列存表

關閉wal_log_hints和enable_crc_check參數（故障期間有丟數風險，不推薦）

場景10：小文件多IOPS高

某業務現場一批業務起來后，整個集群IOPS飆高，另外當出現集群故障后，長期building不完，IOPS飆高，相關表信息如下：

SELECT relname,reloptions,partcount FROM pg_class c INNER JOIN ( SELECT parented,count(*) AS partcount FROM pg_partition GROUP BY parentid ) s ON c.oid = s.parentid ORDER BY partcount DESC;

觸發因素：某業務庫大量列存多分區（3000+）的表，導致小文件巨多（單DN文件2000w+），訪問效率低，故障恢復Building極慢，同時building也消耗大量IOPS，發向影響業務性能。

解決辦法：

整改列存分區間隔，減少分區個數來降低文件個數

列存表修改為行存表，行存的存儲特征決定其文件個數不會像列存那么膨脹嚴重

三、小結

經過前面案例，稍微總結下不難發現，提升IO使用效率概括起來可分為兩個維度，即提升IO的存儲效率和計算效率（又稱訪問效率），提升存儲效率包括整合小CU、減少臟數據、消除存儲傾斜等，提升計算效率包括分區剪枝、索引掃描等，大家根據實際場景靈活處理即可。

【這次高斯不是數學家】有獎征文火熱進行中：https://bbs.huaweicloud.com/blogs/345260

附件： iowatcher.rar 4.54KB 下載次數：0次

應用性能調優 數據倉庫服務 GaussDB(DWS)

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

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