[項目實踐-實踐系列] GaussDB(DWS) 實踐系列 - 一些常見參數(shù)調(diào)優(yōu)分享

在我們使用數(shù)據(jù)庫的時候，為了提高集群的性能，讓語句插上翅膀，有多種方式渠道去調(diào)優(yōu)，從硬件配置到軟件驅(qū)動升級，再到數(shù)據(jù)庫的內(nèi)部參數(shù)調(diào)整。這篇文章旨在分享一些參數(shù)的介紹以及推薦設置。

1.query_dop

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

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

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

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

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

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

2.enable_dynamic_workload

動態(tài)負載管理指數(shù)據(jù)庫內(nèi)部根據(jù)用戶負載情況，自動對復雜查詢進行隊列控制，不再需要手動設置參數(shù)，做到系統(tǒng)參數(shù)免調(diào)優(yōu)。

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

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

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

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

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

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

完全下推語句的查詢。

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

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

包含UDF的場景，且UDF中使用大量內(nèi)存的場景。

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

3.max_active_statements

設置全局的最大并發(fā)數(shù)量。此參數(shù)只應用到CN，且針對一個CN上的執(zhí)行作業(yè)。

需根據(jù)系統(tǒng)資源（如CPU資源、IO資源和內(nèi)存資源）情況，調(diào)整此數(shù)值大小，使得系統(tǒng)支持最大限度的并發(fā)作業(yè)，且防止并發(fā)執(zhí)行作業(yè)過多，引起系統(tǒng)崩潰。

當取值-1或者0時，不限制全局并發(fā)數(shù)。

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

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

4.session_timeout

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

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

5.影響數(shù)據(jù)庫內(nèi)存的參數(shù)主要是基友5兄弟: max_process_memory、shared_buffers、cstore_buffers、work_mem和maintenance_work_mem，這也是優(yōu)化向上的巨頭兄弟。

max_process_memory

max_process_memory是邏輯內(nèi)存管理參數(shù)，主要功能是控制單個CN/DN上可用內(nèi)存的最大峰值。

計算公式：max_process_memory=物理內(nèi)存*0.665/(1+主DN個數(shù))。

shared_buffers

設置GaussDB 200使用的共享內(nèi)存大小。增加此參數(shù)的值會使GaussDB 200比系統(tǒng)默認設置需要更多的System ?V共享內(nèi)存。

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

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

checkpoint_segments

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

cstore_buffers

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

計算公式可參考shared_buffers。

work_mem

設置內(nèi)部排序操作和Hash表在開始寫入臨時磁盤文件之前使用的內(nèi)存大小。ORDER ?BY，DISTINCT和merge joins都要用到排序操作。Hash表在散列連接、散列為基礎的聚集、散列為基礎的IN子查詢處理中都要用到。

對于復雜的查詢，可能會同時并發(fā)運行好幾個排序或者散列操作，每個都可以使用此參數(shù)所聲明的內(nèi)存量，不足時會使用臨時文件。同樣，好幾個正在運行的會話可能會同時進行排序操作。因此使用的總內(nèi)存可能是work_mem的好幾倍。

計算公式：

對于串行無并發(fā)的復雜查詢場景，平均每個查詢有5-10關聯(lián)操作，建議work_mem=50%內(nèi)存/10。

對于串行無并發(fā)的簡單查詢場景，平均每個查詢有2-5個關聯(lián)操作，建議work_mem=50%內(nèi)存/5。

對于并發(fā)場景，建議work_mem=串行下的work_mem/物理并發(fā)數(shù)。

maintenance_work_mem

maintenance_work_mem用來設置維護性操作（比如VACUUM、CREATE ?INDEX、ALTER TABLE ADD FOREIGN KEY等）中可使用的最大的內(nèi)存。

設置建議：

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

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

例如當服務器內(nèi)存為256G，6DN，跑10,20等并發(fā)的時候，根據(jù)計算公式可以設置參數(shù)如下

max_process_memory: 25G

shared_buffers: 4G (行存表）

cstore_buffers: 4G(列存表)

work_mem: 1G

maintenance_work_mem: 1G

6.bulk_write_ring_size

數(shù)據(jù)并行導入使用的環(huán)形緩沖區(qū)大小。

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

7.comm_quota_size

該參數(shù)控制每個流通道每次數(shù)據(jù)傳輸?shù)拇笮?，默認數(shù)值1M。大并發(fā)場景下，提升該數(shù)值時，可提升通信性能，但消耗更多的內(nèi)存，需要根據(jù)實際場景進行調(diào)優(yōu)。若通過查詢DN的視圖pg_total_memory_detail，發(fā)現(xiàn)通信層使用內(nèi)存已達參數(shù)comm_usable_memory的閾值時，需要減少該數(shù)值，如修改為512K。

8.連接2兄弟: max_connections,max_prepared_transactions

max_connections

允許和數(shù)據(jù)庫連接的最大并發(fā)連接數(shù)。此參數(shù)會影響集群的并發(fā)能力。

設置建議：

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

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

max_prepared_transactions

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

注意：

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

9.checkpoint_completion_target

指定檢查點完成的目標。

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

為提高性能可改成0.9。

10.data_replicate_buffer_size

發(fā)送端與接收端傳遞數(shù)據(jù)頁時，隊列占用內(nèi)存的大小。此參數(shù)會影響主備之間復制的緩沖大小。

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

11.wal_receiver_buffer_size

備機與從備接收Xlog存放到內(nèi)存緩沖區(qū)的大小。

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

以上參數(shù)是我們調(diào)優(yōu)過程中會經(jīng)常碰到的一些參數(shù)，希望眾同學可以有所收獲。

數(shù)據(jù)倉庫服務 GaussDB(DWS) 數(shù)據(jù)庫

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