?PostgreSQL近些年熱度越來越高，特別在國內(nèi)，基于其生態(tài)的數(shù)據(jù)庫產(chǎn)品種類繁多。如果有人問“信創(chuàng)數(shù)據(jù)庫學(xué)啥比較好”，從今后的工作機(jī)會(huì)以及學(xué)習(xí)資料的普及程度來說，我首先推薦的就是PostgreSQL。

不過目前大多數(shù)PostgreSQL用戶都沒有認(rèn)真配置數(shù)據(jù)庫，讓其達(dá)到最佳的使用效果，并充分發(fā)揮出硬件的性能特征。其中數(shù)據(jù)庫IO的優(yōu)化是重中之重，IO延時(shí)較大會(huì)導(dǎo)致所有的SQL都會(huì)變慢。今天的這篇文章將介紹提高 PostgreSQL IO 性能的八個(gè)技巧。

首先，使用相應(yīng)速度更快，吞吐能力更強(qiáng)的存儲(chǔ)硬件：提高 IO 性能的最重要因素之一是用于存儲(chǔ)數(shù)據(jù)庫文件的存儲(chǔ)硬件。在關(guān)鍵系統(tǒng)中，一般會(huì)考慮使用固態(tài)硬盤 (SSD) 或硬件 RAID 陣列以獲得更快的讀寫速度。高性能低延時(shí)的集中式SAN存儲(chǔ)是傳統(tǒng)大型數(shù)據(jù)庫的主要存儲(chǔ)介質(zhì)，不過現(xiàn)在很多PG數(shù)據(jù)庫都在單機(jī)部署，使用服務(wù)器本地存儲(chǔ)，從而降低使用成本。在本地存儲(chǔ)中充分優(yōu)化存儲(chǔ)性能，提高存儲(chǔ)介質(zhì)可靠性是十分關(guān)鍵的。用本地SATA SSD盤可以有效提高數(shù)據(jù)庫的整體性能，在HDD上增加高性能緩沖也是性價(jià)比很高的做法。為企業(yè)應(yīng)用設(shè)計(jì)一個(gè)性能優(yōu)秀，價(jià)格適中的本地存儲(chǔ)方案，是確保PG IO性能的關(guān)鍵。

第二，調(diào)整 shared_buffers：shared_buffers 配置參數(shù)確定 PostgreSQL 用于在內(nèi)存中緩存數(shù)據(jù)頁的內(nèi)存量。調(diào)整此參數(shù)以匹配系統(tǒng)上可用的內(nèi)存量以獲得最佳性能。由于PG數(shù)據(jù)庫使用double buffer機(jī)制，因此不同的業(yè)務(wù)負(fù)載，shared_buffers參數(shù)的設(shè)置會(huì)有所不同。PG管網(wǎng)建議配置25%的物理內(nèi)存給shared_buffers使用，這是一種當(dāng)你不了解業(yè)務(wù)場景與數(shù)據(jù)分布時(shí)的中庸的配置方案。舉個(gè)例子，如果你的物理內(nèi)存是256GB，而你的常用數(shù)據(jù)是100GB，那么設(shè)置一個(gè)128GB的shared_buffers有可能是比較好的配置。設(shè)置shared_buffers的首要原則是，不能讓操作系統(tǒng)產(chǎn)生較多的換頁，如果OS經(jīng)常性出現(xiàn)換頁，那么你要評(píng)估一下是不是由于shared_buffers占用了過多的物理內(nèi)存，導(dǎo)致OS內(nèi)存使用率過高引起的。

第三，優(yōu)化WAL的配置：WAL是 PostgreSQL 中的一項(xiàng)關(guān)鍵功能，可確保事務(wù)的持久性和一致性。配置 wal_buffers 參數(shù)以匹配您的工作負(fù)載并確保最佳 WAL 性能。調(diào)整 wal_buffers 的值時(shí)，重要的是要考慮生成 WAL 數(shù)據(jù)的速率，增加 wal_buffers 的值有助于降低磁盤寫入頻率并提高性能，不過在普通的負(fù)載下，調(diào)整wal_buffers并不能看到數(shù)據(jù)庫性能的提升，只有當(dāng)WAL寫入BUFFER的速度大于Walwriter寫盤的速度的時(shí)候，加大wal_buffers才會(huì)有特別明顯的性能提升。作為一般規(guī)則，建議將 wal_buffers 的值設(shè)置為磁盤塊大小的小倍數(shù)，16 MB。除了調(diào)整wal_buffers之外，調(diào)整max_wal_size等參數(shù)也能有效地減少WAL導(dǎo)致的性能下降，另外CHECKPOINT的優(yōu)化也能大幅減少WAL的寫出量，從而優(yōu)化WAL的性能。

第四，IO分區(qū)：IO分區(qū)是一種將數(shù)據(jù)和索引分布在多個(gè)磁盤上的技術(shù)，它可以通過減少磁盤 I/O 爭用來幫助提高 IO 性能。考慮使用表和索引分區(qū)來利用這種性能提升。將WAL存儲(chǔ)與單獨(dú)的高性能存儲(chǔ)也是IO分區(qū)的一種十分常用的做法，對(duì)于高并發(fā)環(huán)境的數(shù)據(jù)庫IO性能提升十分有效。利用tablespace將熱表分散到不同的存儲(chǔ)上去也是十分有效的IO分區(qū)的方法。不過大家要注意的是，要使用IO分區(qū)，首先要確保存放PG數(shù)據(jù)庫的磁盤或者磁盤組本身是分區(qū)的，具有一定的隔離性，如果你在一個(gè)磁盤組上分出多個(gè)邏輯卷，然后將PG的存儲(chǔ)做IO分區(qū)，那么用處就不大了。

第五，預(yù)熱緩存：pg_prewarm 擴(kuò)展可用于預(yù)熱具有頻繁訪問數(shù)據(jù)的緩存，減少未來查詢所需的磁盤 I/O 量。以前大家都做過很多測試，發(fā)現(xiàn)在PG數(shù)據(jù)庫中某條SQL執(zhí)行計(jì)劃不變的情況下，執(zhí)行速度差異很大，最終大家都發(fā)現(xiàn)了如果SQL訪問的大多數(shù)數(shù)據(jù)都在shared buffers中或者在OS的FILE CACHE中，那么執(zhí)行效率是較高的。因此在PG數(shù)據(jù)庫中對(duì)熱數(shù)據(jù)做預(yù)熱緩沖是有效提升數(shù)據(jù)庫性能的方法。Pg_prewarm是一個(gè)十分常用的緩沖預(yù)熱插件。

安裝完插件后，我們可以使用select pg_prewarm(‘tablename’)來預(yù)熱某張表的數(shù)據(jù)。在某些大型統(tǒng)計(jì)報(bào)表開始之前先預(yù)熱數(shù)據(jù)是提高性能的很好的方法，用于預(yù)熱數(shù)據(jù)的PG插件也很多，大家可以根據(jù)需要選擇使用。

第六，優(yōu)化檢查點(diǎn)：檢查點(diǎn)是將共享緩沖區(qū)緩存中的臟頁刷新到磁盤的過程。降低檢查點(diǎn)的頻率和大小有助于減少磁盤 I/O 并提高性能。優(yōu)化檢查點(diǎn)性能的一些技術(shù)包括增加 checkpoint_timeout 和 checkpoint_completion_target 配置參數(shù)，以及使用更快的存儲(chǔ)硬件來存儲(chǔ)數(shù)據(jù)和 WAL 文件。

第七，調(diào)整CBO策略參數(shù)：調(diào)整 effective_cache_size，random_page_cost等多個(gè)參數(shù)都是CBO優(yōu)化器來評(píng)估各種操作的成本的重要參數(shù)，在一個(gè)有數(shù)萬甚至數(shù)十萬條SQL語句的數(shù)據(jù)庫系統(tǒng)而言，CBO能夠產(chǎn)生合理的執(zhí)行計(jì)劃對(duì)于數(shù)據(jù)庫性能至關(guān)重要，PG數(shù)據(jù)庫沒有Oracle那么強(qiáng)大的SQL優(yōu)化工具與優(yōu)化手段來輔助，因此設(shè)置好這些與CBO產(chǎn)生合理執(zhí)行計(jì)劃緊密相關(guān)的參數(shù)十分重要。effective_cache_size 配置參數(shù)用于估計(jì) PostgreSQL 可用的磁盤緩存量，從而確定掃描數(shù)據(jù)的成本。random_page_cost 配置參數(shù)確定隨機(jī)磁盤 I/O 相對(duì)于順序磁盤 I/O 的成本。設(shè)置此參數(shù)以準(zhǔn)確反映系統(tǒng)上隨機(jī)磁盤 I/O 的成本。據(jù)磁盤類型的不同，對(duì) random_page_cost 的設(shè)置也會(huì)有所不同：對(duì)于 HDD，可以設(shè)置為 4.0 到 4.5；對(duì)于 SSD，可以設(shè)置為 1.0 到 1.5。如果使用中央化的 SAN 存儲(chǔ)，可以根據(jù)其具體配置和性能進(jìn)行調(diào)整，為了設(shè)置合理的值，需要對(duì)你的存儲(chǔ)的隨機(jī)讀寫性能進(jìn)行測試。PG中還有幾個(gè)類似的參數(shù)，可能會(huì)影響到CBO生成執(zhí)行計(jì)劃，如果你發(fā)現(xiàn)你的PG數(shù)據(jù)庫中存在較多的錯(cuò)誤的執(zhí)行計(jì)劃，那么可以嘗試調(diào)整一下這些參數(shù)：seq_page_cost、cpu_tuple_costcpu_index_tuple_cost、cpu_operator_cost。

第八，操作系統(tǒng)參數(shù)優(yōu)化：主要是在VM的后臺(tái)寫、前臺(tái)寫、臟塊刷新策略、內(nèi)存換頁策略等方面進(jìn)行優(yōu)化，這方面我以前已經(jīng)寫過多篇文章介紹，在這里就不重復(fù)了，有興趣的朋友可以去翻閱一下我以前的發(fā)文。

綜上所述，這八個(gè)技巧可以大大提高 PostgreSQL 的 IO 性能。請務(wù)必仔細(xì)考慮您的硬件設(shè)置并配置適當(dāng)?shù)膮?shù)以獲得最佳結(jié)果。?