PG資料庫最佳化上我們都能做點什麼

資料庫最佳化是一個綜合工程，不僅僅是需要 D BA 參與，更重要的是研發設計人員針對 P G 資料庫的特點來進行相關的最佳化設計。不過對於 D BA 來說，一旦接到上線和運維任務，基本上都是木已成舟，軟體設計方面留下的坑已經挖好， D BA 的作為已經十分有限了。不過既然要幹運維，那麼少不了就要參與最佳化。 P G 的最佳化工作該如何開展呢？今天我從幾個主要的方面聊聊 P G 最佳化的幾個常見的角度。針對 P G 資料庫，只要做好了下面幾個方面的最佳化工作，那麼運維起來也就比較省心了。

 

l  硬體資源 問題 ：如果資料庫伺服器硬體資源不足，例如 CPU 、記憶體、磁碟 IO 等，會導致系統效能下降，響應時間變慢。

l  作業系統配置不合理：如果作業系統沒有針對 PG 資料庫進行最佳化，那麼 P G 資料庫也無法發揮最 佳的效能，因此針對 P G 資料庫的最佳化，從作業系統引數調整入手永遠是不會錯的。

l  檔案系統配置不合理：對於一些負載較高的大型資料庫來說，如果無法發揮後端儲存的 I O 能力，或者說讓後端磁碟出現了效能問題，那麼就會嚴重影響 P G 資料庫的效能甚至穩定性。對於大型資料庫來說，檔案系統設計與配置一定要十分用心。

l  S QL 不夠最佳化 ：如果 應用 沒有經過最佳化，可能會導致查詢效率低下，索引 設計不合理，缺少必要的索引，過多的單列索引以及索引型別使用不合理等都會帶來效能問題。最後 不合理 多表 的 JOIN 、 WHERE 子句 和大表並行掃碼都可能成為效能殺手 。

l  資料庫結構設計不合理：如果資料庫結構設計不合理，可能會導致查詢效率低下，例如表過度歸一化、 大表未分割槽或者分割槽設定不合理，表或者索引的的 F ILL FACTOR 引數設定不合理導致的熱塊衝突 。 索引設計不合理產生的不必要的寫成本過高。應該儲存到物件儲存中的非結構化資料儲存到 P G 資料庫中等。表分割槽設計不合理，時序資料沒有使用 t imescaledb 的自動分割槽與自動壓縮特性也會導致時序資料訪問的效能不佳。

l  資料庫引數設定不合理：如果 PostgreSQL 資料庫引數設定不合理，可能會導致資料庫效能低下，例如 shared_buffers 、 work_mem 、 W AL /Checkpoint   等引數的設定 等 。

l  併發控制不合理：如果資料庫併發控制不合理，可能會導致效能下降 ，這方面包含 事務隔離級別設定不合理 ，併發度相關引數設定不合理等 。

l  快取命中率低：如果快取命中率低，會導致頻繁的磁碟 IO 操作，從而降低資料庫效能。

l  訪問冷資料的效能不足： P G 資料庫是採用 D OUBLE CACHE 機制的，冷資料是指在 S HARED BUFFERS 和 O S CACHE 中都不存在的資料，這些資料一旦要訪問，要產生大量的物理 I O ，訪問效能較差。

l  自動化任務衝突：如果資料庫中存在大量的自動化任務，例如備份、VACUUM 、定時任務等，可能會導致任務之間的衝突，從而影響系統效能。

 

硬體資源不足的問題我們就不多加討論了，這種情況一般會出現在 C PU 、 I O 等方面，在分析這方面問題的時候，需要關注 R 佇列的長度是否超過 C PU 邏輯核數的 2 倍以上，對於 I O 來說，不僅僅要看 I OPS/IO 吞吐量等指標，更重要的是要看 I O 延時是否合理。

作業系統配置不合理是絕大多數 P G 資料庫都存在的問題，這方面實際上是有一些最 佳實踐的。

[sysctl]

vm.swappiness = 1

vm.dirty_background_ratio = 10

vm.dirty_ratio = 40

vm.dirty_expire_centisecs = 3000

vm.dirty_writeback_centisecs = 500

kernel.shmmax = 18446744073692700000

kernel.shmall = 18446744073692700000

kernel.shmmni = 4096

kernel.sem = 250 512000 100 2048

fs.file-max = 312139770

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 2048 65499

# Permits sockets in the time-wait state to be reused for new connections:

net.ipv4.tcp_tw_reuse = 1

net.core.netdev_budget = 1024

net.core.netdev_max_backlog = 2048

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

kernel.panic_on_oops = 1

# We don't need NUMA balancing in this box:

kernel.numa_balancing = 0

# Used if not defined by the service:

net.core.somaxconn = 4096

# Other parameters to override throughput-performance template

net.ipv4.tcp_rmem = 4096 87380 16777216

net.ipv4.tcp_wmem = 4096 65536 16777216

net.ipv4.tcp_window_scaling = 1

net.netfilter.nf_conntrack_max = 250000

net.ipv4.tcp_max_syn_backlog=4096

[vm]

transparent_hugepages=never

 

上面是一個紅帽公司對於 PG 資料庫 RHEL 引數最佳化的建議，大家可以參考，對於絕大多數高負載的系統來說，都是有效的。大家要注意的是，關於髒塊回寫的設定，對於不同的寫 I O 負載以及不同的底層 I O 硬體，可能調整會有不同，甚至會有截然相反的配置策略。要注意的是，絕對不能因為不合理的髒塊重新整理策略導致了 O S IO 負載的過載。在此前提下，縮短 I O 寫盤的週期對於提高併發負載是有幫助的。

檔案系統的設計對於大型系統來說十分關鍵，很多 DBA 都只會設定一個 $ PGDATA ，整個資料庫都放在同一個檔案系統上，這需要對檔案系統底層的卷做十分細緻的最佳化，確保整個卷的 I O 能力是優秀的，這一點總是無法做到的。因此在資料庫設計的時候就透過 W AL 與資料檔案分離，熱資料與冷資料分離，透過表空間隔離熱點 I O 等方式規劃 P G 資料庫的檔案儲存。如果應用系統已經無法透過表空間來隔離 I O 熱點，那麼透過軟連線將部分庫的目錄遷移到其他檔案系統也是一個可行的方案。

對於資料庫引數來說，實際上不同的應用場景下的最 佳調整方案是不同的，一般來說，設定合理的 s hared_buffers ，以及最佳化好相關的而 b gwriter ， W AL ， checkpoint，work_mem，V ACUUM 等相關的引數，就能夠滿足大多數應用的需求了。在這裡我們就不做過多的討論了。在這方面我以前寫過十多篇文章，有興趣的朋友可以到公眾號透過搜尋 “效能最佳化”或者透過公眾號的選單去查詢。

併發控制不合理方面的問題是比較容易被忽視的問題，事務隔離級別用錯對於效能的影響極大，不過一般情況下我們都是使用 r ead committed ，不要輕易去修改資料庫級的事務隔離級別。

併發的另外一個方面是系統中的各類併發訪問的控制，特別是並行執行的設定。 m ax_worker_processes 、 max_parallel_workers、max_parallel_maintenance_workers和max_parallel_workers_per_gather 等引數對資料庫的併發度控制都至關重要 。

如果併發相關的設定過小，那麼當活躍會話數量不高的時候，無法充分發揮伺服器硬體的資源優勢，造成巨大的浪費。 P G 資料庫可以支撐巨大的資料庫與極高的併發，因此如果伺服器的配置足夠好，系統資源使用率不高，但是應用效能無法達到設計要求，那麼我們就應該關注一下是否併發控制相關的引數設定過低了。預設的 P G 引數裡 , max_worker_processes 是偏小的，僅僅是 8,對於有上百甚至上千個邏輯核數的伺服器來說是完全不夠用的。

當然如果因為併發控制引數設定的過高而導致了 C PU 等資源出現了不足，因為 I OPS 過大或者 I O 吞吐量過大，底層儲存能力不足導致的 I O 延時過大等現象，那麼適當調低這些引數對資料庫的整體效能提升是有幫助的。

P G 的 S HARED_BUFFERS 設定不合理可能會導致緩衝區命中率不高，從而影響 S QL 的執行效能。不過 P G 資料庫是使用 D OUBLE BUFFER 機制的，要想為你的應用調整好緩衝區並不容易。再怎麼調整都無法滿足不同場景的應用，有些時候 D BA 真的很難透過調整來最佳化這方面的效能。對於一些定期的報表等應用，在跑批之前做資料預熱可能是 D BA 能夠控制的最佳化方法，也是最為有效的提升統計報表效能的方法。

最後一點，自動化任務衝突是所有資料庫都會遇到的效能問題，如果資料庫備份，大批次統計作業與大資料量匯入匯出同時發生，再好的硬體也可能撐不住，因此在設計這些定期任務的時候，一定要透過演演算法將這些作業分開，千萬不要讓這些大型操作存在最大公約數。否則哪怕現在你的系統沒問題，幾年後，還是會出問題的。