怎么在PostgreSQL中通過配置服務器參數優化性能

本篇文章給大家分享的是有關怎么在PostgreSQL中通過配置服務器參數優化性能，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

查看/設置參數值

我們使用 PostgreSQL 12，服務器的配置參數有 300 多個，運行時的參數值可以使用 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.     |
...

這些參數的詳細信息也可以使用 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 視圖不僅可以查看運行時的參數值，而且可以知道這些值的來源。

這些參數有些可以在服務器運行時進行修改，有些需要重啟服務器之后才能生效；不同修改方式的優先級不同，下圖列出了所有可能的修改方式：

以上設置方式的優先級從高到低；也就是說，在一個在事務內部設置的參數值會覆蓋其他任何設置，不過該設置只在當前事務中有效。需要注意的是，并非所有參數都可以支持所有的修改方式，具體可以參考官方文檔關于 pg_settings 的說明。

接下來我們介紹幾個重要的配置參數。

max_connections

max_connections 決定了客戶端的最大并發連接數，默認值通常為 100。如果出現連接數過多，無法連接數據庫的錯誤時，可能需要考慮增加最大連接數。不過，修改該參數還需要考慮對其他參數的影響（尤其是 work_mem）；因為它們是基于每個連接設置的值，增加連接數也會導致這些內存使用量的增加。

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

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

修改配置文件 postgresql.conf；

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

對于主從復制中的從節點，必須將該參數的值設置為大于等于主節點上的值；否則，從節點將無法執行查詢操作。

shared_buffers

除了操作系統的 I/O 緩存之外，PostgreSQL 還會使用自己的內部緩存。PostgreSQL 共享內存緩沖區由參數 shared_buffers 設置，它決定了 PostgreSQL 能夠使用的專用緩存大小。

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

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

雖然增加 shared_buffers 的值可以提高以讀為主的系統性能，但是可能影響以寫為主的系統性能；因為 shared_buffers 的全部內容必須在寫入操作時進行處理。

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

修改配置文件 postgresql.conf；

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

wal_buffers

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

默認的 WAL 緩沖大小由 wal_buffers 參數進行設置，初始值為 16MB（shared_buffers 的 1/32）。WAL 緩沖區在每次事務提交時都會寫入磁盤，因此過大的值并不會帶來顯著的性能提升。不過，對于大量并發的寫入操作，適當增加該參數的值可以提高系統的性能。

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

修改配置文件 postgresql.conf；

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

effective_cache_size

effective_cache_size 參數為 PostgreSQL 提供了一個可供操作系統和數據庫使用的緩存估值（考慮了操作系統自身和其他應用之后）。

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

effective_cache_size 默認值為 4 GB，保守估可以設置為是系統可用內存的 1/2。通常對于專用數據庫服務器可以設置為系統總內存的 75％，可以根據特定的服務器工作負載進行調整。如果 effective_cache_size 設置過低，查詢計劃器可能會忽略某些索引，即使通過索引可以明顯增加查詢的性能。

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

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

修改配置文件 postgresql.conf；

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

work_mem

work_mem 參數用于復雜的排序操作，它決定了中間結果（例如哈希表）或者排序操作可以使用的最大內存。

如果設置了合適的 work_mem 值，大部分的排序操作都在內存中執行，而不需要使用磁盤存儲臨時結果。對于復雜的查詢，可能會執行并發的排序或者哈希操作，每個操作都可以最多使用該參數設置的內存。另外，多個會話可能同時執行排序操作。因此，排序占用的總內存可能是 work_mem 的許多倍；work_mem 的值不能設置的過高，因為它可能導致內存使用瓶頸。

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

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

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

maintenance_work_mem

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

由于一個數據庫會話同時只能執行一個維護操作，一般不會存在并發的維護操作；所以將該參數設置的比 work_mem 大很多也不會有問題，更大的維護內存還能夠提高數據庫清理和數據導入的性能。

唯一需要注意的是，如果啟動了 autovacuum，可能會占用 autovacuum_max_workers（默認為 3）倍 work_mem 設置的內存。我們也可以為此設置單獨的 autovacuum_work_mem 參數。

maintenance_work_mem 參數的默認值為 64MB，支持從事務級別到命令行參數的各種修改方式。

總結

調整服務器的配置參數通常是我們在安裝 PostgreSQL 之后需要進行優化的第一步，本文介紹了如何進行參數的設置以及幾個重要參數的作用和調整目標。

PostgreSQL 提供了大量可以調整的參數，PGTune 工具給我們提供了一個很好的嘗試起點，wiki.postgresql.org列出了最常見的幾個參數優化；不過性能優化的關鍵是對工作負載進行基準測試并且和已知的基線進行比較。

另外，數據庫的配置優化并不是優化的全部。

例如，編寫不當的查詢語句可能無法通過調整參數解決性能問題；此時我們需要通過其他方式進行優化，比如利用索引或者修改查詢的連接方式等。

以上就是怎么在PostgreSQL中通過配置服務器參數優化性能，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。