Tối ưu PostgreSQL: 12 tham số ăn theo ổ NVMe và RAM

Trả lời nhanh: Tối ưu Postgres hiệu quả nằm ở 4 nhóm tham số: bộ nhớ (shared_buffers ~25% RAM, effective_cache_size ~75%, work_mem theo độ phức tạp query), planner (random_page_cost=1.1 trên NVMe, bảo planner rằng đọc ngẫu nhiên đã rẻ, cứ mạnh dạn dùng index), WAL (wal_compression, checkpoint giãn ra), và autovacuum (đủ mạnh để bảng không phình). Nhưng nhớ thứ tự tác động: index đúng > query gọn > tham số, tham số chỉ phát huy trên nền schema tử tế.

Bài trước cài Postgres chuẩn với 5 tham số khởi điểm; bài này đi sâu bản đầy đủ 12 tham số cho máy đã có tải thật, kèm lý do từng con số để bạn chỉnh theo máy của mình thay vì chép mù. Tinh thần xuyên suốt: cấu hình mặc định của Postgres giả định phần cứng 15 năm trước; máy NVMe hiện đại xứng đáng được khai đúng sức.

Trước khi chỉnh: đo đã

Ba phép đo lấy mốc, mỗi phép một dòng:

-- ty le cache hit (muon > 0.99 voi web app)
SELECT sum(blks_hit)::float/nullif(sum(blks_hit)+sum(blks_read),0) FROM pg_stat_database;
-- bat query cham hon 500ms vao log
ALTER SYSTEM SET log_min_duration_statement = 500; SELECT pg_reload_conf();
-- 5 query ngon thoi gian nhat (can extension pg_stat_statements)
SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

Chỉnh xong quay lại đo đúng ba phép này, trước/sau rõ ràng, khỏi tranh cãi bằng cảm giác.

Nhóm 1: Bộ nhớ - cho dữ liệu sống trong RAM

# may 8 GB danh chu yeu cho database
shared_buffers = 2GB              # cache trang cua chinh Postgres, ~25% RAM
effective_cache_size = 6GB        # RAM he thong con lai cung dang cache file, bao planner biet
work_mem = 32MB                   # moi sort/hash mot suat, query phuc tap x nhieu ket noi = can than
maintenance_work_mem = 512MB      # VACUUM/CREATE INDEX chay le, cho han suat to

Sai lầm quen thuộc: shared_buffers 75% RAM "cho máu", phản tác dụng vì Postgres dựa cả vào page cache của hệ điều hành; 25-40% là vùng ngọt đã kiểm chứng nhiều năm. work_mem là suất mỗi phép chứ không phải tổng: app 100 kết nối mà work_mem 256MB là lời mời OOM.

Nhóm 2: Planner - khai thật về ổ NVMe

random_page_cost = 1.1            # mac dinh 4.0 = gia dinh o co; NVMe doc ngau nhien ~ tuan tu
effective_io_concurrency = 200    # NVMe nuot duoc nhieu I/O song song, cho prefetch manh tay

Đây là cặp tham số "ăn theo ổ" đúng nghĩa: để mặc định trên NVMe, planner tưởng đọc ngẫu nhiên đắt gấp 4 nên nhiều khi chọn quét cả bảng thay vì dùng index, bạn có index tốt mà nó không thèm dùng. Hạ về 1.1, nhiều query nặng tự đổi kế hoạch sang index scan mà không phải sửa dòng SQL nào. Đây cũng là lý do cùng một app, chuyển từ ổ thường sang NVMe nên chỉnh lại cấu hình thay vì chỉ chuyển nhà.

Nhóm 3: WAL - ghi nhật ký cho êm

wal_compression = on              # nen ban ghi WAL, giam I/O ghi
max_wal_size = 4GB                # checkpoint thua hon, ghi don deu hon
checkpoint_completion_target = 0.9 # dan trai checkpoint, tranh bao ghi dot ngot

WAL là nhật ký mọi thay đổi, nơi hứng fsync mỗi commit. Bộ ba trên làm nhịp ghi đều và nhẹ hơn, tránh cảnh cứ vài phút một cơn bão ghi làm query khựng đồng loạt. Trên ổ NVMe có tụ chống mất điện (chuẩn enterprise), fsync vốn đã rẻ, cộng cấu hình này là hệ ghi mượt như không.

Nhóm 4: Autovacuum - chống bệnh phình âm thầm

autovacuum_vacuum_scale_factor = 0.05   # mac dinh 0.2: bang 10 trieu dong doi 2 trieu dong chet moi don
autovacuum_analyze_scale_factor = 0.02  # thong ke tuoi hon cho planner
autovacuum_max_workers = 4

Postgres không ghi đè dòng khi UPDATE, nó tạo bản mới, bản cũ thành "dòng chết" chờ vacuum dọn. Mặc định đợi 20% bảng chết mới dọn: với bảng lớn nghĩa là hàng triệu xác chiếm chỗ, index phình, cache loãng, hiện tượng "database tự nhiên chậm dần sau vài tháng" trứ danh. Hạ ngưỡng xuống 5%, máy NVMe thừa sức gánh vacuum chạy thường xuyên hơn đổi lấy bảng luôn thon.

Chỉnh xong - áp dụng và nghiệm thu

sudo systemctl restart postgresql   # shared_buffers can restart; da so con lai chi can reload
# do lai 3 phep dau bai sau 1-2 ngay tai that

Kỳ vọng thực tế theo kinh nghiệm chung: cache hit nhích lên trên 99%, nhóm query dùng index nhanh lên rõ (đặc biệt sau random_page_cost), và biểu đồ độ trễ phẳng hơn ở giờ đông. Nếu chỉnh rồi vẫn ì: quay về tam giác gốc, thiếu index, query N+1, hoặc nền ổ đĩa yếu (đo fio để loại trừ trước khi nghi ngờ tiếp).

Áp dụng và nghiệm thu, đúng quy trình

# 1. Sua file cau hinh
sudo -u postgres psql -c "SHOW config_file;"     # biet file nam dau
sudo nano /etc/postgresql/16/main/postgresql.conf

# 2. Tham so nao can restart, tham so nao chi can reload
sudo -u postgres psql -c "SELECT name,setting,pending_restart FROM pg_settings WHERE pending_restart;"
sudo systemctl reload postgresql     # du cho phan lon tham so
sudo systemctl restart postgresql    # chi khi pending_restart = true

# 3. Xac nhan gia tri that su dang chay
sudo -u postgres psql -c "SELECT name,setting,unit,source FROM pg_settings WHERE name IN
  ('shared_buffers','effective_cache_size','work_mem','random_page_cost','effective_io_concurrency','max_wal_size');"

Cột nguồn cho biết giá trị đến từ tệp cấu hình hay vẫn là mặc định. Đây là bước nhiều người bỏ qua rồi tưởng đã tối ưu trong khi tiến trình vẫn chạy giá trị cũ.

Đo trước và sau, để biết có tác dụng thật không

# Ty le trung dem, muon tren 0,99 voi ung dung web
SELECT round(sum(blks_hit)*100.0/nullif(sum(blks_hit)+sum(blks_read),0),2) AS ty_le_trung
FROM pg_stat_database;

# Truy van ton thoi gian nhat
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, round(mean_exec_time::numeric,1) tb_ms, round(total_exec_time::numeric) tong_ms,
       left(query,90) truy_van
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

# Do tai gia lap truoc va sau khi chinh
pgbench -i -s 50 app_db
pgbench -c 20 -j 4 -T 120 app_db     # ghi lai so tps de so sanh

Quy tắc: đổi một nhóm tham số, đo lại, ghi con số. Đổi cả bốn nhóm cùng lúc rồi thấy nhanh hơn thì bạn không biết nhờ cái nào, và lần sau gặp máy khác lại phải mò từ đầu.

Bốn tham số hay bị đặt sai

Thứ tự tác động, đừng làm ngược

1. Chỉ mục đúng. Một chỉ mục thiếu làm truy vấn chậm gấp hàng trăm lần, không tham số nào bù được. Tìm bằng EXPLAIN ANALYZE và bảng pg_stat_user_tables.
2. Truy vấn gọn. Bỏ lấy toàn bộ cột, bỏ truy vấn lồng nhiều tầng không cần, gộp các truy vấn lặp trong vòng lặp.
3. Tham số. Phần bài này, cho thêm 20 tới 50% khi hai bước trên đã đúng.
4. Phần cứng. Chỉ nâng khi ba bước trên đã làm và vẫn chạm trần.

Câu hỏi thường gặp

Có công cụ tự sinh cấu hình không?

PGTune (web) sinh bộ số khởi điểm tốt theo RAM/loại ổ, dùng làm nháp rồi hiểu từng dòng qua bài này để chỉnh tiếp theo tải thật của bạn. Đừng dán mù cấu hình máy người khác.

Chỉnh tham số có rủi ro làm hỏng dữ liệu không?

Các tham số trong bài là hiệu năng, không đụng độ an toàn dữ liệu (không tắt fsync, không synchronous_commit off). Rủi ro thật duy nhất là work_mem quá tay gây OOM, tăng từ từ và quan sát RAM.

Bao lâu nên xem lại cấu hình một lần?

Khi một trong ba thứ đổi: RAM máy, cỡ dữ liệu (x5-x10), hoặc pattern truy cập (thêm tính năng nặng report). Bình thường một bộ số tốt sống êm cả năm.

pgvector cho AI có cần chỉnh gì thêm không?

Nền tảng vẫn là bài này (đặc biệt maintenance_work_mem lớn khi build index vector). Tìm kiếm vector đọc ngẫu nhiên dày đặc, ổ NVMe và RAM đủ chứa index quyết định trải nghiệm nhiều hơn mọi tham số.

Bài viết liên quan

• Cài PostgreSQL trên VPS Ubuntu chuẩn production
• Postgres với pgvector: tìm kiếm vector cần IOPS
• VPS ổ Enterprise U.2 NVMe RAID 10 tại TND