PostgreSQL近些年熱度越來越高，特別在國內，基於其生態的資料庫產品種類繁多。如果有人問“信創資料庫學啥比較好”，從今後的工作機會以及學習資料的普及程度來說，我首先推薦的就是PostgreSQL。
不過目前大多數PostgreSQL使用者都沒有認真配置資料庫，讓其達到最佳的使用效果，並充分發揮出硬體的效能特徵。其中資料庫IO的最佳化是重中之重，IO延時較大會導致所有的SQL都會變慢。今天的這篇文章將介紹提高 PostgreSQL IO 效能的八個技巧。
首先，使用相應速度更快，吞吐能力更強的儲存硬體：提高 IO 效能的最重要因素之一是用於儲存資料庫檔案的儲存硬體。在關鍵系統中，一般會考慮使用固態硬碟 (SSD) 或硬體 RAID 陣列以獲得更快的讀寫速度。高效能低延時的集中式SAN儲存是傳統大型資料庫的主要儲存介質，不過現在很多PG資料庫都在單機部署，使用伺服器本地儲存，從而降低使用成本。在本地儲存中充分最佳化儲存效能，提高儲存介質可靠性是十分關鍵的。用本地SATA SSD盤可以有效提高資料庫的整體效能，在HDD上增加高效能緩衝也是價效比很高的做法。為企業應用設計一個效能優秀，價格適中的本地儲存方案，是確保PG IO效能的關鍵。
第二，調整 shared_buffers：shared_buffers 配置引數確定 PostgreSQL 用於在記憶體中快取資料頁的記憶體量。調整此引數以匹配系統上可用的記憶體量以獲得最佳效能。由於PG資料庫使用double buffer機制，因此不同的業務負載，shared_buffers引數的設定會有所不同。PG管網建議配置25%的實體記憶體給shared_buffers使用，這是一種當你不瞭解業務場景與資料分佈時的中庸的配置方案。舉個例子，如果你的實體記憶體是256GB，而你的常用資料是100GB，那麼設定一個128GB的shared_buffers有可能是比較好的配置。設定shared_buffers的首要原則是，不能讓作業系統產生較多的換頁，如果OS經常性出現換頁，那麼你要評估一下是不是由於shared_buffers佔用了過多的實體記憶體，導致OS記憶體使用率過高引起的。
第三，最佳化WAL的配置：WAL是 PostgreSQL 中的一項關鍵功能，可確保事務的永續性和一致性。配置 wal_buffers 引數以匹配您的工作負載並確保最佳 WAL 效能。調整 wal_buffers 的值時，重要的是要考慮生成 WAL 資料的速率，增加 wal_buffers 的值有助於降低磁碟寫入頻率並提高效能，不過在普通的負載下，調整wal_buffers並不能看到資料庫效能的提升，只有當WAL寫入BUFFER的速度大於Walwriter寫盤的速度的時候，加大wal_buffers才會有特別明顯的效能提升。作為一般規則，建議將 wal_buffers 的值設定為磁碟塊大小的小倍數，16 MB。除了調整wal_buffers之外，調整max_wal_size等引數也能有效地減少WAL導致的效能下降，另外CHECKPOINT的最佳化也能大幅減少WAL的寫出量，從而最佳化WAL的效能。
第四，IO分割槽：IO分割槽是一種將資料和索引分佈在多個磁碟上的技術，它可以透過減少磁碟 I/O 爭用來幫助提高 IO 效能。考慮使用表和索引分割槽來利用這種效能提升。將WAL儲存與單獨的高效能儲存也是IO分割槽的一種十分常用的做法，對於高併發環境的資料庫IO效能提升十分有效。利用tablespace將熱表分散到不同的儲存上去也是十分有效的IO分割槽的方法。不過大家要注意的是，要使用IO分割槽，首先要確儲存放PG資料庫的磁碟或者磁碟組本身是分割槽的，具有一定的隔離性，如果你在一個磁碟組上分出多個邏輯卷，然後將PG的儲存做IO分割槽，那麼用處就不大了。
第五，預熱快取：pg_prewarm 擴充套件可用於預熱具有頻繁訪問資料的快取，減少未來查詢所需的磁碟 I/O 量。以前大家都做過很多測試，發現在PG資料庫中某條SQL執行計劃不變的情況下，執行速度差異很大，最終大家都發現瞭如果SQL訪問的大多數資料都在shared buffers中或者在OS的FILE CACHE中，那麼執行效率是較高的。因此在PG資料庫中對熱資料做預熱緩衝是有效提升資料庫效能的方法。Pg_prewarm是一個十分常用的緩衝預熱外掛。
安裝完外掛後，我們可以使用select pg_prewarm(‘tablename’)來預熱某張表的資料。在某些大型統計報表開始之前先預熱資料是提高效能的很好的方法，用於預熱資料的PG外掛也很多，大家可以根據需要選擇使用。
第六，最佳化檢查點：檢查點是將共享緩衝區快取中的髒頁重新整理到磁碟的過程。降低檢查點的頻率和大小有助於減少磁碟 I/O 並提高效能。最佳化檢查點效能的一些技術包括增加 checkpoint_timeout 和 checkpoint_completion_target 配置引數，以及使用更快的儲存硬體來儲存資料和 WAL 檔案。
第七，調整CBO策略引數：調整 effective_cache_size，random_page_cost等多個引數都是CBO最佳化器來評估各種操作的成本的重要引數，在一個有數萬甚至數十萬條SQL語句的資料庫系統而言，CBO能夠產生合理的執行計劃對於資料庫效能至關重要，PG資料庫沒有Oracle那麼強大的SQL最佳化工具與最佳化手段來輔助，因此設定好這些與CBO產生合理執行計劃緊密相關的引數十分重要。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 儲存，可以根據其具體配置和效能進行調整，為了設定合理的值，需要對你的儲存的隨機讀寫效能進行測試。PG中還有幾個類似的引數，可能會影響到CBO生成執行計劃，如果你發現你的PG資料庫中存在較多的錯誤的執行計劃，那麼可以嘗試調整一下這些引數：seq_page_cost、cpu_tuple_costcpu_index_tuple_cost、cpu_operator_cost。
第八，作業系統引數最佳化：主要是在VM的後臺寫、前臺寫、髒塊重新整理策略、記憶體換頁策略等方面進行最佳化，這方面我以前已經寫過多篇文章介紹，在這裡就不重複了，有興趣的朋友可以去翻閱一下我以前的發文。
綜上所述，這八個技巧可以大大提高 PostgreSQL 的 IO 效能。請務必仔細考慮您的硬體設定並配置適當的引數以獲得最佳結果。
來自 “ 白鱔的洞穴 ”， 原文作者：白鱔；原文連結：https://mp.weixin.qq.com/s/Y5PGdNiwdyLKk5zu_-C3pA，如有侵權，請聯絡管理員刪除。
