Phân tích truy vấn chậm với pg_stat_statements

Khi bạn làm việc với các dự án thực tế, có thể hàng ngàn user cùng lúc tương tác với app của bạn. Họ gửi truy vấn tới database, thêm dữ liệu, đọc, update... Và rồi bạn phát hiện server của mình bắt đầu “rên rỉ”. Đó là dấu hiệu cho thấy truy vấn của bạn chưa tối ưu tẹo nào. Đôi khi truy vấn mà “trên giấy” nhìn rất ổn, nhưng thực tế lại là thảm họa cho hiệu năng. Đây là lúc pg_stat_statements xuất hiện trên sân khấu.

pg_stat_statements cho phép bạn:

1. Theo dõi truy vấn chậm.
2. Biết được truy vấn nào được thực hiện bao nhiêu lần.
3. Biết chúng tốn bao nhiêu thời gian.
4. Xem thời gian trung bình thực thi truy vấn.
5. Không mắc sai lầm chí mạng và rewrite cả app!

Khám phá cấu trúc pg_stat_statements

Sau khi bật extension này, trong database của bạn sẽ xuất hiện một view đặc biệt là pg_stat_statements. Ở đây lưu tất cả dữ liệu về các truy vấn đã thực hiện. Đầu tiên, cùng xem trong đó có gì nhé:

SELECT * FROM pg_stat_statements LIMIT 1;

Kết quả có thể trông như này (bản rút gọn):

Giải thích nhanh:

• query — chính là truy vấn SQL.
• calls — truy vấn này đã chạy bao nhiêu lần.
• total_time — tổng thời gian truy vấn này đã tiêu tốn.
• rows — số dòng truy vấn trả về.
• shared_blks_read — số block đã đọc (sẽ truy cập disk nếu bạn không dùng cache).

Phân tích kết quả

Giờ khi pg_stat_statements đã bật, cùng xem cách tìm truy vấn chậm nhé.

Truy vấn chậm nhất

Để tìm truy vấn nào tốn nhiều thời gian nhất, bạn có thể dùng truy vấn sau:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Ở đây:

• mean_time — là thời gian trung bình thực thi một truy vấn (total_time / calls).
• ORDER BY total_time DESC — sắp xếp theo tổng thời gian giảm dần.

Truy vấn thực thi nhiều nhất

Đôi khi vấn đề không phải truy vấn chậm mà là truy vấn bị gọi quá nhiều lần. Ví dụ:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Tối ưu hóa truy vấn

1. Dùng indexing

Nếu bạn thấy truy vấn trên một số cột chạy chậm, hãy kiểm tra xem đã có index cho các cột đó chưa. Giả sử bạn có bảng students với rất nhiều dòng, và bạn hay truy vấn theo trường last_name. Nên tạo index như sau:

CREATE INDEX idx_students_last_name ON students (last_name);

2. Viết lại truy vấn

Giả sử bạn thấy truy vấn kiểu SELECT * FROM orders WHERE amount > 1000 chạy quá lâu. Có thể thay vì “lấy tất cả”, bạn chỉ nên lấy các cột cần thiết thôi:

SELECT order_id, amount FROM orders WHERE amount > 1000;

Xóa thống kê

Đôi khi, để xem kết quả mới (ví dụ sau khi tối ưu), bạn cần xóa dữ liệu trong pg_stat_statements. Làm như sau:

SELECT pg_stat_statements_reset();

Hoạt động như nút “Reset” trên máy tính bỏ túi của bạn. Sau khi chạy, thống kê sẽ được thu thập lại từ đầu.

Tìm truy vấn có vấn đề

Giả sử bạn là admin database cho trường đại học, và sinh viên kêu ca rằng trang cá nhân load quá lâu. Bạn quyết định kiểm tra pg_stat_statements:

Bước 1: Tìm truy vấn chậm nhất

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Bạn thấy truy vấn kiểu SELECT * FROM students WHERE status = 'active' mất 30 giây. Vãi. Phải xử lý ngay.

Bước 2: Kiểm tra indexing Sau khi phân tích bảng students, bạn nhận ra trường status chưa có index. Bạn fix ngay:

CREATE INDEX idx_students_status ON students (status);

Bước 3: Kiểm tra kết quả Sau khi tối ưu, bạn kiểm tra lại pg_stat_statements và thấy truy vấn chỉ còn 0.5 giây. Quá đỉnh!

Lỗi thường gặp khi dùng pg_stat_statements

Đôi khi admin mắc lỗi khi phân tích truy vấn:

1. Chưa bật extension. Nếu bạn quên thêm pg_stat_statements vào shared_preload_libraries, sẽ không có thống kê nào được thu thập.
2. Bỏ qua indexing. Dù truy vấn có vẻ chậm, đôi khi chỉ cần thêm index là xong.
3. Không reset thống kê. Nếu không chạy pg_stat_statements_reset(), dữ liệu cũ sẽ làm bạn khó phân tích hiện tại.

Dùng pg_stat_statements trong công việc giống như GPS cho database: nó chỉ cho bạn chính xác chỗ nào đang “kẹt xe”, thậm chí gợi ý đường vòng. Cài đặt đúng công cụ này, bạn sẽ cải thiện hiệu năng database của mình rõ rệt.