Sau shared_buffers (bộ đệm chung), tham số bộ nhớ quan trọng thứ hai là work_mem — và nó hoạt động theo cách hoàn toàn khác. Trong khi shared_buffers là một vùng RAM chung cấp phát một lần, work_mem là lượng RAM tối đa cho mỗi thao tác sort hoặc hash. Đặt đúng nó quyết định một truy vấn sắp xếp trong RAM (nhanh) hay tràn ra đĩa (chậm). Nhưng đặt sai theo hướng ngược lại — quá lớn — có thể làm cạn kiệt bộ nhớ máy chủ, vì một lý do tinh vi: nó nhân lên theo số kết nối và số thao tác. Bài này đo thật cả hai mặt.
work_mem quyết định sort/hash trong RAM hay tràn đĩa
work_mem (mặc định 4MB) là RAM tối đa một thao tác sort hoặc hash được dùng trước khi phải tràn ra đĩa. Nếu dữ liệu vừa work_mem, thao tác làm trong RAM (nhanh); nếu vượt, PostgreSQL chuyển sang thuật toán dựa đĩa (chậm).
SHOW work_mem; -- mặc định 4MB (thường quá nhỏ cho analytics)
Đo thật sort 2 triệu dòng với hai giá trị work_mem:
Hình 1: work_mem là RAM cho mỗi thao tác sort/hash — đủ thì làm trong RAM, thiếu thì tràn đĩa. Nhưng nó là per-operation per-connection nên tổng có thể rất lớn.
Đo thật: tràn đĩa với work_mem nhỏ
Hình 2: Sort 2 triệu dòng: work_mem 4MB dùng external merge tràn 35 MB ra đĩa (584 ms), work_mem 256MB dùng quicksort trong RAM 112 MB (471 ms). Hash join: 4MB chia 16 batch, 512MB gọn 1 batch.
- SORT: với
work_mem=4MB, sort 2 triệu dòng dùngexternal merge — Disk: 35 MB(ghi 35 MB ra đĩa rồi trộn), 584 ms. Vớiwork_mem=256MB, dùngquicksort — Memory: 112 MB(hoàn toàn trong RAM), 471 ms — nhanh hơn và không đụng đĩa. - HASH JOIN: với
work_mem=4MB, bảng băm không vừa nên chia thành 16 batch (ghi/đọc đĩa giữa các batch). Vớiwork_mem=512MB, gọn trong 1 batch (bảng băm 86 MB vừa RAM).
Dấu hiệu trong EXPLAIN ANALYZE: Sort Method: external merge Disk: ... và Batches: >1 báo thao tác đang tràn đĩa vì thiếu work_mem. Đây là những cờ đỏ đáng theo dõi cho truy vấn analytics chậm.
Cạm bẫy: work_mem là per-operation per-connection
Đây là lý do bạn không thể chỉ đặt work_mem thật lớn toàn cục. Không như shared_buffers (một vùng chung), work_mem được cấp cho mỗi thao tác sort/hash, trong mỗi truy vấn, của mỗi kết nối — độc lập. Một truy vấn phức tạp có thể có nhiều sort và hash cùng lúc, mỗi cái lấy tới work_mem.
Làm phép tính tệ nhất: 100 kết nối × 256MB work_mem × 3 thao tác sort mỗi truy vấn = 75 GB RAM. Nếu máy chỉ có 32 GB, đây là con đường thẳng tới hết bộ nhớ (OOM) và PostgreSQL bị hệ điều hành giết. Đó là lý do work_mem mặc định chỉ 4MB — an toàn cho nhiều kết nối.
Cách đặt an toàn
-- toàn cục: khiêm tốn, tính theo số kết nối
-- ví dụ thô: (RAM khả dụng cho work_mem) / (max_connections × ~2-3)
-- per-query: nâng cao cho một truy vấn nặng cụ thể
SET work_mem = '512MB';
SELECT ...báo cáo phân tích lớn...;
RESET work_mem;
Chiến lược đúng: đặt work_mem toàn cục thấp (vài MB tới vài chục MB, đủ cho truy vấn OLTP thông thường), rồi nâng per-query hoặc per-role cho các truy vấn analytics nặng cần sort/hash lớn. Cách này cho truy vấn nặng đủ RAM mà không cho phép 100 kết nối cùng ngốn.
Đánh đổi cần cân nhắc
Tràn đĩa không phải luôn thảm họa. Với sort/hash thỉnh thoảng trên dữ liệu lớn, external merge chậm hơn nhưng vẫn chạy được — không cần work_mem đủ cho mọi truy vấn. Chỉ nâng work_mem khi một truy vấn thường xuyên và quan trọng bị chậm vì tràn đĩa. Đo bằng EXPLAIN ANALYZE (tìm Disk: và Batches).
PostgreSQL 13+ có hashed aggregate spill an toàn hơn. Trước PG13, HashAggregate có thể vượt work_mem mà không tràn đĩa (nguy cơ OOM). Từ PG13 nó tràn đĩa đúng cách như hash join. Nhưng vẫn nên đặt work_mem hợp lý — tràn đĩa vẫn chậm.
Kết nối nhiều thì work_mem phải thấp — hoặc dùng connection pooler. Nếu ứng dụng mở hàng trăm kết nối trực tiếp, work_mem buộc phải rất thấp để an toàn. Một connection pooler (PgBouncer) giảm số kết nối thật tới PostgreSQL, cho phép work_mem cao hơn an toàn — chủ đề đáng cân nhắc cho hệ tải cao.
Ba ý mang về
- work_mem quyết định sort/hash trong RAM hay tràn đĩa: đo thật, sort 2 triệu dòng với work_mem 4MB dùng external merge tràn 35 MB ra đĩa (584 ms) còn 256MB dùng quicksort trong RAM (471 ms); hash join thiếu work_mem chia 16 batch, đủ thì 1 batch.
- work_mem là per-operation per-connection — đặt quá lớn có thể hết bộ nhớ: 100 kết nối × 256MB × 3 sort/truy vấn = 75 GB RAM tối đa; đây là lý do mặc định chỉ 4MB.
- Đặt toàn cục khiêm tốn, nâng per-query cho truy vấn nặng:
SET work_mem='512MB'chỉ cho truy vấn analytics cần nó, thay vì cho mọi kết nối — và dùng connection pooler nếu có nhiều kết nối để nâng work_mem an toàn.
Phần sau ta xét một tham số bộ nhớ chuyên biệt cho bảo trì: Phần sau mổ xẻ maintenance_work_mem — RAM cho VACUUM, CREATE INDEX và REINDEX, vì sao nó tách khỏi work_mem, và đặt cao hơn được vì ít thao tác đồng thời.
