Linux

Ubuntu 22.04 PostgreSQL 效能最佳化

Ubuntu 22.04 PostgreSQL Performance Optimization

PostgreSQL 是一個功能強大的開源關聯式資料庫，但預設設定通常較為保守。本文將介紹如何在 Ubuntu 22.04 上對 PostgreSQL 進行效能最佳化，以充分發揮硬體資源的潛力。

效能調校概述

PostgreSQL 的預設設定是為了確保在各種硬體環境下都能穩定運行，因此通常設定得相當保守。針對特定的硬體配置和使用情境進行調校，可以顯著提升資料庫效能。

效能調校的主要面向包括：

  • 記憶體配置：調整快取和工作記憶體
  • 連線管理：優化連線池和最大連線數
  • WAL 設定：平衡資料安全性和寫入效能
  • 自動清理：調整 autovacuum 參數
  • 查詢優化：使用 EXPLAIN 分析和索引優化

設定檔位置：

1

/etc/postgresql/14/main/postgresql.conf

修改設定後需要重新載入或重啟服務：

1
2
3
4
5

# 重新載入設定（大部分參數）
sudo systemctl reload postgresql

# 重新啟動服務（部分參數需要重啟）
sudo systemctl restart postgresql

shared_buffers 設定

shared_buffers 是 PostgreSQL 用於快取資料的共享記憶體區域，是最重要的效能參數之一。

建議設定

一般建議將 shared_buffers 設定為系統總記憶體的 25%：

1
2

# 編輯設定檔
sudo nano /etc/postgresql/14/main/postgresql.conf

根據系統記憶體調整：

1
2
3
4
5
6
7
8

# 8GB 記憶體系統
shared_buffers = 2GB

# 16GB 記憶體系統
shared_buffers = 4GB

# 32GB 記憶體系統
shared_buffers = 8GB

注意事項

  • 設定過高可能導致作業系統記憶體不足
  • 超過 8GB 後效益遞減，建議不超過總記憶體的 40%
  • 修改此參數需要重啟 PostgreSQL 服務

查看目前設定

1

SHOW shared_buffers;

work_mem 設定

work_mem 定義了每個查詢操作（如排序、雜湊等）可使用的記憶體大小。

建議設定

1
2
3
4
5
6
7
8

# 一般 OLTP 應用
work_mem = 64MB

# 複雜報表查詢
work_mem = 256MB

# 資料倉儲應用
work_mem = 512MB

計算公式

考慮最大連線數的影響：

1

work_mem = (總記憶體 - shared_buffers) / (max_connections * 3)

注意事項

  • 此值是每個操作的記憶體，非每個連線
  • 複雜查詢可能同時使用多個 work_mem
  • 可在 session 層級動態調整：
1

SET work_mem = '256MB';

effective_cache_size 設定

effective_cache_size 是 PostgreSQL 估計作業系統檔案快取大小的參數，用於查詢規劃器決策。

建議設定

通常設定為系統總記憶體的 50%-75%：

1
2
3
4
5

# 16GB 記憶體系統
effective_cache_size = 12GB

# 32GB 記憶體系統
effective_cache_size = 24GB

說明

  • 此參數不會實際分配記憶體
  • 影響查詢規劃器選擇索引掃描或序列掃描
  • 設定過低可能導致查詢計劃不佳

連線管理設定

max_connections

最大連線數設定：

1
2
3
4
5

# 一般 Web 應用
max_connections = 200

# 使用連線池時可降低
max_connections = 100

連線池建議

建議使用 PgBouncer 作為連線池：

1

sudo apt install pgbouncer -y

基本設定 /etc/pgbouncer/pgbouncer.ini：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

WAL 設定優化

Write-Ahead Logging (WAL) 設定影響資料持久性和寫入效能。

wal_buffers

WAL 緩衝區大小：

1
2
3
4
5

# 自動設定（推薦）
wal_buffers = -1

# 手動設定
wal_buffers = 64MB

checkpoint 設定

1
2
3
4
5
6
7
8

# 檢查點之間的最大 WAL 大小
max_wal_size = 2GB

# 檢查點之間的最小 WAL 大小
min_wal_size = 1GB

# 檢查點完成目標（0.0-1.0）
checkpoint_completion_target = 0.9

synchronous_commit

控制事務提交的同步行為：

1
2
3
4
5

# 預設值，最安全
synchronous_commit = on

# 效能優先，可能丟失少量資料
synchronous_commit = off

完整 WAL 設定範例

1
2
3
4
5
6

wal_level = replica
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
wal_compression = on

自動清理（autovacuum）調校

Autovacuum 負責回收已刪除資料佔用的空間並更新統計資訊。

基本設定

1
2
3
4
5
6
7
8

# 啟用自動清理
autovacuum = on

# 同時執行的最大 autovacuum 程序數
autovacuum_max_workers = 3

# 檢查間隔（毫秒）
autovacuum_naptime = 60s

閾值設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 觸發 vacuum 的最小行數變更
autovacuum_vacuum_threshold = 50

# 觸發 vacuum 的比例因子
autovacuum_vacuum_scale_factor = 0.1

# 觸發 analyze 的最小行數變更
autovacuum_analyze_threshold = 50

# 觸發 analyze 的比例因子
autovacuum_analyze_scale_factor = 0.05

大型資料表調校

對於大型資料表，可單獨設定較低的比例因子：

1
2
3
4

ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

監控 autovacuum

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查看 autovacuum 活動
SELECT schemaname, relname, last_vacuum, last_autovacuum,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables;

-- 查看需要 vacuum 的資料表
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

查詢效能分析（EXPLAIN）

使用 EXPLAIN 分析查詢執行計劃是效能調校的重要工具。

基本用法

1
2
3
4
5
6
7
8
9

-- 顯示執行計劃
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 顯示執行計劃和實際執行時間
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 詳細輸出
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

輸出解讀

1
2
3

Seq Scan on users  (cost=0.00..1234.00 rows=1 width=100) (actual time=0.015..12.345 rows=1 loops=1)
  Filter: (email = 'test@example.com'::text)
  Rows Removed by Filter: 9999

關鍵指標：

  • cost：估計成本（啟動成本..總成本）
  • rows：估計返回行數
  • actual time：實際執行時間（毫秒）
  • Seq Scan：順序掃描（可能需要索引）

常見執行計劃類型

類型說明
Seq Scan全表掃描
Index Scan索引掃描
Index Only Scan僅索引掃描（最佳）
Bitmap Index Scan點陣圖索引掃描
Nested Loop巢狀迴圈連接
Hash Join雜湊連接
Merge Join合併連接

索引優化

適當的索引可以大幅提升查詢效能。

建立索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 基本 B-tree 索引
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 複合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 部分索引
CREATE INDEX idx_orders_pending ON orders(status) WHERE status = 'pending';

-- GIN 索引（適用於陣列、JSONB）
CREATE INDEX idx_products_tags ON products USING GIN(tags);

查看索引使用情況

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查看索引使用統計
SELECT schemaname, relname, indexrelname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 找出未使用的索引
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%pkey%';

索引維護

1
2
3
4
5
6
7
8

-- 重建索引
REINDEX INDEX idx_users_email;

-- 重建表的所有索引
REINDEX TABLE users;

-- 並行重建（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY idx_users_email;

索引建議

  1. 為經常出現在 WHERE 子句的欄位建立索引
  2. 為外鍵欄位建立索引以加速 JOIN
  3. 考慮使用複合索引取代多個單欄位索引
  4. 定期檢查並移除未使用的索引
  5. 避免過度索引，每個索引都會增加寫入成本

完整設定範例

以下是 16GB 記憶體伺服器的建議設定：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# 記憶體設定
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB

# 連線設定
max_connections = 200

# WAL 設定
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
wal_compression = on

# 查詢規劃器
random_page_cost = 1.1
effective_io_concurrency = 200

# 日誌設定
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on

# Autovacuum 設定
autovacuum = on
autovacuum_max_workers = 3
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

效能監控

pg_stat_statements

啟用查詢統計擴充套件：

1

CREATE EXTENSION pg_stat_statements;

查看最耗資源的查詢：

1
2
3
4

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

即時監控

1
2
3
4
5
6
7
8
9

-- 查看目前連線
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';

-- 查看鎖定狀況
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

參考資料

comments powered by Disqus
© 2020 - 2026 Astroicers Blog
Built with Hugo
Theme Stack designed by Jimmy