Sắp xếp 3 triệu dòng nhanh nhất ở work_mem 4 MB — cấp 256 MB để chạy hết trong RAM lại chậm hơn 36%

Đưa cho ai đó một cái bàn làm việc thật to rồi đổ hết mọi hồ sơ lên đó không khiến họ làm nhanh hơn — họ đi tới đi lui khắp mặt bàn để lục. Một cái bàn nhỏ gọn, chỉ đặt vài hồ sơ đang cần, phần còn lại nằm trong ngăn kéo và lấy ra theo thứ tự, lại nhanh hơn. Đây là chuyện xảy ra bên trong CPU, và không biết nó là lý do "cấp thêm bộ nhớ" thường làm mọi thứ chậm đi. "Truy vấn tràn ra đĩa thì phải tăng work_mem" là lời khuyên có 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.

Mẫu số chung

Việc 4 MB thắng 256 MB không phải một điều kỳ quặc của PostgreSQL — nó chỉ ra một sự thật mà dân tối ưu hay quên: tốc độ do phân cấp bộ nhớ quyết định, không do bạn có bao nhiêu bộ nhớ. Một phép tính nhét vừa tập làm việc vào cache và chạm bộ nhớ tuần tự sẽ thắng một phép tính được cấp RAM dư dả nhưng nhảy lung tung trong đó — nên "cấp thêm bộ nhớ" thường làm chậm đi, vì dung lượng chưa bao giờ là nút thắt; tính cục bộ mới là. Nước đi thắng là thu nhỏ tập làm việc về cỡ cache (chia khối, phát dòng, LIMIT), chứ không phải phình bộ đệm ra.

Đúng "sự đồng cảm cơ khí" (mechanical sympathy) này chi phối mọi ngôn ngữ:

• Trong Java, duyệt một mảng liền khối nhanh hơn hẳn LinkedList cùng số phần tử — không phải vì độ phức tạp khác, mà vì mảng đọc tuần tự và vừa cache, còn linked list phải đuổi theo con trỏ trượt cache mỗi bước. Cấp cho JVM một heap khổng lồ cũng không làm việc tính-nặng nhanh lên, đôi khi còn chậm vì áp lực cache và TLB.
• Trong Go, một slice liền khối thắng một cấu trúc đầy con trỏ; và người ta chọn "struct of arrays" thay vì "array of structs" đúng để giữ thứ đang quét nằm liền nhau trong cache.
• Ở tầng giải thuật, đây là lý do nhân ma trận theo khối (tiling) đánh bại bản ngây thơ dù làm nhiều việc sổ sách hơn: nó chia bài toán thành từng khối vừa cache. Trộn ngoài của PostgreSQL chính là một giải thuật chia khối như vậy.

Nên khi một thứ chạy chậm, câu hỏi đúng không phải "cấp thêm bộ nhớ được không" mà là "tập làm việc nóng của nó có vừa cache không, và nó có chạm bộ nhớ tuần tự không". Cấp thêm dung lượng chỉ giúp khi dung lượng đúng là thứ đang thiếu — mà đo lên thì thường không phải. Tự soát ngay:

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.