本文分享自華為雲社區《GaussDB(DWS)性能調優系列實戰篇七：十八般武藝之GUC參數調優》，作者： 黎明的風。

1. 前言

• 適用版本：【8.1.1及以上】

GaussDB(DWS)性能調優系列專題文章，介紹了資料庫性能調優的思路和總體策略。在系統級調優中資料庫全局的GUC參數對整體性能的提升至關重要，而在語句級調優中GUC參數可以調整估算模型，選擇查詢計劃中運算元的類型，或者選擇不同的執行計劃。因此在SQL調優過程中合理的設置GUC參數十分重要。

2. 優化器GUC參數調優

在GaussDB(DWS)中，SQL語句的執行所需要經歷的步驟如下圖所示，其中紅色部分為DBA可以介入實施調優的環節。

查詢計劃的生成是基於一定的模型和統計信息進行代碼估算，在某些場景由於統計信息不准確或者代價估算有偏差時，就需要通過GUC參數設置的的方式選擇更優的查詢計劃。

在GaussDB(DWS)中，和SQL執行性能相關的GUC參數主要有以下幾個：

• best_agg_plan: 進行聚集計算模型的設置
• enable_sort: 控制優化器是否使用的排序，主要用於讓優化器選擇使用HashAgg來實現聚集操作
• enable_hashagg：控制優化器是否使用HashAgg來實現聚集操作
• enable_force_vector_engine：開啟參數後強制生成向量化的執行計劃
• query_dop：用戶自定義的查詢並行度

2.1 best_agg_plan參數

GaussDB(DWS)是分散式的資料庫集群，數據計算儘量在各個DN上並行計算，可以得到最優的性能，在Stream框架下Agg操作可以分為兩個場景。

Agg下層運算元輸出結果集的分佈列是Group By列的子集。

這種場景，直接對下層結果集進行匯聚的結果就是正確的匯聚結果，生成運算元直接使用即可。例如以下語句，lineitem的分佈列是l_orderkey，它是Group By的列。

select
l_orderkey,
count(*) as count_order
from
lineitem
group by
l_orderkey;

查詢計劃如下：

對於這種場景Stream下的聚集（Agg）操作，優化器可以生成以下三種形態的查詢計劃：

• hashagg+gather(redistribute)+hashagg
• redistribute+hashagg(+gather)
• hashagg+redistribute+hashagg(+gather)

通常優化器總會選擇最優的執行計劃，但是眾所周知代價估算，尤其是中間結果集的代價估算有時會有比較大的偏差。這種比較大的偏差就可能會導致聚集（agg）的計算方式出現比較大的偏差，這時候就需要通過best_agg_plan參數進行聚集計算模型的干預。

以下通過TPC-H Q1語句分析三種形態的查詢計劃：

-- TPC-H Q1
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '90' day (3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
}

當best_agg_plan=1時，在DN上進行了一次聚集，然後結果通過GATHER運算元彙總到CN上進行了二次聚集，對應的查詢計劃如下：

該方法適用於DN第一次聚集後結果集較少並且DN數較少的場景，在CN上進行第二次聚集時的結果集小，CN不會成為計算瓶頸。

當best_agg_plan=2時，在DN上先按照Group By的列進行數據重分佈，然後在DN上進行聚集操作，將彙總的結果返回給CN，對應的查詢計劃如下：

該方法適用於DN第一次聚集後結果集縮減不明顯的場景，因為這樣可以省略DN上的第一次聚集操作。

當best_agg_plan=3時，在DN上進行一次聚集，然後將聚集結果按照Group By的列進行數據重分佈，之後在DN上進行二次聚集得到結果，對應的查詢計劃如下：

該方法使用於DN第一次聚集後中間結果縮減明顯，但最終結果行數比較大的場景。

GaussDB(DWS)中，以上三種方法的選擇是根據代價來自動選擇。在實際的SQL調優時，如果遇到有聚集方式不合理的場景，可以通過嘗試設置best_agg_plan參數，選擇最優的聚集方式。

2.2 enable_sort參數

GaussDB(DWS)中實現分組聚集操作有兩種方法：

• HashAgg：使用Hash表對數據進行去重，並同時進行聚集操作，適用於聚集後行數縮減較多的場景。
• Sort + GroupAgg：首先對數據進行排序，然後遍歷排序後的數據，完成去重和聚集操作，適用於聚集後行數縮減較少的場景。

以下麵的SQL為例：

select
l_orderkey,
count(*) as count_order
from
lineitem
group by
l_orderkey;

如果使用Sort + GroupAgg的方式，在Sort排序運算元里執行時間比較長，因為需要對大量數據進行排序操作。

以上這種場景，可以關閉enable_sort參數，選擇使用HashAgg的方式來實現聚集操作，可以獲得較好的執行性能。

2.3 enable_hashagg參數

GaussDB(DWS)中通過count distinct來統計多個列的數據時，通常會使用HashAgg來實現每一個列的統計聚集操作，然後將結果通過Join方式關聯起來得到最終結果。

以下麵的SQL為例：

select
l_orderkey,
count(distinct l_partkey) as count_partkey,
count(distinct l_suppkey) as count_suppkey,
count(distinct l_linenumber) as count_linenumber,
count(distinct l_returnflag) as count_returnflag,
count(distinct l_linestatus) as count_linestatus,
count(distinct l_shipmode) as count_shipmode
from
lineitem
group by
l_orderkey;

從查詢計劃來看，通過count distinct統計了lineitem表中的6列數據，是通過6個HashAgg操作來實現的，該SQL執行時消耗的資源相對較高。

如果關閉enable_hashagg參數，優化器會選擇Sort + GroupAgg的方式，該SQL執行時消耗的資源相對較少。

在應用開發時，可以根據SQL併發和資源使用情況，通過設置enable_hashagg參數來選擇合適的執行計劃。

2.4 enable_force_vector_engine參數

GaussDB(DWS)支持行存儲和列存儲兩種存儲模型，用戶可以根據應用場景，建表的時候選擇行存儲還是列存儲表。向量化執行將傳統的執行模式由一次一元組的模型修改為一次一批元組，配合列存特性，可以帶來巨大的性能提升。

如果使用行存表或者是行列混存的場景，由於行存表預設走的是行存執行引擎，最終查詢無法走向量化執行引擎。

以下麵的SQL為例：

select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer_row,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-15'
and l_shipdate > date '1995-03-15'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
    limit 10;

SQL語句中的customer_row表為行存表，orders和lineitem為列存表，該場景在預設參數的情況下無法走向量化引擎，Row Adapter運算元表示將列存數據轉為行存數據，對應的查詢計劃為：

這種場景，可以選擇開啟enable_force_vector_engine參數，通過向量化執行引擎來執行，Vector Adapter運算元表示將行存數據轉換為列存數據，每個運算元前面的Vector表示改運算元為向量化引擎的執行器運算元，對應的查詢計劃為：

從上述計劃可以看出，向量化引擎相比行執行引擎，執行性能有數倍的提升效果。

2.5 query_dop參數

GaussDB(DWS)支持並行計算技術，當系統的CPU、記憶體、I/O和網路帶寬等資源充足時，可以充分利用富餘硬體資源，提升語句的執行速度。在GaussDB(DWS)中，通過query_dop參數，來控制語句的並行度，取值如下：

• query_dop=1，串列執行
• query_dop=[2…N]，指定並行執行並行度
• query_dop=0，自適應調優，根據系統資源和語句複雜度情況自適應選擇並行度

query_dop參數設置的一些原則：

• 對於短查詢為主的TP類業務中，如果不能通過CN輕量化或下發語句進行業務的調優，則生成SMP計劃的時間較長，建議設置query_dop=1。
• 對於AP類複雜語句的場景，建議設置query_dop=0。
• 計劃並行執行之後必定會引起資源消耗的增加，當資源成為瓶頸的情況下，SMP無法提升性能，反而可能導致性能的劣化。出現資源瓶頸的情況下，建議關閉SMP，即設置query_dop=1。

設置query_dop=0可以實現自適應調優，在部分場景下語句執行的並行度沒有達到最優，這種情況可以考慮通過query_dop參數設置並行度。

例如下麵的SQL：

select count(*) from 
(
    select
    l_orderkey,
    count(*) as count_order
    from
    lineitem
    group by
    l_orderkey
);

在query_dop=0時使用的並行度為2。

設置query_dop=4時使用的並行度為4，執行時間相比並行度為2時有明顯的提升。

3. 資料庫全局GUC參數

在使用GaussDB(DWS)時，全局的GUC參數對集群整體性能影響很大，這裡介紹一些常用參數以及推薦的配置。

3.1 數據記憶體參數

影響資料庫性能的五大記憶體參數有：max_process_memory、shared_buffers、cstore_buffers、work_mem和maintenance_work_mem。

max_process_memory

計算公式：max_process_memory=物理記憶體*0.665/(1+主DN個數)。

shared_buffers

建議設置shared_buffers值為記憶體的40%以內。主要用於行存表scan。計算公式：shared_buffers=(單伺服器記憶體/單伺服器DN個數)0.40.25

cstore_buffers

計算公式可參考shared_buffers。

work_mem

ORDER BY，DISTINCT和merge joins都要用到排序操作。Hash表在散列連接、散列為基礎的聚集、散列為基礎的IN子查詢處理中都要用到。

對於複雜的查詢，可能會同時併發運行好幾個排序或者散列操作，每個都可以使用此參數所聲明的記憶體量，不足時會使用臨時文件。同樣，好幾個正在運行的會話可能會同時進行排序操作。因此使用的總記憶體可能是work_mem的好幾倍。

計算公式：

對於串列無併發的複雜查詢場景，平均每個查詢有5-10關聯操作，建議work_mem=50%記憶體/10。

對於串列無併發的簡單查詢場景，平均每個查詢有2-5個關聯操作，建議work_mem=50%記憶體/5。

對於併發場景，建議work_mem=串列下的work_mem/物理併發數。

maintenance_work_mem

當自動清理進程運行時，autovacuum_max_workers倍數的記憶體將會被分配，所以此時設置maintenance_work_mem的值應該不小於work_mem。

3.2 連接相關GUC參數

連接相關的參數有兩個：max_connections和max_prepared_transactions

max_connections

設置建議：
CN中此參數建議保持預設值。DN中此參數建議設置為CN的個數乘以CN中此參數的值。

增大這個參數可能導致GaussDB(DWS)要求更多的System V共用記憶體或者信號量，可能超過操作系統預設配置的最大值。這種情況下，請酌情對數值加以調整。

max_prepared_transactions

NOTICE:

max_connections取值的設置受max_prepared_transactions的影響，在設

max_connections之前，應確保max_prepared_transactions的值大於或等

max_connections的值，這樣可確保每個會話都有一個等待中的預備事務。

3.3 併發控制GUC參數

max_active_statements

需根據系統資源（如CPU資源、IO資源和記憶體資源）情況，調整此數值大小，使得系統支持最大限度的併發作業，且防止併發執行作業過多，引起系統崩潰。

當取值-1或者0時，不限制全局併發數。

在點查詢的場景下，參數建議設置為100。

在分析類查詢的場景下，參數的值設置為CPU的核數除以DN個數，一般可以設置5~8個。

3.4 其他GUC參數

bulk_write_ring_size

該參數主要影響入庫性能，建議導入壓力大的場景增加DN上的該參數配置。

checkpoint_completion_target

含義是每個checkpoint需要在checkpoints間隔時間的50%內完成。

預設值為0.5，為提高性能可改成0.9。

data_replicate_buffer_size

預設值為128MB，若伺服器記憶體為256G，可適當增大到512MB。

wal_receiver_buffer_size

預設值為64MB，若伺服器記憶體為256G，可適當增大到128MB

4. 總結

本篇文章主要介紹了GaussDB(DWS)性能調優涉及到的優化器和系統級GUC參數，通過合理配置這些GUC參數，能夠充分利用好CPU、記憶體、磁碟IO和網路IO等資源，提升語句的執行性能和GaussDB(DWS)集群的整體性能。

5. 參考文檔

1. GaussDB(DWS) SQL進階之SQL操作之聚集函數 https://bbs.huaweicloud.com/blogs/293963
2. PB級數倉GaussDB(DWS)性能黑科技之並行計算技術解密 https://bbs.huaweicloud.com/blogs/203426
3. 常見性能參數調優設計 https://support.huaweicloud.com/performance-dws/dws_10_0068.html

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