Hiện trường sự cố: Database nghẽn cứng vì một query lạc nhịp
9 giờ sáng thứ Hai, kênh Slack trực chiến réo dồn dập. Trang quản lý đơn hàng xoay vòng tròn mất hơn 10 giây mới phản hồi. Khách sốt ruột bấm F5 liên tục. Kết quả: CPU cụm database chạm đỉnh 98%, hàng trăm kết nối rơi vào trạng thái chờ.
Bản chất database vận hành khá giống một thư viện chứa hàng triệu đầu sách. Khi bạn chạy lệnh: “Lấy toàn bộ đơn thành công của khách hàng VIP trong tháng trước”, Postgres phải chọn cách tìm kiếm. Có sẵn mục lục chuẩn, thủ thư chỉ mất 2 giây tới thẳng giá sách. Không có mục lục, thủ thư buộc phải lật giở từng trang của cả triệu cuốn sách. Hàng dài người phía sau lập tức bị tắc nghẽn.
Phản xạ quen thuộc của nhiều team khi thấy database quá tải là khởi động lại service hoặc nâng cấu hình server. Nhưng nâng RAM hay thêm CPU chỉ dời cơn đau sang tuần sau. Gốc rễ vấn đề vẫn nằm ở câu SQL đang chạy thiếu hiệu quả.
Giải phẫu hộp đen: Postgres thực thi câu lệnh như thế nào?
PostgreSQL dùng bộ Query Planner để chọn đường đi tối ưu nhất cho mỗi câu lệnh. Bộ lập kế hoạch này dựa vào bảng thống kê (statistics) sẵn có để ước lượng chi phí. Tuy nhiên, tính toán trên giấy tờ và thực tế trên đĩa cứng đôi khi lệch nhau rất xa.
Để biết chính xác database làm gì bên dưới, lập trình viên dùng hai công cụ chính:
- EXPLAIN: Chỉ đưa ra phán đoán lý thuyết. Database không chạy query thật mà chỉ ước tính chi phí dựa trên metadata hiện có.
- EXPLAIN ANALYZE: Cho câu lệnh chạy thật trên dữ liệu sống. Database bấm giờ chính xác từng công đoạn và đếm số dòng thực tế được xử lý.
Để chuẩn bị bộ dữ liệu test 1 triệu đơn hàng phục vụ bài viết, mình thường xuất dữ liệu mô phỏng rồi dùng tool convert nhanh CSV sang JSON tại toolcraft.app/vi/tools/data/csv-to-json — chạy client-side ngay trên trình duyệt nên không lo lộ dữ liệu nội bộ. Dưới đây là câu truy vấn tìm đơn hàng phát sinh vấn đề:
EXPLAIN ANALYZE
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE customer_id = 98452 AND status = 'COMPLETED';
Kết quả trả về từ Postgres:
Seq Scan on orders (cost=0.00..21458.00 rows=3 width=40) (actual time=48.215..95.120 rows=2 loops=1)
Filter: ((customer_id = 98452) AND ((status)::text = 'COMPLETED'::text))
Rows Removed by Filter: 999998
Planning Time: 0.124 ms
Execution Time: 95.160 ms
Những chỉ số quan trọng cần lưu ý:
- Seq Scan (Sequential Scan): Quét tuần tự toàn bộ bảng. Database phải duyệt đủ 1.000.000 dòng trên ổ đĩa chỉ để lấy đúng 2 dòng thỏa mãn (Rows Removed by Filter: 999.998).
- Cost (0.00..21458.00): Đơn vị chi phí ước tính của planner. Số 0.00 là chi phí khởi động để lấy dòng đầu tiên, 21458.00 là tổng chi phí để hoàn tất toàn bộ node quét.
- Actual time (48.215..95.120 ms): Thời gian thực thi thực tế. Một query đơn lẻ đã nuốt mất 95.16 ms. Khi có 200 request đồng thời, hàng đợi kết nối sẽ nghẽn ngay lập tức.
Những cách chữa cháy thường thấy và hệ lụy
Khi gặp query chậm, các đội ngũ thường vội vã chọn giải pháp ngắn hạn:
1. Nâng cấp cấu hình server (Scale Up)
Tăng gấp đôi RAM từ 16GB lên 32GB giúp server chịu đòn tốt hơn vài tuần. Nhưng khi bảng dữ liệu chạm mốc 10 triệu dòng, tình trạng nghẽn I/O sẽ quay trở lại. Bạn tốn thêm vài trăm USD tiền cloud mỗi tháng mà không dứt điểm được lỗi.
2. Đánh index vô tội vạ
Thấy cột nào nằm trong mệnh đề WHERE cũng tạo index riêng là thói quen nguy hiểm. Index giúp đọc nhanh nhưng kéo tụt tốc độ ghi (INSERT, UPDATE, DELETE). Mỗi dòng ghi mới buộc Postgres phải cập nhật lại toàn bộ cây B-Tree tương ứng, vừa tốn RAM vừa phình dung lượng lưu trữ.
3. Viết lại SQL theo cảm tính
Đổi thứ tự điều kiện trong WHERE hoặc tách câu query thành nhiều query con dựa vào suy đoán thường không đem lại hiệu quả. Bộ tối ưu hóa của Postgres đủ thông minh để tự sắp xếp thứ tự logic. Sửa mù quáng chỉ làm code rối thêm.
Quy trình 4 bước tối ưu dựa trên số liệu thực nghiệm
Thay vì đoán mò, chúng ta bám sát các chỉ số từ Execution Plan để xử lý triệt để:
Bước 1: Bật cờ BUFFERS để soi dung lượng đọc đĩa
Thêm tùy chọn BUFFERS vào câu lệnh giúp xác định rõ dữ liệu được đọc từ bộ đệm RAM (shared hit) hay phải kéo từ đĩa cứng (read):
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE customer_id = 98452 AND status = 'COMPLETED';
Seq Scan on orders (cost=0.00..21458.00 rows=3 width=40) (actual time=48.215..95.120 rows=2 loops=1)
Filter: ((customer_id = 98452) AND ((status)::text = 'COMPLETED'::text))
Rows Removed by Filter: 999998
Buffers: shared hit=124 read=8802
Planning Time: 0.145 ms
Execution Time: 95.210 ms
Chỉ số shared read=8802 tương đương khoảng 68.7 MB dữ liệu (8802 blocks x 8KB) bị lôi trực tiếp từ ổ cứng lên. Đây chính là lý do khiến IOPS tăng vọt và query bị treo.
Bước 2: Tạo Composite Index đúng thứ tự chọn lọc (Cardinality)
Điều kiện lọc gồm cả customer_id và status. Cột customer_id có độ phân tán dữ liệu rất cao (hàng chục nghìn khách), trong khi status chỉ có vài trạng thái cố định. Đặt cột có độ chọn lọc cao lên trước trong B-Tree Index là quy tắc bắt buộc:
-- Đặt customer_id lên đầu index để thu hẹp vùng tìm kiếm nhanh nhất
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
Bước 3: Cập nhật lại số liệu thống kê cho bảng
Sau khi tạo index hoặc khi bảng vừa trải qua đợt biến động dữ liệu lớn, hãy chủ động nhắc Postgres cập nhật catalog thống kê:
ANALYZE orders;
Bước 4: Đối chiếu kết quả sau khi tối ưu
Chạy lại câu lệnh phân tích để kiểm tra đường đi mới của Postgres:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE customer_id = 98452 AND status = 'COMPLETED';
Execution Plan mới xuất hiện:
Index Scan using idx_orders_customer_status on orders (cost=0.42..8.45 rows=2 width=40) (actual time=0.038..0.042 rows=2 loops=1)
Index Cond: ((customer_id = 98452) AND ((status)::text = 'COMPLETED'::text))
Buffers: shared hit=4
Planning Time: 0.115 ms
Execution Time: 0.065 ms
Sự cải thiện thể hiện rõ qua các con số:
- Index Scan thay thế Seq Scan: Postgres nhảy thẳng vào cây chỉ mục, trích xuất chính xác 2 dòng mà không cần duyệt qua 999.998 dòng thừa.
- Buffers chỉ tốn 4 shared hit: Toàn bộ dữ liệu nằm gọn trong cache RAM (khoảng 32KB). Không phát sinh bất kỳ lượt đọc đĩa vật lý nào (read=0).
- Thời gian chạy: Rút ngắn từ 95.160 ms xuống còn 0.065 ms, nhanh hơn xấp xỉ 1.460 lần.
Dùng EXPLAIN ANALYZE giúp bạn ra quyết định tối ưu dựa trên dữ liệu định lượng. Bắt đúng bệnh, kê đúng index vừa tiết kiệm chi phí hạ tầng, vừa giữ hệ thống ổn định khi dữ liệu tăng trưởng.
