問題現(xiàn)象

在DWS實際業(yè)務場景中因I/O高、I/O瓶頸導致的性能問題較多，其中應用業(yè)務設計不合理導致的問題占大多數(shù)。本文從應用業(yè)務優(yōu)化角度，以常見觸發(fā)I/O慢的業(yè)務SQL場景為例，指導如何通過優(yōu)化業(yè)務去提升I/O效率和降低I/O。

確定I/O瓶頸&識別高I/O的語句

1. 通過以下內(nèi)容掌握SQL級I/O問題分析的基礎知識。
   • PGXC_THREAD_WAIT_STATUS視圖功能。
   • EXPLAIN功能，需了解并熟悉Scan算子、A-time、A-rows、E- rows，詳細內(nèi)容參見：SQL執(zhí)行計劃。

2. 通過pgxc_thread_wait_status視圖查看并確定I/O瓶頸。全部狀態(tài)信息請參見PG_THREAD_WAIT_STATUS。

   1	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;

   I/O瓶頸時常見的主要狀態(tài)如下表所示。

   表1 I/O常見狀態(tài)

   Wait status	Wait event
   wait io：等待I/O完成。	BufFileRead：從臨時文件中讀取數(shù)據(jù)到指定buffer。 BufFileWrite：向臨時文件中寫入指定buffer中的內(nèi)容。 DataFileRead：同步讀取表數(shù)據(jù)文件。 DataFileWrite：向表數(shù)據(jù)文件寫入內(nèi)容。 ......
   acquire lwlock：等待獲取輕量級鎖。	WALWriteLock：用于避免并發(fā)WAL寫盤。
   wait wal sync：等待特定LSN的wal log完成到備機的同步。	NA
   wait data sync：等待完成數(shù)據(jù)頁到備機的同步。	NA
   Material | Material - write file：當前是Material算子，write file表示Material算子正在將數(shù)據(jù)寫入磁盤。	NA

3. 獲取高I/O消耗的SQL。

   先通過OS命令識別消耗高的線程，然后結合DWS的線程號信息找到消耗高的業(yè)務SQL，獲取工具和操作方法請下載iowatcher。

場景1：列存小CU膨脹

某業(yè)務SQL查詢出390871條數(shù)據(jù)需43248ms，分析計劃主要耗時在Cstore Scan。

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

觸發(fā)因素：對列存表（尤其是分區(qū)表）進行高頻小批量導入會造成CU膨脹。

處理方法

1. 列存表的數(shù)據(jù)入庫方式修改為攢批入庫，單分區(qū)單批次入庫數(shù)據(jù)量需大于DN個數(shù)*6W。
2. 如果因業(yè)務原因無法攢批入庫，則需定期VACUUM FULL此類高頻小批量導入的列存表。
3. 當小CU膨脹很快時，頻繁VACUUM FULL也會消耗大量I/O，甚至加劇整個系統(tǒng)的I/O瓶頸，此場景建議修改列存表為行存表（CU長期膨脹嚴重的情況下，列存的存儲空間優(yōu)勢和順序掃描性能優(yōu)勢將不復存在）。

場景2：臟數(shù)據(jù)&數(shù)據(jù)清理

某業(yè)務SQL總執(zhí)行時間2.519s，其中Scan占了2.516s，同時該表的掃描最終只掃描到0條符合條件數(shù)據(jù)，過濾了20480條數(shù)據(jù)，即總共掃描了20480+0條數(shù)據(jù)卻消耗了2s+，掃描時間與掃描數(shù)據(jù)量嚴重不符，此現(xiàn)象可判斷為由于臟數(shù)據(jù)多從而影響掃描和I/O效率。

查看表臟頁率為99%，VACUUM FULL后性能優(yōu)化到100ms左右。

觸發(fā)因素：表頻繁執(zhí)行UPDATE/DELETE導致臟數(shù)據(jù)過多，且長時間未VACUUM FULL清理。

處理方法

1. 對頻繁UPDATE/DELETE產(chǎn)生臟數(shù)據(jù)的表，定期VACUUM FULL，因大表的VACUUM FULL也會消耗大量I/O，因此需要在業(yè)務低峰時執(zhí)行，避免加劇業(yè)務高峰期I/O壓力。
2. 當臟數(shù)據(jù)產(chǎn)生很快，頻繁VACUUM FULL也會消耗大量I/O，甚至加劇整個系統(tǒng)的I/O瓶頸，這時需要考慮臟數(shù)據(jù)的產(chǎn)生是否合理。針對頻繁DELETE的場景，可以考慮如下方案：
   1. 全量DELETE修改為TRUNCATE或者使用臨時表替代。
   2. 定期DELETE某時間段數(shù)據(jù)，使用分區(qū)表并使用TRUNCATE或DROP分區(qū)替代。

場景3：表存儲傾斜

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

通過table_distribution發(fā)現(xiàn)所有數(shù)據(jù)傾斜到了dn_6009單個DN，修改分布列使得表存儲分布均勻后，max dn time和min dn time基本維持在相同水平400ms左右，Scan時間從6554ms優(yōu)化到431ms。

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

解決辦法：修改表的分布列使表的存儲分布均勻，分布列選擇原則參見選擇分布列。

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

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

對目標表ANALYZE后，計劃能夠自動選擇索引，性能從3s+優(yōu)化到2ms+，極大降低I/O消耗。

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

• 過濾條件列上沒建索引。
• 有索引但是計劃沒選索引掃描。

觸發(fā)因素：

• 常用過濾條件列沒有建索引。
• 表中數(shù)據(jù)因執(zhí)行DML操作后產(chǎn)生數(shù)據(jù)變化未及時ANALYZE，導致優(yōu)化器無法選擇索引掃描計劃，ANALYZE介紹參見ANALYZE。

處理方式：

1. 對行存表常用過濾列增加索引，索引基本設計原則：
   • 索引列選擇distinct值多，且常用于過濾條件，過濾條件多時可以考慮建組合索引，組合索引中distinct值多的列排在前面，索引個數(shù)不宜超過3個。
   • 大量數(shù)據(jù)帶索引導入會產(chǎn)生大量I/O，如果該表涉及大量數(shù)據(jù)導入，需嚴格控制索引個數(shù)，建議導入前先將索引刪除，導入完成后再重新建索引。

2. 對頻繁做DML操作的表，業(yè)務中加入及時ANALYZE，主要場景：
   • 表數(shù)據(jù)從無到有。
   • 表頻繁進行INSERT/UPDATE/DELETE。
   • 表數(shù)據(jù)即插即用，需要立即訪問且只訪問剛插入的數(shù)據(jù)。

場景5：無分區(qū)、有分區(qū)不剪枝

例如某業(yè)務表經(jīng)常使用createtime時間列作為過濾條件獲取特定時間數(shù)據(jù)，對該表設計為分區(qū)表后沒有走分區(qū)剪枝（Selected Partitions數(shù)量多），Scan花了701785ms，I/O效率極低。

在增加分區(qū)鍵createtime作為過濾條件后，Partitioned scan走分區(qū)剪枝（Selected Partitions數(shù)量極少），性能從700s優(yōu)化到10s，I/O效率極大提升。

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

• 未設計成分區(qū)表。
• 設計了分區(qū)沒使用分區(qū)鍵做過濾條件。
• 分區(qū)鍵做過濾條件時，對列值有函數(shù)轉(zhuǎn)換。

觸發(fā)因素：未合理使用分區(qū)表和分區(qū)剪枝功能，導致掃描效率低。

處理方式：

• 對按照時間特征存儲和訪問的大表設計成分區(qū)表。
• 分區(qū)鍵一般選離散度高、常用于查詢過濾條件中的時間類型的字段。
• 分區(qū)間隔一般參考高頻的查詢所使用的間隔，需要注意的是針對列存表，分區(qū)間隔過?。ɡ绨葱r）可能會導致小文件過多的問題，一般建議最小間隔為按天。

場景6：行存表求count值

某行存大表頻繁全表count（指不帶過濾條件或者過濾條件過濾很少數(shù)據(jù)的count），其中Scan花費43s，持續(xù)占用大量I/O，此類作業(yè)并發(fā)起來后，整體系統(tǒng)I/O持續(xù)100%，觸發(fā)I/O瓶頸，導致整體性能慢。

對比相同數(shù)據(jù)量的列存表（A-rows均為40960000），列存的Scan只花費14ms，I/O占用極低。

觸發(fā)因素：行存表因其存儲方式的原因，全表scan的效率較低，頻繁的對大表全表掃描，導致I/O持續(xù)占用。

解決辦法：

• 業(yè)務側審視頻繁全表count的必要性，降低全表count的頻率和并發(fā)度。
• 如果業(yè)務類型符合列存表，則將行存表修改為列存表，提高I/O效率。

場景7：行存表求max值

計算某行存表某列的max值，花費了26772ms，此類作業(yè)并發(fā)起后，整體系統(tǒng)I/O持續(xù)100%，觸發(fā)I/O瓶頸，導致整體性能慢。

針對max列增加索引后，語句耗時從26s優(yōu)化到32ms，極大減少I/O消耗。

觸發(fā)因素：行存表max值逐個scan符合條件的值來計算max，當scan的數(shù)據(jù)量很大時，會持續(xù)消耗I/O。

解決辦法：給max列增加索引，憑借B-Tree索引數(shù)據(jù)有序存儲的特征，加速掃描過程，降低I/O消耗。

場景8：大量數(shù)據(jù)帶索引導入

某業(yè)務場景數(shù)據(jù)往DWS同步時，延遲嚴重，集群整體I/O壓力大。

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

觸發(fā)因素：大量數(shù)據(jù)帶索引（一般超過3個）導入（insert/copy/merge into）會產(chǎn)生大量xlog，導致主備同步慢，備機長期Catchup，整體I/O利用率飆高。

解決方案：

• 嚴格控制每張表的索引個數(shù)，建議3個以內(nèi)。
• 大量數(shù)據(jù)導入前先將索引刪除，導入完成后再重新建索引。

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

某業(yè)務場景出現(xiàn)備DN持續(xù)catchup，I/O壓力大，觀察某個SQL等待視圖在wait wal sync。

排查業(yè)務發(fā)現(xiàn)某查詢語句執(zhí)行時間較長，執(zhí)行kill命令后恢復。

觸發(fā)因素：行存表大量數(shù)據(jù)入庫后，首次查詢觸發(fā)page hint產(chǎn)生大量X LOG ，觸發(fā)主備同步慢及大量I/O消耗。

解決措施：

• 對該類一次性訪問大量新數(shù)據(jù)的場景，修改行存表為列存表。
• 可關閉wal_log_hints和enable_crc_check參數(shù)（不推薦該方式，因故障期間有數(shù)據(jù)丟失風險）。

場景10：小文件多IOPS高

某業(yè)務執(zhí)行過程中，整個集群IOPS飆高，另外當出現(xiàn)集群故障后，長期Building不成功，IOPS飆高，相關表信息如下：

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

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

解決辦法：

• 整改列存分區(qū)間隔，減少分區(qū)個數(shù)來降低文件個數(shù)。
• 列存表修改為行存表，行存的存儲特征決定其文件個數(shù)不會像列存般膨脹嚴重。

小結

通過前面的場景總結得出，提升I/O使用效率可分為兩個維度，即提升I/O的存儲效率和計算效率（又稱訪問效率）。

• 提升存儲效率包括整合小CU、減少臟數(shù)據(jù)、消除存儲傾斜等。
• 提升計算效率包括分區(qū)剪枝、索引掃描等，可根據(jù)實際業(yè)務場景靈活處理。