PG資料庫IO最佳化技巧

PostgreSQL 近些年熱度越來越高，特別在國內，基於其生態的資料庫產品種類繁多。如果有人問“信創資料庫學啥比較好”，從今後的工作機會以及學習資料的普及程度來說，我首先推薦的就是PostgreSQL 。

不過目前大多數PostgreSQL使用者都沒有認真配置資料庫，讓其達到最 佳的使用效果，並充分發揮出硬體的效能特徵。其中資料庫I O 的最佳化是重中之重， IO 延時較大會導致所有的S QL 都會變慢 。  今天的這篇文章 將介紹提高 PostgreSQL IO 效能的 八 個技巧。

首先，使用相應速度更快，吞吐能力更強的儲存硬體：提高  IO 效能的最重要因素之一是用於儲存資料庫檔案的儲存硬體。  在關鍵系統中，一般會 考慮使用固態硬碟 (SSD) 或硬體 RAID 陣列以獲得更快的讀寫速度。 高效能低延時的集中式S AN 儲存是傳統大型資料庫的主要儲存介質，不過現在很多P G 資料庫都在單機部署，使用伺服器本地儲存，從而降低使用成本。在本地儲存中充分最佳化儲存效能，提高儲存介質可靠性是十分關鍵的。是用本地 SATA SSD 盤可以有效提高資料庫的整體效能，在H DD 上增加高效能緩衝也是價效比很高的做法。為企業應用設計一個效能優秀，價格適中的本地儲存方案，是確保P G IO 效能的關鍵。

第二，調整  shared_buffers：shared_buffers 配置引數確定 PostgreSQL 用於在記憶體中快取資料頁的記憶體量。 調整此引數以匹配系統上可用的記憶體量以獲得最 佳效能。 由於P G 資料庫使用double   buffer機制，因此不同的業務負載，s hared_buffers 引數的設定會有所不同。P G 管網建議配置2 5 %的實體記憶體給s hared_buffers 使用，這是一種當你不瞭解業務場景與資料分佈時的中庸的配置方案。舉個例子，如果你的實體記憶體是2 56GB ，而你的常用設資料是1 00GB ，那麼設定一個1 28GB 的s hared_buffers 有可能是比較好的配置。設定s hared_buffers 的首要原則是，不能讓作業系統產生較多的換頁，如果O S 經常性出現換頁，那麼你要評估一下是不是由於s hared_buffers 佔用了過多的實體記憶體，導致O S 記憶體使用率過高引起的。

第三，最佳化W AL 的配置： WAL是 PostgreSQL 中的一項關鍵功能，可確保事務的永續性和一致性。 配置 wal_buffers 引數以匹配您的工作負載並確保最 佳 WAL 效能。 調整  wal_buffers 的值時，重要的是要考慮生成 WAL 資料的速率 ， 增加 wal_buffers 的值有助於降低磁碟寫入頻率並提高效能， 不過在普通的負載下，調整w al_buffers 並不能看到資料庫效能的提升，只有當W AL 寫入B UFFER 的速度大於Walwriter寫盤的速度的時候，加大w al_buffers 才會有特別明顯的效能提升。作為一般規則，建議將  wal_buffers 的值設定為磁碟塊大小的小倍數 ， 16 MB。 除了調整w al_buffers 之外，調整m ax_wal_size 等引數也能有效的減少 WAL 導致的效能下降，另外C HECKPOINT 的最佳化也能大幅減少W AL 的寫出量，從而最佳化W AL 的效能 。

第四，I O 分割槽：I O 分割槽是一種將資料和索引分佈在多個磁碟上的技術，它可以透過減少磁碟  I/O 爭用來幫助提高 IO 效能。 考慮使用表和索引分割槽來利用這種效能提升。 將W AL 儲存與單獨的高效能儲存也是I O 分割槽的一種十分常用的做法，對於高併發環境的資料庫I O 效能提升十分有效。利用t ablespace 將熱表分散到不同的儲存上去也是十分有效的I O 分割槽的方法。不過大家要注意的是，要使用I O 分割槽，首先要確儲存放P G 資料庫的磁碟或者磁碟組本身是分割槽的，具有一定的隔離性，如果你在一個磁碟組上分出多個邏輯卷，然後將P G 的儲存做I O 分割槽，那麼用處就不大了。

第五，預熱快取： pg_prewarm 擴充套件可用於預熱具有頻繁訪問資料的快取，減少未來查詢所需的磁碟 I/O 量。 以前大家都做過很多測試，發現在P G 資料庫中某條S QL 執行計劃不變得情況下，執行速度差異很大，最終大家都發現瞭如果S QL 訪問的大多數資料都在s hared buffers 中或者在O S 的F ILE CACHE 中，那麼執行效率是較高的。因此在P G 資料庫中對熱資料做預熱緩衝是有效提升資料庫效能的方法。 Pg_prewarm 是一個十分常用的緩衝預熱外掛。

 

安裝完外掛後，我們可以使用 select pg_prewarm(‘tablename’) 來預熱某張表的資料。

第六，最佳化檢查點：檢查點是將共享緩衝區快取中的髒頁重新整理到磁碟的過程。  降低檢查點的頻率和大小有助於減少磁碟 I/O 並提高效能。 最佳化檢查點效能的一些技術包括增加 checkpoint_timeout 和 checkpoint_completion_target 配置引數，以及使用更快的儲存硬體來儲存資料和 WAL 檔案。

第七，調整C BO 策略引數：調整  effective_cache_size ， random_page_cost 等多個引數都是C BO 最佳化器來評估各種操作的成本的重要引數，在一個有數萬甚至數十萬條S QL 語句的資料庫系統而言，C BO 能夠產生合理的執行計劃對於資料庫效能至關重要，P G 資料庫沒有Oracle那麼強大的S QL 最佳化工具與最佳化手段來輔助，因此設定好這些與C BO 產生合理執行計劃緊密相關的引數十分重要。 effective_cache_size 配置引數用於估計 PostgreSQL 可用的磁碟快取量 ，從而確定掃描資料的成本 。random_page_cost 配置引數確定隨機磁碟 I/O 相對於順序磁碟 I/O 的成本。 設定此引數以準確反映系統上隨機磁碟 I/O 的成本。據磁碟型別的不同，對 random_page_cost 的設定也會有所不同：對於 HDD，可以設定為 4.0 到 4.5；對於 SSD，可以設定為 1.0 到 1.5。如果使用中央化的 SAN 儲存，可以根據其具體配置和效能進行調整 ，為了設定合理的值，需要對你的儲存的隨機讀寫效能進行測試 。 P G 中還有幾個類似的引數，可能會影響到C BO 生成執行計劃，如果你發現你的P G 資料庫中存在較多的錯誤的執行計劃，那麼可以嘗試調整一下這些引數： seq_page_cost 、 cpu_tuple_costcpu_index_tuple_cost 、 cpu_operator_cost 。

第八，作業系統引數最佳化：主要是在V M 的後臺寫、前臺寫、髒塊重新整理策略、記憶體換頁策略等方面進行最佳化，這方面我以前已經寫過多篇文章介紹，在這裡就不重複了，有興趣的朋友可以去翻閱一下我以前的發文。

綜上所述，這八個技巧可以大大提高  PostgreSQL 的 IO 效能。 請務必仔細考慮您的硬體設定並配置適當的引數以獲得最 佳結果。