Linux 上的 PostgreSQL 效能調優

PostgreSQL 以其穩定性、標準相容性與強大的功能著稱，被廣泛應用於各種企業級應用中。然而，預設安裝的 PostgreSQL 配置通常僅適合一般開發或輕度負載環境。當面對高併發、大量資料讀寫或複雜查詢時，若未針對 Linux 系統進行適當的效能調優，資料庫很容易成為系統瓶頸。

本文將以 Ubuntu 22.04 / Debian 12 為例，介紹如何透過調整核心參數與 PostgreSQL 設定檔，顯著提升資料庫的 I/O 效能與記憶體使用效率。請注意，效能調優需根據實際硬體規格（特別是 RAM 與儲存設備類型）進行微調，以下建議值適用於擁有 16GB RAM 與 SSD 的標準伺服器環境。

第一步：調整 Linux 核心參數

PostgreSQL 嚴重依賴作業系統的記憶體管理與 I/O 排程器。在調整 PostgreSQL 設定前，我們需要先優化 Linux 核心。

1. 調整共享記憶體與信號量

PostgreSQL 使用 System V 共享記憶體來存放資料快取。預設值 shmmax 可能过小，導致無法分配足夠的共享記憶體。

編輯 /etc/sysctl.conf 檔案：

sudo nano /etc/sysctl.conf

在檔案末尾加入以下參數：

# 設定共享記憶體最大大小（建議設為實體 RAM 的 75%-90%）
kernel.shmmax = 68719476736
# 設定共享記憶體段數
kernel.shmall = 4294967296

套用設定：

sudo sysctl -p

2. 優化 I/O 排程器

對於 SSD 儲存設備，建議將 I/O 排程器改為 none 或 mq-deadline，以減少不必要的延遲。

檢查目前使用的排程器：

cat /sys/block/sda/queue/scheduler

若為 SSD，可透過 GRUB 永久設定。編輯 /etc/default/grub，在 GRUB_CMDLINE_LINUX 中加入 elevator=none（或 mq-deadline）：

GRUB_CMDLINE_LINUX="quiet splash elevator=none"

更新 GRUB 並重啟：

sudo update-grub
sudo reboot

第二步：調整 PostgreSQL 主要設定檔

PostgreSQL 的主要設定檔位於 /etc/postgresql/15/main/postgresql.conf（版本號可能因安裝而異，請確認你的實際版本）。

1. 記憶體配置 (`shared_buffers`)

shared_buffers 是 PostgreSQL 最重要的記憶體參數。它定義了 PostgreSQL 用於快取資料頁的記憶體大小。

建議值：實體 RAM 的 25% 左右，但通常不建議超過 8GB，因為作業系統頁快取（Page Cache）通常更有效率。對於 16GB RAM 的伺服器，設定為 2GB - 4GB 即可。

shared_buffers = 4GB

2. 工作記憶體 (`work_mem` 與 `maintenance_work_mem`)

work_mem：每個查詢排序或雜湊操作使用的記憶體。若設定過大，高併發時會消耗大量記憶體。
- 建議值：4MB - 16MB。若查詢複雜且記憶體充足，可調高至 32MB。
maintenance_work_mem：用於 VACUUM、CREATE INDEX 等維護操作的記憶體。
- 建議值：實體 RAM 的 2%-5%。例如 1GB - 2GB。

work_mem = 16MB
maintenance_work_mem = 1GB

3. WAL 與 I/O 優化

Write-Ahead Logging (WAL) 對效能影響巨大。調整以下參數可減少磁碟 I/O 壓力：

# 設定 WAL 檔案大小（預設 16MB，可調大至 64MB 或 1GB 減少寫入頻率）
wal_buffers = 64MB

# 減少 fsync 頻率，但需承擔資料遺失風險（僅限非關鍵業務測試）
# 生產環境建議保持預設或略微調整
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

4. 查詢規劃器優化

啟用並調整統計資訊收集，幫助查詢規劃器做出更佳的決策：

# 啟用平行查詢（根據 CPU 核心數調整）
max_parallel_workers_per_gather = 2

# 統計資訊更新頻率
default_statistics_target = 100

常見問題與解決方案

Q1: 調整後 PostgreSQL 無法啟動，報錯 "could not map anonymous shared memory"

這通常表示 shmmax 或 shmall 設定過小，或超過了核心限制。

解決方案：

確認 /etc/sysctl.conf 中的 kernel.shmmax 大於 shared_buffers 的大小。
執行 sudo sysctl -p 重新載入核心參數。
若問題依舊，檢查 /proc/sys/kernel/shmmax 的值是否與設定一致。

Q2: 效能提升不明顯，甚至變慢

過度調優參數可能導致記憶體交換（Swap）頻繁，反而降低效能。

解決方案：

監控記憶體使用情況：使用 htop 或 free -m 確認是否發生 Swap。若 Swap 使用率高，請降低 work_mem 或 shared_buffers。
檢查磁碟 I/O：使用 iostat -x 1 觀察 %util。若持續接近 100%，表示磁碟成為瓶頸，需考慮升級硬體或優化查詢，而非繼續調整軟體參數。
使用 pg_stat_statements 擴充套件找出最耗時的查詢，針對特定 SQL 進行索引優化，這通常比全域參數調優更有效。

小結

PostgreSQL 的效能調優是一個持續的過程，沒有「一勞永逸」的設定。本文介紹的基礎調優步驟，能為大多數中階應用帶來顯著改善。關鍵在於理解每個參數的意義，並透過監控工具（如 pg_stat_activity、iostat、htop）觀察實際負載，逐步微調。

記住，監控先於調優。在生產環境中進行任何變更前，請務必在測試環境驗證，並確保有完整的備份與回滾方案。希望這篇文章能幫助你更好地掌控 PostgreSQL 的效能表現。