PG資料庫IO最佳化技巧

DBAIOps社群發表於2024-02-20

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 效能。 請務必仔細考慮您的硬體設定並配置適當的引數以獲得最 佳結果。

 




來自 “ ITPUB部落格 ” ,連結:https://blog.itpub.net/70036742/viewspace-3006868/,如需轉載,請註明出處,否則將追究法律責任。

相關文章