大家好！我是只談技術不剪發(fā)的 Tony 老師。今天我們來聊聊 PostgreSQL 的性能優(yōu)化；數(shù)據(jù)庫優(yōu)化是一個系統(tǒng)的工程，本文只專注于服務器的參數(shù)配置優(yōu)化。
默認安裝時，PostgreSQL 的配置參數(shù)通常都偏小，不太適合作為生產(chǎn)服務器使用。所以，安裝 PostgreSQL 數(shù)據(jù)庫之后首先需要執(zhí)行的操作就是對服務器的配置參數(shù)進行調(diào)整。
查看/設置參數(shù)值
我們使用 PostgreSQL 12，服務器的配置參數(shù)有 300 多個，運行時的參數(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ù)的詳細信息也可以使用 pg_settings 視圖進行查看：
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 視圖不僅可以查看運行時的參數(shù)值，而且可以知道這些值的來源。
這些參數(shù)有些可以在服務器運行時進行修改，有些需要重啟服務器之后才能生效；不同修改方式的優(yōu)先級不同，下圖列出了所有可能的修改方式：
以上設置方式的優(yōu)先級從高到低；也就是說，在一個在事務內(nèi)部設置的參數(shù)值會覆蓋其他任何設置，不過該設置只在當前事務中有效。需要注意的是，并非所有參數(shù)都可以支持所有的修改方式，具體可以參考官方文檔關于 pg_settings 的說明。
接下來我們介紹幾個重要的配置參數(shù)。
max_connections
max_connections 決定了客戶端的最大并發(fā)連接數(shù)，默認值通常為 100。如果出現(xiàn)連接數(shù)過多，無法連接數(shù)據(jù)庫的錯誤時，可能需要考慮增加最大連接數(shù)。不過，修改該參數(shù)還需要考慮對其他參數(shù)的影響（尤其是 work_mem）；因為它們是基于每個連接設置的值，增加連接數(shù)也會導致這些內(nèi)存使用量的增加。
通常來說，商業(yè)服務器至少可以支持幾百個連接。如果應用的連接數(shù)到大上千或者幾千，可以考慮使用連接池技術減少連接的消耗。
修改 max_connections 的方式有兩種，修改之后必須重啟服務器才能生效：
修改配置文件 postgresql.conf；
alter system set max_connections = N;，該命令會修改配置文件 postgresql.auto.conf。
對于主從復制中的從節(jié)點，必須將該參數(shù)的值設置為大于等于主節(jié)點上的值；否則，從節(jié)點將無法執(zhí)行查詢操作。
shared_buffers
除了操作系統(tǒng)的 I/O 緩存之外，PostgreSQL 還會使用自己的內(nèi)部緩存。PostgreSQL 共享內(nèi)存緩沖區(qū)由參數(shù) shared_buffers 設置，它決定了 PostgreSQL 能夠使用的專用緩存大小。
為確保在所有機器和操作系統(tǒng)上的兼容性，PostgreSQL 默認將該值設置得很小，通常是 128 MB。因此，增加 shared_buffers 的值是提高性能最有效的設置之一。
雖然對于 shared_buffers 沒有具體的推薦值，但是可以針對具體的系統(tǒng)計算出一個大概的值。一般來說，對于專用的數(shù)據(jù)庫服務器，shared_buffers 大概可以設置為系統(tǒng)內(nèi)存的 25%。增加 shared_buffers 的值通常可以提高性能，例如，當整個數(shù)據(jù)庫都可以被加載到緩存中時，可以明顯減少磁盤的讀取操作。由于 PostgreSQL 還依賴于操作系統(tǒng)的緩存，大于內(nèi)存 40% 的 shared_buffers 并不會帶來性能的提示，反而可能會下降。
雖然增加 shared_buffers 的值可以提高以讀為主的系統(tǒng)性能，但是可能影響以寫為主的系統(tǒng)性能；因為 shared_buffers 的全部內(nèi)容必須在寫入操作時進行處理。
修改 shared_buffers 的方式有兩種，修改之后必須重啟服務器才能生效：
修改配置文件 postgresql.conf；
alter system set shared_buffers = 'xxx';，該命令會修改配置文件 postgresql.auto.conf。另外，增加 shared_buffers 的值通常也需要相應地增加 max_wal_size 的值，以便延長檢查點的時間間隔。
wal_buffers
PostgreSQL 使用預寫日志（WAL）確保數(shù)據(jù)的持久性；與 shared_buffers 作用類似，PostgreSQL 將 WAL 日志寫入緩沖并且批量寫入磁盤。
默認的 WAL 緩沖大小由 wal_buffers 參數(shù)進行設置，初始值為 16MB（shared_buffers 的 1/32）。WAL 緩沖區(qū)在每次事務提交時都會寫入磁盤，因此過大的值并不會帶來顯著的性能提升。不過，對于大量并發(fā)的寫入操作，適當增加該參數(shù)的值可以提高系統(tǒng)的性能。
修改 wal_buffers 的方式有兩種，修改之后必須重啟服務器才能生效：
修改配置文件 postgresql.conf；
alter system set wal_buffers = 'xxx';，該命令會修改配置文件 postgresql.auto.conf。
effective_cache_size
effective_cache_size 參數(shù)為 PostgreSQL 提供了一個可供操作系統(tǒng)和數(shù)據(jù)庫使用的緩存估值（考慮了操作系統(tǒng)自身和其他應用之后）。
該參數(shù)只是一個評估值，而不是實際的分配值；它僅用于 PostgreSQL 查詢計劃器判斷索引的代價，越大的值越可能使用索引掃描，否則更可能使用表的順序掃描。
effective_cache_size 默認值為 4 GB，保守估可以設置為是系統(tǒng)可用內(nèi)存的 1/2。通常對于專用數(shù)據(jù)庫服務器可以設置為系統(tǒng)總內(nèi)存的 75％，可以根據(jù)特定的服務器工作負載進行調(diào)整。如果 effective_cache_size 設置過低，查詢計劃器可能會忽略某些索引，即使通過索引可以明顯增加查詢的性能。
通過操作系統(tǒng)的統(tǒng)計信息可以得到一個更好的估計值。對于 UNIX/LINUX 系統(tǒng)，將 free 或者 top 命令結果中的 free 加上 cached；；對于 Windows 系統(tǒng)，查看任務管理器 Performance 頁面中的“System Cache”。
修改 effective_cache_size 不需要重啟服務器，通過以下方式修改之后執(zhí)行pg_ctl reload或者SELECT pg_reload_conf();重新加載即可：
修改配置文件 postgresql.conf；
alter system set effective_cache_size = 'xxx';，該命令會修改配置文件 postgresql.auto.conf。
work_mem
work_mem 參數(shù)用于復雜的排序操作，它決定了中間結果（例如哈希表）或者排序操作可以使用的最大內(nèi)存。
如果設置了合適的 work_mem 值，大部分的排序操作都在內(nèi)存中執(zhí)行，而不需要使用磁盤存儲臨時結果。對于復雜的查詢，可能會執(zhí)行并發(fā)的排序或者哈希操作，每個操作都可以最多使用該參數(shù)設置的內(nèi)存。另外，多個會話可能同時執(zhí)行排序操作。因此，排序占用的總內(nèi)存可能是 work_mem 的許多倍；work_mem 的值不能設置的過高，因為它可能導致內(nèi)存使用瓶頸。
該參數(shù)的默認值為 4MB，支持從事務級別到命令行參數(shù)的各種修改方式。理想的方式是將全局的 work_mem 參數(shù)設置為一個較低的值，然后為具體的查詢指定更大的值：
SET LOCAL work_mem = '256MB';
SELECT * FROM db ORDER BY LOWER(name);
涉及排序操作的 SQL 子句包括 ORDER BY、DISTINCT 以及排序合并連接（Sort Merge Join）。使用哈希表的操作包括哈希連接（Hash Join）、基于哈希的聚合以及基于哈希的 IN 子查詢實現(xiàn)。
maintenance_work_mem
maintenance_work_mem 參數(shù)指定了日常維護操作允許占用的最大內(nèi)存，例如 VACUUM、CREATE INDEX 以及 ALTER TABLE ADD FOREIGN KEY 等操作。
由于一個數(shù)據(jù)庫會話同時只能執(zhí)行一個維護操作，一般不會存在并發(fā)的維護操作；所以將該參數(shù)設置的比 work_mem 大很多也不會有問題，更大的維護內(nèi)存還能夠提高數(shù)據(jù)庫清理和數(shù)據(jù)導入的性能。
唯一需要注意的是，如果啟動了 autovacuum，可能會占用 autovacuum_max_workers（默認為 3）倍 work_mem 設置的內(nèi)存。我們也可以為此設置單獨的 autovacuum_work_mem 參數(shù)。
maintenance_work_mem 參數(shù)的默認值為 64MB，支持從事務級別到命令行參數(shù)的各種修改方式。
總結
調(diào)整服務器的配置參數(shù)通常是我們在安裝 PostgreSQL 之后需要進行優(yōu)化的第一步，本文介紹了如何進行參數(shù)的設置以及幾個重要參數(shù)的作用和調(diào)整目標。
PostgreSQL 提供了大量可以調(diào)整的參數(shù)，PGTune 工具給我們提供了一個很好的嘗試起點，wiki.postgresql.org列出了最常見的幾個參數(shù)優(yōu)化；不過性能優(yōu)化的關鍵是對工作負載進行基準測試并且和已知的基線進行比較。
另外，數(shù)據(jù)庫的配置優(yōu)化并不是優(yōu)化的全部。
例如，編寫不當?shù)牟樵冋Z句可能無法通過調(diào)整參數(shù)解決性能問題；此時我們需要通過其他方式進行優(yōu)化，比如利用索引或者修改查詢的連接方式等。
文章來源：腳本之家
來源地址：https://www.jb51.net/article/204777.htm
申請創(chuàng)業(yè)報道，分享創(chuàng)業(yè)好點子。點擊此處，共同探討創(chuàng)業(yè)新機遇！
