PostgreSQL的內存參數

基礎信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg軟件目錄：/home/pg16/soft
pg數據目錄：/home/pg16/data
端口：5777

PostgreSQL 提供了多種內存參數，可以通過調整這些參數來優化數據庫的性能。以下是一些主要的內存參數及其簡要說明：

主要內存參數概述

1. shared_buffers：
   • 描述：決定 PostgreSQL 用于緩存表和索引數據的內存大小。通常推薦設置為系統內存的 25%-50%。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep shared_buffers
shared_buffers = 128MB                  # min 128kB

2. work_mem：
   • 描述：用于操作（如排序、哈希聚合、聯接等）內存的大小。每個操作會單獨使用這些內存，可以調多但不能過大。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep work_mem
work_mem = 4MB                          # min 64kB

3. maintenance_work_mem：
   • 描述：用于維護操作（如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等）的內存大小。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep maintenance_work_mem
maintenance_work_mem = 64MB             # min 1MB

4. temp_buffers：
   • 描述：用于每個數據庫會話的臨時表緩存內存大小。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep temp_buffers
temp_buffers = 8MB                      # min 800kB

5. max_connections：
   • 描述：允許的最大數據庫連接數。需要結合 shared_buffers 參數調節。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep max_connections
max_connections = 50                    # (change requires restart)

6. effective_cache_size：
   • 描述：設置 PostgreSQL 認為操作系統內核文件系統緩存及 PostgreSQL 緩存的大小。推薦設置為系統內存的 50%-75%。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep effective_cache_size
effective_cache_size = 1GB

7. wal_buffers：
   • 描述：用于存儲寫入 WAL (Write-Ahead Log) 的緩沖區大小。通常設置為 shared_buffers 的 3%-4%。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep wal_buffers
wal_buffers = 8MB                       # min 32kB, -1 sets based on shared_buffers

8. checkpoint_completion_target：
   • 描述：設置 checkpoint 完成平均占用時間的比例（0到1之間）。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep checkpoint_completion_target
checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0

9. autovacuum_work_mem：
   • 描述：用于自動化 VACUUM 操作的內存大小。
   • 示例：

[pg16@test ~]$ cat data/postgresql.conf |grep autovacuum_work_mem
autovacuum_work_mem =64MB

內存參數配置示例

以下是一個示例 postgresql.conf 中內存參數配置：

# 內存配置示例shared_buffers = 2GB                        # 共享內存緩沖區，設置為系統內存的 25%-50%
work_mem = 64MB                             # 每個操作的工作內存，適當調整以優化查詢性能
maintenance_work_mem = 512MB                # 維護任務的工作內存
temp_buffers = 64MB                         # 臨時表緩存內存
max_connections = 200                       # 最大連接數，根據并發需求設置
effective_cache_size = 6GB                  # 預期總緩存大小，包括操作系統的文件系統緩存
wal_buffers = 16MB                          # WAL 緩沖區
checkpoint_completion_target = 0.7          # checkpoint 完成時間比例
autovacuum_work_mem = 64MB                  # 自動 `VACUUM` 操作的內存大小

其他相關參數

除了主要內存參數，還有一些其他參數可能間接影響內存使用：

• sort_mem / work_mem: 不同版本的配置名稱不同，但作用相似。
• maintenance_work_mem: 影響 CREATE INDEX, VACUUM, REINDEX 等操作的性能。
• hash_mem_multiplier: 影響哈希連接的內存分配。
• max_worker_processes: 影響并行查詢的最大工作進程數。
• parallel_min_parallel_table_scan_size: 影響并行查詢的觸發條件。

調整和優化建議

1. 監控內存使用情況：
   使用系統工具（如 top, htop, free -m）和 PostgreSQL 內置視圖（如 pg_stat_activity）監控內存使用情況。

2. 逐步調整參數：
   從小到大逐步調整內存參數，觀察系統性能變化，避免一次性調整過大導致系統不穩定。

3. 平衡系統資源：
   結合數據庫和操作系統的內存需求，合理分配系統內存資源。確保操作系統有足夠的內存用于文件系統緩存和其他重要任務。

4. 考慮實際工作負載：
   根據實際工作負載特點調整內存參數。例如，查詢密集的環境下可以增大 work_mem，而批處理任務較多的環境下則更關注 maintenance_work_mem 等參數。

小結

調整和優化 PostgreSQL 的內存參數可以顯著提升數據庫的性能和效率。每個參數的具體值應根據系統資源、實際工作負載和性能需求進行合理配置和優化。通過逐步調整和監控，你可以找到最適合你的系統的配置參數組合。

謹記：心存敬畏，行有所止。