Cách sử dụng pg_stat_statements để theo dõi các truy vấn?

Question

Bùi Văn Thái · Accepted Answer

Để theo dõi các truy vấn trong PostgreSQL, bạn có thể sử dụng pg_stat_statements, một tiện ích mở rộng cung cấp thông tin về hiệu suất và thống kê các câu lệnh SQL được thực thi trên cơ sở dữ liệu.
Các bước cài đặt và sử dụng pg_stat_statements:
1. Cài đặt tiện ích mở rộng
Trước tiên, cần kiểm tra xem tiện ích đã được cài đặt hay chưa. Để kích hoạt pg_stat_statements, bạn cần thêm nó vào cấu hình của PostgreSQL.
Mở file cấu hình PostgreSQL (postgresql.conf) và tìm dòng shared_preload_libraries, sau đó thêm pg_stat_statements vào đó.
shared_preload_libraries = 'pg_stat_statements'
Khởi động lại PostgreSQL để áp dụng thay đổi.
sudo systemctl restart postgresql
2. Tạo extension trong cơ sở dữ liệu
Kết nối với cơ sở dữ liệu mà bạn muốn theo dõi các truy vấn và tạo extension pg_stat_statements.
CREATE EXTENSION pg_stat_statements;
3. Truy vấn dữ liệu từ pg_stat_statements
Sau khi tiện ích mở rộng được cài đặt và kích hoạt, bạn có thể truy vấn bảng pg_stat_statements để lấy thông tin về các câu lệnh SQL đã chạy.
Ví dụ:
SELECT
    query,
    calls,
    total_time,
    rows,
    mean_time,
    min_time,
    max_time
FROM
    pg_stat_statements
ORDER BY
    total_time DESC
LIMIT 10;
query: Câu lệnh SQL được thực thi.
calls: Số lần câu lệnh được gọi.
total_time: Tổng thời gian thực thi câu lệnh (ms).
rows: Số dòng trả về bởi câu lệnh.
mean_time: Thời gian trung bình mỗi lần thực thi.
min_time: Thời gian thực thi nhanh nhất.
max_time: Thời gian thực thi chậm nhất.
4. Reset thống kê
Nếu muốn reset lại các thống kê của pg_stat_statements, bạn có thể sử dụng lệnh:
SELECT pg_stat_statements_reset();
5. Sử dụng để tối ưu hóa
Dựa trên dữ liệu thống kê, bạn có thể xác định được các truy vấn tốn nhiều thời gian nhất, từ đó tối ưu hóa bằng cách thêm chỉ mục, chỉnh sửa truy vấn, hoặc cải thiện cấu trúc cơ sở dữ liệu.
Tiện ích pg_stat_statements rất hữu ích trong việc theo dõi và tối ưu hóa hiệu suất truy vấn, giúp bạn dễ dàng phát hiện các vấn đề trong cơ sở dữ liệu của mình.