Debugging the Postgres query planner

GoCardless · 2019 · 2. Tăng trưởng · Độ khó: Khó — bài gốc

Bối cảnh

• Năm 2019, DB Postgres chính của GoCardless khoảng 2 TB. Bảng payment_transitions (mỗi lần payment đổi trạng thái là một dòng, mô hình state machine bằng Statesman) có gần nửa tỉ dòng.
• Một ngày, cả worker bất đồng bộ lẫn API đều chậm. Các query chậm đều chạm bảng này; nhiều query thường dưới 50 ms nay chạy rất lâu.

Thay đổi chính

• Query mẫu: lấy một transition theo payout_id, ORDER BY id, LIMIT 1, join payments. Có index trên payout_id.
• Hình dạng dữ liệu: khoảng 20% transition có payout_id, mỗi payout khoảng 20 transition; với 350 triệu dòng thì có khoảng 3,5 triệu payout_id khác nhau.
• Plan bị chọn: nested loop đi theo primary key (đã đúng thứ tự id), lọc payout_id, hy vọng gặp dòng khớp sớm rồi dừng. Vì payout mới có id nằm ở cuối, thực tế phải quét khoảng 17,5 triệu dòng (khoảng 20 GB), còn đẩy dữ liệu nóng khỏi page cache.
• Vì sao planner chọn vậy: pg_stats báo n_distinct = 25.650 cho payout_id thay vì khoảng 3,5 triệu (sai hai bậc), nên planner ước mỗi payout có khoảng 2.600 dòng thay vì 20. Trong code planner (create_limit_path), plan đã đúng thứ tự được giảm chi phí theo tỉ lệ LIMIT / số dòng ước tính; ước tính càng lớn thì plan quét PK trông càng rẻ.
• Plan lẽ ra nên có: dùng index payout_id lấy khoảng 20 dòng, sort trong bộ nhớ, lấy dòng đầu.
• Xử lý tức thời: chạy lại ANALYZE vài lần cho tới khi plan tốt lên. Phải chạy nhiều lần là dấu hiệu của vấn đề sâu hơn.
• Nguyên nhân gốc: lấy mẫu bị lệch.
  • Bộ ước lượng n_distinct (Haas–Stokes) luôn ước thiếu khi mẫu nhỏ. Tính theo số dòng tối đa của một payout (tới 100 nghìn) và 80% null thì cần target khoảng 1.666; thử 1.666 vẫn dao động, tới 5.000 mới ổn định.
  • ANALYZE lấy mẫu hai bước: chọn page trước rồi chọn dòng trong các page đó. Các transition của cùng một payout được ghi cùng lúc nên nằm cạnh nhau trên đĩa (bảng gần như chỉ thêm), nên mẫu theo page gặp lại cùng một giá trị nhiều lần. Thử TABLESAMPLE system (giống ANALYZE) ra 11.029 giá trị khác nhau, TABLESAMPLE bernoulli cùng cỡ mẫu ra 25.351.
• Hành động: tăng statistics target cho cột này; đồng thời đẩy việc thiết kế truy cập dữ liệu cẩn thận hơn để query đơn giản, khó bị planner chọn sai.

Vì sao không đặt target tối đa

• ANALYZE lâu hơn; autovacuum chạy ANALYZE liên tục và khoá ACCESS SHARE có thể chặn ALTER TABLE (xem bài lock timeout của GoCardless).
• Nâng major version Postgres xoá hết thống kê; phải ANALYZE toàn bộ trước khi nhận traffic, nên ANALYZE càng lâu thì downtime càng dài.
• Theo GoCardless, ngoài n_distinct thì planner chọn tốt với mẫu mặc định; dữ liệu đa dạng hơn nên tách bảng (partition) thay vì vặn thống kê.

Bài học cho SWE

• Một plan có ORDER BY ... LIMIT có thể lật hiệu năng chỉ vì một con số thống kê sai; query nhanh hôm qua có thể chậm hôm nay mà code không đổi (Query planner và hiệu năng truy vấn).
• Giả định "phân bố đều" của planner gãy khi dữ liệu có tương quan với thứ tự vật lý (ID tăng dần, ghi theo lô).
• Thống kê là đầu vào vận hành như cấu hình: cần theo dõi, có ảnh hưởng tới upgrade và migration schema (Migration không downtime).
• Cách đọc: hiểu query → biết vì sao plan hiện tại tệ → hình dung plan lý tưởng → tìm vì sao planner không chọn nó. Bài này là một sự cố được viết thành bài học kỹ thuật (Sự cố và postmortem).

Liên quan

• GoCardless 2015 · GoCardless 2017 · GoCardless 2020
• Render 2024 · Query planner và hiệu năng truy vấn · Sự cố và postmortem