大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們來聊聊 PostgreSQL 的性能優(yōu)化；數(shù)據(jù)庫優(yōu)化是一個(gè)系統(tǒng)的工程，本文只專注于服務(wù)器的參數(shù)配置優(yōu)化。

默認(rèn)安裝時(shí)，PostgreSQL 的配置參數(shù)通常都偏小，不太適合作為生產(chǎn)服務(wù)器使用。所以，安裝 PostgreSQL 數(shù)據(jù)庫之后首先需要執(zhí)行的操作就是對服務(wù)器的配置參數(shù)進(jìn)行調(diào)整。

查看/設(shè)置參數(shù)值

我們使用 PostgreSQL 12，服務(wù)器的配置參數(shù)有 300 多個(gè)，運(yùn)行時(shí)的參數(shù)值可以使用 SHOW 命令查看：

show server_version;
server_version|
--------------|
12.3  |
show all;
name    |setting     |description            |
-----------------------------------|-----------------------------------------|----------------------------------------------------------------------------------------------------------|
allow_system_table_mods  |off     |Allows modifications of the structure of system tables.       |
application_name   |DBeaver 7.0.5 - SQLEditor Script-13.sql>|Sets the application name to be reported in statistics and logs.      |
archive_cleanup_command  |      |Sets the shell command that will be executed at every restart point.     |
...

這些參數(shù)的詳細(xì)信息也可以使用 pg_settings 視圖進(jìn)行查看：

SELECT name, setting, unit, source, sourcefile, sourceline, short_desc
from pg_settings
where name like '%buffers%';
name  |setting|unit|source  |sourcefile    |sourceline|short_desc       |
--------------|-------|----|------------------|--------------------------------------|----------|------------------------------------------------------------------|
shared_buffers|16384 |8kB |configuration file|/var/lib/pgsql/12/data/postgresql.conf| 121|Sets the number of shared memory buffers used by the server. |
temp_buffers |1024 |8kB |default  |     |  |Sets the maximum number of temporary buffers used by each session.|
wal_buffers |512 |8kB |override  |     |  |Sets the number of disk-page buffers in shared memory for WAL. |

通過 pg_settings 視圖不僅可以查看運(yùn)行時(shí)的參數(shù)值，而且可以知道這些值的來源。

這些參數(shù)有些可以在服務(wù)器運(yùn)行時(shí)進(jìn)行修改，有些需要重啟服務(wù)器之后才能生效；不同修改方式的優(yōu)先級不同，下圖列出了所有可能的修改方式：

以上設(shè)置方式的優(yōu)先級從高到低；也就是說，在一個(gè)在事務(wù)內(nèi)部設(shè)置的參數(shù)值會(huì)覆蓋其他任何設(shè)置，不過該設(shè)置只在當(dāng)前事務(wù)中有效。需要注意的是，并非所有參數(shù)都可以支持所有的修改方式，具體可以參考官方文檔關(guān)于 pg_settings 的說明。

接下來我們介紹幾個(gè)重要的配置參數(shù)。

max_connections

max_connections 決定了客戶端的最大并發(fā)連接數(shù)，默認(rèn)值通常為 100。如果出現(xiàn)連接數(shù)過多，無法連接數(shù)據(jù)庫的錯(cuò)誤時(shí)，可能需要考慮增加最大連接數(shù)。不過，修改該參數(shù)還需要考慮對其他參數(shù)的影響（尤其是 work_mem）；因?yàn)樗鼈兪腔诿總€(gè)連接設(shè)置的值，增加連接數(shù)也會(huì)導(dǎo)致這些內(nèi)存使用量的增加。

通常來說，商業(yè)服務(wù)器至少可以支持幾百個(gè)連接。如果應(yīng)用的連接數(shù)到大上千或者幾千，可以考慮使用連接池技術(shù)減少連接的消耗。

修改 max_connections 的方式有兩種，修改之后必須重啟服務(wù)器才能生效：

修改配置文件 postgresql.conf；

alter system set max_connections = N;，該命令會(huì)修改配置文件 postgresql.auto.conf。

對于主從復(fù)制中的從節(jié)點(diǎn)，必須將該參數(shù)的值設(shè)置為大于等于主節(jié)點(diǎn)上的值；否則，從節(jié)點(diǎn)將無法執(zhí)行查詢操作。

shared_buffers

除了操作系統(tǒng)的 I/O 緩存之外，PostgreSQL 還會(huì)使用自己的內(nèi)部緩存。PostgreSQL 共享內(nèi)存緩沖區(qū)由參數(shù) shared_buffers 設(shè)置，它決定了 PostgreSQL 能夠使用的專用緩存大小。

為確保在所有機(jī)器和操作系統(tǒng)上的兼容性，PostgreSQL 默認(rèn)將該值設(shè)置得很小，通常是 128 MB。因此，增加 shared_buffers 的值是提高性能最有效的設(shè)置之一。

雖然對于 shared_buffers 沒有具體的推薦值，但是可以針對具體的系統(tǒng)計(jì)算出一個(gè)大概的值。一般來說，對于專用的數(shù)據(jù)庫服務(wù)器，shared_buffers 大概可以設(shè)置為系統(tǒng)內(nèi)存的 25%。增加 shared_buffers 的值通常可以提高性能，例如，當(dāng)整個(gè)數(shù)據(jù)庫都可以被加載到緩存中時(shí)，可以明顯減少磁盤的讀取操作。由于 PostgreSQL 還依賴于操作系統(tǒng)的緩存，大于內(nèi)存 40% 的 shared_buffers 并不會(huì)帶來性能的提示，反而可能會(huì)下降。

雖然增加 shared_buffers 的值可以提高以讀為主的系統(tǒng)性能，但是可能影響以寫為主的系統(tǒng)性能；因?yàn)?shared_buffers 的全部內(nèi)容必須在寫入操作時(shí)進(jìn)行處理。

修改 shared_buffers 的方式有兩種，修改之后必須重啟服務(wù)器才能生效：

修改配置文件 postgresql.conf；

alter system set shared_buffers = 'xxx';，該命令會(huì)修改配置文件 postgresql.auto.conf。另外，增加 shared_buffers 的值通常也需要相應(yīng)地增加 max_wal_size 的值，以便延長檢查點(diǎn)的時(shí)間間隔。

wal_buffers

PostgreSQL 使用預(yù)寫日志（WAL）確保數(shù)據(jù)的持久性；與 shared_buffers 作用類似，PostgreSQL 將 WAL 日志寫入緩沖并且批量寫入磁盤。

默認(rèn)的 WAL 緩沖大小由 wal_buffers 參數(shù)進(jìn)行設(shè)置，初始值為 16MB（shared_buffers 的 1/32）。WAL 緩沖區(qū)在每次事務(wù)提交時(shí)都會(huì)寫入磁盤，因此過大的值并不會(huì)帶來顯著的性能提升。不過，對于大量并發(fā)的寫入操作，適當(dāng)增加該參數(shù)的值可以提高系統(tǒng)的性能。

修改 wal_buffers 的方式有兩種，修改之后必須重啟服務(wù)器才能生效：

修改配置文件 postgresql.conf；

alter system set wal_buffers = 'xxx';，該命令會(huì)修改配置文件 postgresql.auto.conf。

effective_cache_size

effective_cache_size 參數(shù)為 PostgreSQL 提供了一個(gè)可供操作系統(tǒng)和數(shù)據(jù)庫使用的緩存估值（考慮了操作系統(tǒng)自身和其他應(yīng)用之后）。

該參數(shù)只是一個(gè)評估值，而不是實(shí)際的分配值；它僅用于 PostgreSQL 查詢計(jì)劃器判斷索引的代價(jià)，越大的值越可能使用索引掃描，否則更可能使用表的順序掃描。

effective_cache_size 默認(rèn)值為 4 GB，保守估可以設(shè)置為是系統(tǒng)可用內(nèi)存的 1/2。通常對于專用數(shù)據(jù)庫服務(wù)器可以設(shè)置為系統(tǒng)總內(nèi)存的 75％，可以根據(jù)特定的服務(wù)器工作負(fù)載進(jìn)行調(diào)整。如果 effective_cache_size 設(shè)置過低，查詢計(jì)劃器可能會(huì)忽略某些索引，即使通過索引可以明顯增加查詢的性能。

通過操作系統(tǒng)的統(tǒng)計(jì)信息可以得到一個(gè)更好的估計(jì)值。對于 UNIX/LINUX 系統(tǒng)，將 free 或者 top 命令結(jié)果中的 free 加上 cached；；對于 Windows 系統(tǒng)，查看任務(wù)管理器 Performance 頁面中的“System Cache”。

修改 effective_cache_size 不需要重啟服務(wù)器，通過以下方式修改之后執(zhí)行pg_ctl reload或者SELECT pg_reload_conf();重新加載即可：

修改配置文件 postgresql.conf；

alter system set effective_cache_size = 'xxx';，該命令會(huì)修改配置文件 postgresql.auto.conf。

work_mem

work_mem 參數(shù)用于復(fù)雜的排序操作，它決定了中間結(jié)果（例如哈希表）或者排序操作可以使用的最大內(nèi)存。

如果設(shè)置了合適的 work_mem 值，大部分的排序操作都在內(nèi)存中執(zhí)行，而不需要使用磁盤存儲(chǔ)臨時(shí)結(jié)果。對于復(fù)雜的查詢，可能會(huì)執(zhí)行并發(fā)的排序或者哈希操作，每個(gè)操作都可以最多使用該參數(shù)設(shè)置的內(nèi)存。另外，多個(gè)會(huì)話可能同時(shí)執(zhí)行排序操作。因此，排序占用的總內(nèi)存可能是 work_mem 的許多倍；work_mem 的值不能設(shè)置的過高，因?yàn)樗赡軐?dǎo)致內(nèi)存使用瓶頸。

該參數(shù)的默認(rèn)值為 4MB，支持從事務(wù)級別到命令行參數(shù)的各種修改方式。理想的方式是將全局的 work_mem 參數(shù)設(shè)置為一個(gè)較低的值，然后為具體的查詢指定更大的值：

SET LOCAL work_mem = '256MB';
SELECT * FROM db ORDER BY LOWER(name);

涉及排序操作的 SQL 子句包括 ORDER BY、DISTINCT 以及排序合并連接（Sort Merge Join）。使用哈希表的操作包括哈希連接（Hash Join）、基于哈希的聚合以及基于哈希的 IN 子查詢實(shí)現(xiàn)。

maintenance_work_mem

maintenance_work_mem 參數(shù)指定了日常維護(hù)操作允許占用的最大內(nèi)存，例如 VACUUM、CREATE INDEX 以及 ALTER TABLE ADD FOREIGN KEY 等操作。

由于一個(gè)數(shù)據(jù)庫會(huì)話同時(shí)只能執(zhí)行一個(gè)維護(hù)操作，一般不會(huì)存在并發(fā)的維護(hù)操作；所以將該參數(shù)設(shè)置的比 work_mem 大很多也不會(huì)有問題，更大的維護(hù)內(nèi)存還能夠提高數(shù)據(jù)庫清理和數(shù)據(jù)導(dǎo)入的性能。

唯一需要注意的是，如果啟動(dòng)了 autovacuum，可能會(huì)占用 autovacuum_max_workers（默認(rèn)為 3）倍 work_mem 設(shè)置的內(nèi)存。我們也可以為此設(shè)置單獨(dú)的 autovacuum_work_mem 參數(shù)。

maintenance_work_mem 參數(shù)的默認(rèn)值為 64MB，支持從事務(wù)級別到命令行參數(shù)的各種修改方式。

總結(jié)

調(diào)整服務(wù)器的配置參數(shù)通常是我們在安裝 PostgreSQL 之后需要進(jìn)行優(yōu)化的第一步，本文介紹了如何進(jìn)行參數(shù)的設(shè)置以及幾個(gè)重要參數(shù)的作用和調(diào)整目標(biāo)。

PostgreSQL 提供了大量可以調(diào)整的參數(shù)，PGTune 工具給我們提供了一個(gè)很好的嘗試起點(diǎn)，wiki.postgresql.org列出了最常見的幾個(gè)參數(shù)優(yōu)化；不過性能優(yōu)化的關(guān)鍵是對工作負(fù)載進(jìn)行基準(zhǔn)測試并且和已知的基線進(jìn)行比較。

另外，數(shù)據(jù)庫的配置優(yōu)化并不是優(yōu)化的全部。

例如，編寫不當(dāng)?shù)牟樵冋Z句可能無法通過調(diào)整參數(shù)解決性能問題；此時(shí)我們需要通過其他方式進(jìn)行優(yōu)化，比如利用索引或者修改查詢的連接方式等。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。