GaussDB(DWS)性能調優(yōu)：列存表scan性能優(yōu)化

1.問題背景

某局點出現如下業(yè)務場景：從存量清單表中，根據條碼，合同號等條件，查詢明細數據，表總數據量有3億。一次業(yè)務請求包含10個并發(fā)的查詢語句，需要1秒內返回結果集。但是多次優(yōu)化之后并發(fā)性能依舊長達4s左右。

2.???硬件配置

硬件配置信息：集群?6個數據節(jié)點：每個節(jié)點都是RH5885服務器，配置1T內存、?144個CPU core、SSD?存儲空間

3.???歷史優(yōu)化

對于這個業(yè)務場景需求，客戶做了多種性能優(yōu)化嘗試，優(yōu)化動作主要分為如下三個階段

l??第一版本：調試分布鍵

分析業(yè)務數據特性，并測試驗證選擇最合適的分布鍵，既能保障數據不傾斜，又能利用分布鍵做為條件過濾數據

調整后單個查詢響應從平均9秒降到4~5秒

l??第二版本：添加PCK&限制結果集

接口上線后，發(fā)現業(yè)務中會有幾個高頻條件字段，同時有些查詢返回結果集過大，有幾十萬記錄，不符合前端查詢響應要求，所以添加了限制，單次最大只能返回1萬條記錄；同時對合同號添加了PCK

調整后單個查詢響應從平均5秒降到2秒

l??第三版本：使用SMP，以資源換性能

從系統(tǒng)監(jiān)控發(fā)現資源使用率低，建議使用SMP，通過提高CPU資源使用率的方式提升查詢性能。根據現網CPU配置推薦設置query_dop=8

調整后單個查詢響應從2秒降到了400MS

版本上線后，生產使用發(fā)現查詢性能劣化到3~4秒，但是把SQL提取之后單獨運行時性能達到400ms。分析平臺日志發(fā)現下日志中會每10個請求發(fā)起時間點很接近，然后再找到下游調用方了解，才確認是會每此業(yè)務請求包含10個類似SQL語句的并發(fā)調用。后續(xù)測試確認并發(fā)查詢時性能下降，導致性能不達標。

4.???優(yōu)化分析

4.1.?SQL語句分析

業(yè)務SQL是如下簡單的單表查詢語句，SQL語句未發(fā)現明顯的不下推、不能提升子查詢等明顯低效性能點。需要進一步獲取performance信息分析性能瓶頸。

SELECT DISTINCT SN::text AS SnNo , ItemNo::text AS PartNumber FROM dm_ioc.RPT_ServiceManagementBySN a where 1=1 and POSITION(','||a.ItemNo||',' in ','||'0835TXWY'||',') > 0 and (a.ServiceQuoteItem ='Y' or bids_ismeduimquoteitem=1) and a.ContractCountry ='China' order by ContractNo,SN limit 10000

4.2.?性能瓶頸分析

執(zhí)行并發(fā)查詢，獲取performance信息，典型信息如下，詳細信息見附件

發(fā)現耗時主要有兩個

1) ? 數據收集（GATHER和LOCAL GATHER），這個算子主要是從相關線程收集數據，這個算子耗時一般分為兩個場景

a) ??數據量大，導致數據收集耗時特別長

b) ?CN申請線程，DN上創(chuàng)建STREAM線程等計算相關線程初始化耗時長

2) ??數據掃描(Scan)耗時長

分析Scan算子，發(fā)現順序掃描（Cstore Scan）時，RoughCheck（根據稀疏索引排除查詢不相關的數據）時排除掉的元組為0，即稀疏索引沒有預過濾掉任何CU

根據上述分析可以看到當前查詢性能主要損耗在計算相關線程初始化上，但是如果把SMP去掉（設置query_dop為1），底層數據掃描耗時會變長，同樣導致性能不達標。和相關維護和業(yè)務人員也確認了這點

4.3.?性能優(yōu)化

4.3.1.?優(yōu)化思路

找到性能瓶頸點之后就可以針對性進行性能優(yōu)化，我們把優(yōu)化的重點放在Scan性能的提升上，期待在不設置SMP時通過Scan性能的大幅提升來優(yōu)化性能。

根據表掃描信息分析，我們發(fā)現，表掃描的filter條件可以過濾掉376029639條記錄，輸出149條記錄，可以過濾掉99.99996037%的元組，效果非常明顯。

分析表的掃描條件，通過測試驗證發(fā)現過濾效果最明顯的條件為POSITION(','||a.ItemNo||',' in ','||'0835TXWY'||',') > 0，過濾效果在99.99%以上，其它表達式基本沒有起到過濾效果。進一步分析RoughCheck，發(fā)現RoughCheck排除掉的元組為0，稀疏索引沒有任何過濾效果

通常來講，這類過濾效果非常明顯的filter是典型的構建PCK和索引場景，但是這個filter條件為函數表達式，導致此filter條件即沒有辦法構建PCK又沒辦法構建索引。因此我們嘗試跟業(yè)務人員溝通，看能夠改寫POSITION約束，使其支持索引和PCK

4.3.2.?業(yè)務分析

跟客戶業(yè)務人員進一步溝通position函數的業(yè)務含義，發(fā)現

1.?position函數的第二個參數如果個合同號通過’,’連接起來的子串

2. 函數功能是查詢能跟匹配到第二個參數中任一合同號的記錄

第二個參數通過如下的方式從客戶端輸入

這樣寫的原因是從在客戶界面上操作的方便性以及常規(guī)的操作習慣上來講，在外部輸入時不會在合同號上引號，因此業(yè)務人員希望把界面輸入的所有內容作為字符串，然后定義一個函數position，實現滿足指定合同號的記錄篩選。導致在SQL拼接時，此約束沒有寫成常見的索引支持的表達式itemno in ('0AFR2C', ‘0A672C’, ’0A902C',?'0AY72C'')

4.3.3.?最終優(yōu)化方案

在了解這些原因之后，我們和業(yè)務人員溝通，采取以下優(yōu)化手段

1) 在業(yè)務層在接受客戶界面輸入的合同號之后，內部在拼接SQL之前，進行數據預處理，把函數的邏輯轉化為itemno in ('0AFR2C', ‘0A672C’, ’0A902C',?'0AY72C'')式的in約束

2) 在itemno上建PCK以及索引，通過PCK和索引的雙重優(yōu)化來提升性能

采取這兩個措施之后，標準并發(fā)測試場景下，SQL查詢性能提升到90ms，端到端的性能從4s提升到300ms

5. 優(yōu)化總結

5.1. 關于列存點查性能

通常場景下，列存表的點差性能比行存表的點查性能要差，但是在如下場景下列存表的索引查詢性能也非常好

1）寬表的少量列查詢

列存是按照列存儲的，寬表的少量列查詢時，列存表只需要讀取少量查詢相關列即可，這可能會導致列存表掃描的數據量小于行存表的數據量，體現列存表掃描的性能優(yōu)勢

2）索引列上局部聚簇

通過在索引列上做PCK，通過PCK的聚簇效果減小掃描的CU個數，導致列存表掃描的數據量小于行存表的數據量。

注：行存表有全局聚簇的手段，詳見產品文檔的CLUSTER命令

5.2. 關于優(yōu)化思路

SQL語句優(yōu)一些建議：

1) ?梳理性能問題業(yè)務，找出執(zhí)行性能差的SQL語句

2) 分析業(yè)務流程，確認SQL全生命周期的耗時分布，如果數據庫執(zhí)行時間長，那么進入下一步

3) 分析是否存在不下推場景。如果存在，通過改寫SQL消除不下推因素，如果SQL語句可以下推，進入下一步

4) 通過performance找性能瓶頸點：根據performance顯示的算子耗時，查詢耗時最長的算子，這個算子就是導致執(zhí)行耗時時間比較長的最基本因素

5) 針對性能瓶頸點，給出優(yōu)化思路和方案

優(yōu)化不要局限在SQL語句本身，要和具體硬件配置和業(yè)務背景相結合

名詞解釋

1. ?【CU】

列存的基本存儲單元，同時也是列存表掃描時基本的讀取單元。列存表讀取數據時會把全部CU的數據讀上來，而不能讀取CU中的一部分數據。

2. ?【PCK】

Partial Cluster Key（局部聚簇）的簡稱，是列存表的一種局部聚簇技術，該技術可以在批量數據導入時，把數據按照PCK指定的列(單列或多列)進行局部排序，實現不同值區(qū)間的數據存儲到不同的CU上。這種局部聚簇結合列存表CU的內置min/max稀疏索引，可以大幅提升表在PCK列上簡單filter的過濾效果

使用PCK需要注意的幾點

1) ?只有列存表支持PCK

2) 一個表只能定義一個PCK

3) ?PCK列上的fliter要滿足以下約束

a) ?簡單的<、>、≤、≥、=表達式

b) ?一側是PCK列，而不能是列相關表達式、函數

c) ?另一側是常量

d) ?表達式計算時，PCK列不需要進行隱士類型轉換

4) ?上個步驟的filter條件要可以過濾掉較多的元組，減輕Scan以及相關投影計算量

5) ??表數據是批量數據入庫，且單次入庫數據不小于DN的個數*6w * 5條記錄；如果單次入庫記錄數不滿足上述約束，建議把表建成分區(qū)表，定期對增強數據所在分區(qū)進行VACUUM FULL操作增強聚簇效果

6) 通過ALTER語法增加PCK時，只有后續(xù)新增數據才會有局部聚簇效果。可以通過VACUUM FULL全表讓已有數據恢復聚簇

3. ?【SMP】

SMP特性通過算子并行(可以理解為把一個線程的任務拆分為多個線程并行執(zhí)行)來提升性能，本質上是一種以資源換取時間的方式，在合適的場景以及資源充足的情況下，能夠起到較好的性能提升效果；但是如果在不合適的場景下，或者資源不足的情況下，反而可能引起性能的劣化。GaussDB A中可以通過參數query_dop設置算子并行度

4. ?【RoughCheck】

粗過濾檢查，列存表掃描的特殊的優(yōu)化手段。在列存表簡單filter條件（單列?op?常量，?op為>、<、=、≤、≥）過濾時，通過判斷CU的內置min/max跟filter條件進行快速比較，如果此CU內是不滿足filter條件，那么直接跳過此CU的掃描。GaussDB A的列存表通過PCK +RoughCheck+Latter Read(先讀取filter條件列進行過濾、再讀取非filter條件列數據)?組合技術，可以典型場景下節(jié)省大量的數據掃描，提升查詢性能

附件： performance.txt 149.17KB 下載次數：5次

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

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

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