10個常見觸發IO瓶頸的高頻業務場景

華為雲開發者聯盟發表於2022-06-09
摘要：本文從應用業務優化角度，以常見觸發IO慢的業務SQL場景為例，指導如何通過優化業務去提升IO效率和降低IO。

本文分享自華為雲社群《GaussDB(DWS)效能優化之業務降IO優化》，作者：along_2020。

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瓶頸時常見等待狀態如下：

 10個常見觸發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

10個常見觸發IO瓶頸的高頻業務場景

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

10個常見觸發IO瓶頸的高頻業務場景

觸發因素：對列存表（分割槽表尤甚）進行高頻小批量匯入會造成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效率。

10個常見觸發IO瓶頸的高頻業務場景

檢視錶髒頁率為99%，Vacuum Full後效能優化到100ms左右

10個常見觸發IO瓶頸的高頻業務場景

觸發因素：表頻繁執行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的詳細資訊，基本可以確定為表儲存傾斜導致

10個常見觸發IO瓶頸的高頻業務場景

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

10個常見觸發IO瓶頸的高頻業務場景

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

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

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

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

10個常見觸發IO瓶頸的高頻業務場景

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

10個常見觸發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效率極低。

10個常見觸發IO瓶頸的高頻業務場景

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

10個常見觸發IO瓶頸的高頻業務場景

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

  • 未設計成分割槽表
  • 設計了分割槽沒使用分割槽鍵做過濾條件
  • 分割槽鍵做過濾條件時，對列值有函式轉換

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

處理方式：

  • 對按照時間特徵儲存和訪問的大表設計成分割槽表
  • 分割槽鍵一般選離散度高、常用於查詢filter條件中的時間型別的欄位
  • 分割槽間隔一般參考高頻的查詢所使用的間隔，需要注意的是針對列存表，分割槽間隔過小（例如按小時）可能會導致小檔案過多的問題，一般建議最小間隔為按天。

場景6：行存表求count值

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

10個常見觸發IO瓶頸的高頻業務場景

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

10個常見觸發IO瓶頸的高頻業務場景

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

解決辦法：

  • 業務側審視訊繁全表count的必要性，降低全表count的頻率和併發度
  • 如果業務型別符合列存表，則將行存表修改為列存表，提高IO效率

場景7：行存表求max值

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

10個常見觸發IO瓶頸的高頻業務場景

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

10個常見觸發IO瓶頸的高頻業務場景

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

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

場景8：大量資料帶索引匯入

某客戶場景資料往DWS同步時，延遲嚴重，叢集整體IO壓力大。

10個常見觸發IO瓶頸的高頻業務場景

後臺檢視等待檢視有大量wait wal sync和WALWriteLock狀態，均為xlog同步狀態

 10個常見觸發IO瓶頸的高頻業務場景

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

解決方案：

  • 嚴格控制每張表的索引個數，建議3個以內
  • 大量資料匯入前先將索引刪除，導數完畢後再重新建索引；

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

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

10個常見觸發IO瓶頸的高頻業務場景

排查業務發現某查詢語句執行時間較長，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;

10個常見觸發IO瓶頸的高頻業務場景

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

解決辦法：

  • 整改列存分割槽間隔，減少分割槽個數來降低檔案個數
  • 列存表修改為行存表，行存的儲存特徵決定其檔案個數不會像列存那麼膨脹嚴重

三、小結

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

10個常見觸發IO瓶頸的高頻業務場景

華為夥伴暨開發者大會2022火熱來襲，重磅內容不容錯過！

【精彩活動】

勇往直前·做全能開發者→12場技術直播前瞻，8大技術寶典高能輸出，還有程式碼密室、知識競賽等多輪神祕任務等你來挑戰。即刻闖關，開啟終極大獎！點選踏上全能開發者晉級之路吧！

【技術專題】

未來已來，2022技術探祕→華為各領域的前沿技術、重磅開源專案、創新的應用實踐，站在智慧世界的入口，探索未來如何照進現實，乾貨滿滿點選瞭解

 

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章