PostgreSQL Stories: A simple query with a big problem

Render · 2024 · 2. Tăng trưởng · Độ khó: Trung bình — bài gốc

Phần 1 của câu chuyện hai phần. Phần này tìm ra query gây tắc và gỡ tắc; phần 2 (Render 2024) giải thích vì sao query chậm. Đây là DB nội bộ ở staging của Render, không phải sản phẩm Postgres cho khách.

Bối cảnh

• Tháng 2/2024, pipeline deploy staging của Render bị chặn hàng giờ. Production chỉ được deploy sau khi build đã qua staging, nên tắc staging là tắc luôn production.
• Hai dấu hiệu đầu tiên: bước migration DB treo ở cả năm lần deploy gần nhất (chỉ có timeout, không có log lỗi); từ khoảng 8:30 sáng, bộ nhớ trống của DB giảm từ 20% về gần 0, CPU tăng gấp đôi. Staging ít người dùng và tải ổn định, nên giả thuyết đầu là một thay đổi code gây chậm.

Thay đổi chính (quá trình điều tra)

• Thu hẹp phạm vi: thao tác cơ bản trên staging vẫn chạy; lỗi lặp lại đúng chỗ (luôn là bước migration). Trên dashboard, biểu đồ tuổi của transaction mở lâu nhất tăng từ vài giây lên hơn 4 giờ.
• Tìm query bằng pg_stat_activity: lọc các query active quá 5 phút. Query lâu nhất bắt đầu khoảng 8:40, khớp lúc metric xấu đi. Nó trông vô hại: một join đơn giản, có index hai bên, có LIMIT 1. Wait event cho thấy nó đang đọc đĩa, tức là đang làm việc (rất chậm), không phải đang bị chặn.
• Chứng minh nó chặn migration bằng pg_blocking_pids(): query đó chặn hai câu CREATE OR REPLACE VIEW, câu này lại chặn câu kia. CREATE OR REPLACE VIEW cần khoá độc quyền trên view, mà query kia đang đọc bảng postgres_dbs. Mỗi lần deploy thử lại sinh thêm một câu tạo view chờ khoá, nên chúng chồng lên nhau.
• Tìm ai chạy query: code dùng GORM nên khó grep theo text query. Lẽ ra nên lấy ngay cột usename và application_name: kết quả cho thấy query được chạy từ psql bởi một engineer, để kiểm tra dữ liệu cho một tính năng. Người đó bấm Ctrl+C rồi đi làm việc khác; theo Render, việc đó chỉ dừng phía client đọc kết quả, còn backend trên server vẫn chạy query hơn 4 giờ (nhận định của Render, xem Câu hỏi mở ở Query planner và hiệu năng truy vấn).
• Gỡ tắc: kill query đó; migration qua ngay, metric về bình thường. Câu hỏi còn lại (vì sao query đơn giản lại chậm) để sang phần 2.

Vì sao một transaction mở lâu nguy hiểm (theo Render)

• Giữ khoá bảng/dòng, chặn query khác và thay đổi schema cần khoá độc quyền.
• Cản autovacuum và CREATE INDEX CONCURRENTLY, nên mọi query khác chậm dần vì phải quét dòng cũ.
• Chặn vacuum quá lâu có thể dẫn tới transaction ID wraparound: Postgres tự tắt để tránh mất dữ liệu, phải can thiệp tay.

Bài học cho SWE

• Tuổi transaction mở lâu nhất là metric nên có trên dashboard của mọi DB Postgres; nó chỉ thẳng vào loại sự cố này (Observability và monitoring).
• Migration treo không có log lỗi thường là chờ khoá. pg_blocking_pids() cho thấy ai chặn ai; đặt lock_timeout cho bước migration để nó lỗi rõ ràng thay vì treo.
• Đặt application_name trong connection string của mọi client để biết query đến từ đâu; với ORM, đây là cách gần như duy nhất để truy ngược.
• Query ad-hoc của người trên DB dùng chung (kể cả staging) có thể chặn deploy. Nên có statement_timeout cho người dùng tương tác, và biết rằng huỷ phía client không chắc đã huỷ phía server.
• Gỡ tắc trước (kill), tìm gốc sau: phần 2 mới là phần sửa để không lặp lại (Query planner và hiệu năng truy vấn).

Liên quan

• Phần 2: Render 2024 · cùng công ty: Render 2024
• Query planner và hiệu năng truy vấn · Sự cố và postmortem · Migration không downtime