實踐系列-GaussDB(DWS)常用參數優化

1.【query_dop】

SMP架構是一種利用富余資源來換取時間的方案，計劃并行之后必定會引起資源消耗的增加，包括CPU、內存、I/O和網絡帶寬等資源的消耗都會出現明顯的增長，而且隨著并行度的增大，資源消耗也隨之增大。

想要利用SMP提升查詢性能需要滿足以下條件：

系統的CPU、內存、I/O和網絡帶寬等資源充足。SMP架構是一種利用富余資源來換取時間的方案，計劃并行之后必定會引起資源消耗的增加，當上述資源成為瓶頸的情況下，SMP無法提升性能，反而可能導致性能的劣化。在出現資源瓶頸的情況下，建議關閉SMP。

資源許可的情況下，并行度越高，性能提升效果越好。

SMP并行度支持會話級設置，推薦客戶在執行符合要求的查詢前，打開smp，執行結束后，關閉smp。以免在業務峰值時，對業務造成沖擊。

query_dop的默認值是1， 可通過set query_dop=10；語句在會話中打開smp。

2.【enable_dynamic_workload】

動態負載管理指數據庫內部根據用戶負載情況，自動對復雜查詢進行隊列控制，不再需要手動設置參數，做到系統參數免調優。

該參數默認打開，有以下幾個需要注意的點。

?集群有一個CN會作為中心協調節點（CCN），用于收集和調度作業執行，該節點可以通過gs_om -t status --detail查詢到，Central Coordinator State會顯示其狀態。當CCN不存在時，作業不再受動態負載管理控制。

?簡單查詢作業（估算值<32MB）、非DML（即非INSERT、UPDATE、DELETE和SELECT）語句，不走自適應負載，需要通過max_active_statements來進行單CN的上限控制。

?默認work_mem數值為64MB，在自適應負載特性下，該數值不能變大，否則會引起內存不受控（例如未做Analyze的語句）。

?以下場景或語句由于內存使用特殊性和不確定性，可能導致大并發場景內存不受控，如果遇到需要降低并發數。

?單條元組占用內存過大的場景，例如，基表包含超過MB級別的寬列。

?完全下推語句的查詢。

?需要在CN上耗費大量內存的語句，例如，不能下推的語句，withhold cursor場景。

?由于計劃生成不當導致hashjoin算子建立的hash表重復值過多，占用大量內存。

?包含UDF的場景，且UDF中使用大量內存的場景。

該參數可配合query_dop=0使用，當query_dop設成0（自適應），系統會根據資源情況和計劃特征，動態為每個查詢選取[1,8]之間的最優的并行度。enable_dynamic_workload參數會動態分配內存。

3.【max_active_statements】

設置全局的最大并發數量。此參數只應用到CN，且針對一個CN上的執行作業。

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

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

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

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

4.【session_timeout】

缺省情況下，客戶端連接數據庫后處于空閑狀態時會根據參數的默認值自動斷開連接。

建議設成0，以防止超時斷連。增加使用體驗 :)

5.影響數據庫內存的參數主要是基友5兄弟: 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個數)。

?s【hared_buffers】

設置GaussDB 200使用的共享內存大小。增加此參數的值會使GaussDB 200比系統默認設置需要更多的System? V共享內存。

建議設置shared_buffers值為內存的40%以內。主要用于行存表scan。

計算公式：shared_buffers=(單服務器內存/單服務器DN個數)*0.4*0.25

checkpoint_segments

提升此參數可加快大數據的導入速度，但需要結合shared_buffers參數統一考慮，默認值為64。例如shared_buffers被設為4G，可將DN 的checkpoint_segments設為256。

?【cstore_buffers】

設置列存和HDFS所使用的共享緩沖區的大小。

計算公式可參考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等）中可使用的最大的內存。

設置建議：

建議設置此參數的值大于work_mem，可以改進清理和恢復數據庫轉儲的速度。因為在一個數據庫會話里，任意時刻只有一個維護性操作可以執行，并且在執行維護性操作時不會有太多的會話。

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

例如當服務器內存為256G，6DN，跑10,20等并發的時候，根據計算公式可以設置參數如下

max_process_memory: 25G

shared_buffers: 4G (行存表）

cstore_buffers: 4G(列存表)

work_mem: 1G

maintenance_work_mem: 1G

6.【bulk_write_ring_size】

數據并行導入使用的環形緩沖區大小。

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

7.【comm_quota_size】

該參數控制每個流通道每次數據傳輸的大小，默認數值1M。大并發場景下，提升該數值時，可提升通信性能，但消耗更多的內存，需要根據實際場景進行調優。若通過查詢DN的視圖pg_total_memory_detail，發現通信層使用內存已達參數comm_usable_memory的閾值時，需要減少該數值，如修改為512K。

8.連接2兄弟: max_connections,max_prepared_transactions

?【max_connections】

允許和數據庫連接的最大并發連接數。此參數會影響集群的并發能力。

設置建議：

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

增大這個參數可能導致GaussDB 200要求更多的SystemV共享內存或者信號量，可能超過操作系統缺省配置的最大值。這種情況下，請酌情對數值加以調整。

?【max_prepared_transactions】

設置可以同時處于"預備"狀態的事務的最大數目。增加此參數的值會使GaussDB 200比系統默認設置需要更多的System V共享內存。

注意：

max_connections取值的設置受max_prepared_transactions的影響，在設置max_connections之前，應確保max_prepared_transactions的值大于或等于max_connections的值，這樣可確保每個會話都有一個等待中的預備事務。

9.【checkpoint_completion_target】

指定檢查點完成的目標。

默認值為0.5，含義是每個checkpoint需要在checkpoints間隔時間的50%內完成。

為提高性能可改成0.9。

10.【data_replicate_buffer_size】

發送端與接收端傳遞數據頁時，隊列占用內存的大小。此參數會影響主備之間復制的緩沖大小。

默認值為128MB，若服務器內存為256G，可適當增大到512MB。

11.【wal_receiver_buffer_size】

備機與從備接收Xlog存放到內存緩沖區的大小。

默認值為64MB，若服務器內存為256G，可適當增大到128MB。

以上參數是我們調優過程中會經常碰到的一些參數，希望眾同學可以有所收獲。

EI企業智能 Gauss AP 數據倉庫服務 GaussDB(DWS)

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