work_mem

"Truy vấn tràn ra đĩa thì phải tăng work_mem" là lời khuyên xuất hiện trong mọi bài hướng dẫn tinh chỉnh PostgreSQL. Phần này đo nó trên ba loại thao tác, và kết quả đi ngược lại ở cả ba.

Sắp xếp ba triệu dòng

Nhanh nhất là 4 MB, tức cấu hình có ghi tệp tạm. Cấp 256 MB để mọi thứ chạy trong bộ nhớ thì chậm hơn 36% và tốn thêm 175 MB RAM.

Gộp nhóm 200.000 nhóm

Cùng hình dạng:

Cấu hình ghi ra đĩa 31 MB lại nhanh hơn cấu hình không ghi gì.

Đây là lần thứ ba trong sê-ri này cùng một kết quả xuất hiện. Phần 29 đo Hash Join: work_mem 1 MB với 16 lô băm nhanh hơn 16 MB với 1 lô. Phần 32 đo HashAggregate: gấp 64 lần bộ nhớ chỉ nhanh hơn 3%. Ba thao tác khác nhau, ba lần đo độc lập, cùng một kết luận.

Vì sao ghi tệp tạm lại rẻ

Buffers:      temp read=9193   written=21813
I/O Timings:  temp read=12.820  write=101.532

101,532 ms / 21.813 khối = 4,7 micro giây mỗi khối

Phần 44 đã đo rằng một lần đọc SSD thật mất ít nhất 100 micro giây. 4,7 micro giây là tốc độ bộ nhớ.

Tệp tạm được ghi vào bộ đệm trang của hệ điều hành, và với dữ liệu tạm sống vài trăm mili giây thì nó gần như không bao giờ được đẩy xuống đĩa vật lý. Máy chủ có ghi log đàng hoàng:

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp268.1", size 15572992
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp268.0", size 31719424

Tệp có thật, 15 MB và 31 MB. Chúng chỉ không chạm đĩa.

Còn lý do phía ngược lại — vì sao chạy hết trong bộ nhớ lại chậm hơn — là tính cục bộ của bộ nhớ đệm CPU. Quicksort trên một vùng 192 MB nhảy khắp nơi và trượt cache L2/L3 liên tục. Trộn ngoài thì làm việc trên từng khối nhỏ vừa cache, và đọc ghi tuần tự.

work_mem là cho mỗi nút

Đây là chỗ nguy hiểm nhất và hay bị hiểu sai nhất.

Một truy vấn có ba nút Sort, với work_mem = 64MB:

Sort      49.153 kB
Sort      49.153 kB
Sort      49.153 kB
TỔNG     147.459 kB

Gấp 2,3 lần con số đã đặt, cho một truy vấn.

Và phép nhân chưa dừng ở đó:

bộ nhớ tối đa  ≈  work_mem  ×  số nút cần bộ nhớ  ×  số tiến trình song song  ×  số kết nối

Với work_mem = 256MB, một truy vấn ba nút chạy hai tiến trình phụ, và 20 kết nối cùng chạy loại truy vấn đó:

256 MB × 3 × 3 × 20 = 46 GB

Con số đó không phải lý thuyết suông — nó là cách một máy chủ có 32 GB RAM bị OOM killer giết trong lúc mọi biểu đồ theo dõi đều bình thường.

Đây là lý do work_mem mặc định chỉ 4 MB, và là lý do bạn không nên đổi giá trị toàn cục lên hàng trăm megabyte.

LIMIT rẻ hơn mọi cách chỉnh work_mem

Trước khi làm phép đo chính, tôi thử select * from t order by v limit 100 và mọi mức work_mem đều cho ra 46 kB:

Memory: 46kB   Sort Method: top-N heapsort

Có LIMIT, PostgreSQL không sắp xếp cả ba triệu dòng — nó giữ một đống 100 phần tử và đi qua dữ liệu một lần. Bộ nhớ cần là kích thước của LIMIT, không phải kích thước của bảng.

Nếu truy vấn của bạn đang tràn ra đĩa khi sắp xếp, câu hỏi đầu tiên nên là "có thật sự cần cả ba triệu dòng không", chứ không phải "cấp thêm bao nhiêu bộ nhớ".

Cách đặt cho đúng

Đừng đổi giá trị toàn cục. Đặt riêng ở nơi cần:

-- cho một vai, ví dụ vai chạy báo cáo
alter role bao_cao set work_mem = '256MB';

-- cho một phiên
set work_mem = '256MB';

-- cho một giao dịch, tự trả lại khi kết thúc
begin;
set local work_mem = '256MB';
select ...;
commit;

Dạng set local là an toàn nhất: nó tự khôi phục khi giao dịch kết thúc, kể cả khi có lỗi. Với PgBouncer ở chế độ transaction, đây cũng là dạng duy nhất không rò rỉ sang client khác — phần 43 đã đo chính xác hiện tượng đó.

Khi nào tăng work_mem thật sự giúp

Số đo ở trên không có nghĩa là work_mem vô dụng. Nó có ích ở hai chỗ:

Khi tệp tạm lớn tới mức đĩa thật vào cuộc. Nếu bạn thấy temp written hàng gigabyte và I/O Timings: temp write cho ra hơn 100 micro giây mỗi khối, lúc đó dữ liệu đang thật sự xuống đĩa và tăng work_mem sẽ giúp.

Khi máy chủ ít kết nối và nhiều RAM. Kho dữ liệu chạy vài truy vấn lớn một lúc thì phép nhân ở trên không đáng sợ, và work_mem vài trăm megabyte là hợp lý. Ứng dụng web với 100 kết nối thì ngược lại.

Điểm chung: cả hai đều là kết luận rút ra từ số đo, không phải từ việc thấy chữ "external merge" trong kế hoạch.

Chẩn đoán

Bật ghi log tệp tạm:

alter system set log_temp_files = 0;   -- ghi mọi tệp tạm
select pg_reload_conf();

Rồi tìm truy vấn nào sinh tệp tạm lớn nhất:

select queryid, calls,
       pg_size_pretty(temp_blks_written * 8192) as tam_ghi,
       round(temp_blk_write_time::numeric) as thoi_gian_ghi_ms,
       round(temp_blk_write_time::numeric / nullif(temp_blks_written, 0) * 1000, 1) as micro_giay_moi_khoi
from pg_stat_statements
where temp_blks_written > 0
order by temp_blks_written desc limit 10;

Cột cuối là cột quyết định. Dưới 10 micro giây mỗi khối nghĩa là bộ đệm hệ điều hành đang lo — đừng đụng vào. Trên 100 thì mới đáng cân nhắc.

Thử ba mươi giây

show work_mem;
select count(*) from pg_stat_statements where temp_blks_written > 0;

Nếu work_mem toàn cục của bạn lớn hơn 64 MB, hãy tính thử phép nhân ở trên với số kết nối tối đa. Nếu kết quả vượt quá RAM của máy, bạn đang dựa vào việc không bao giờ có đủ truy vấn nặng chạy cùng lúc — và đó không phải một cấu hình, đó là một hy vọng.

Phần sau đo WAL: nó sinh ra bao nhiêu dữ liệu cho mỗi thao tác, và checkpoint làm máy chủ dừng lại như thế nào.