數倉調優實戰：GUC引數調優

华为云开发者联盟發表於2024-04-07

本文分享自華為雲社群《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;

查詢計劃如下：

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

對於這種場景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是邏輯記憶體管理引數，主要功能是控制單個CN/DN上可用記憶體的最大峰值。

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

shared_buffers

設定DWS使用的共享記憶體大小。增加此引數的值會使DWS比系統預設設定需要更多的System V共享記憶體。

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

cstore_buffers

設定列存和OBS、HDFS外表列存格式（orc、parquet、carbondata）所使用的共享緩衝區的大小。

計算公式可參考shared_buffers。

work_mem

設定內部排序操作和Hash表在開始寫入臨時磁碟檔案之前使用的記憶體大小。

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

maintenance_work_mem用來設定維護性操作（比如VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY等）中可使用的最大的記憶體。

當自動清理程序執行時，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

設定可以同時處於"預備"狀態的事務的最大數目。增加此引數的值會使GaussDB(DWS)比系統預設設定需要更多的System V共享記憶體。

NOTICE:

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

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

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

3.3 併發控制GUC引數

max_active_statements

設定全域性的最大併發數量。此引數只應用到CN，且針對一個CN上的執行作業。

需根據系統資源（如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

備機與從備接收Xlog存放到記憶體緩衝區的大小。

預設值為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

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

相關文章