Khi database “nặng” mà không biết do đâu
PostgreSQL chạy chậm là tình huống quen thuộc: CPU lên 100%, response time tăng vọt, nhưng nhìn vào log thì chẳng thấy gì rõ ràng. Nếu bạn đã dùng EXPLAIN ANALYZE để phân tích từng query, bạn biết công cụ đó hiệu quả ra sao — nhưng vấn đề là phải biết query nào cần phân tích trước.
pg_stat_statements được sinh ra để giải quyết đúng bài toán này. Extension này theo dõi thống kê thực thi của tất cả câu lệnh SQL trong suốt thời gian database hoạt động — không phải sampling, không phải bật log rồi grep thủ công. Mọi query được tổng hợp theo template, và bạn có thể hỏi chính xác: query nào chiếm nhiều thời gian nhất? Query nào đọc nhiều block nhất? Query nào chạy hàng nghìn lần mỗi ngày với mean time đáng lo?
Khác với EXPLAIN ANALYZE — vốn chỉ phân tích một query cụ thể theo yêu cầu — pg_stat_statements chạy ngầm và tích lũy dữ liệu liên tục. Khi có sự cố, bạn đã có sẵn số liệu để điều tra, không cần tái hiện lại vấn đề.
Cài đặt extension
Extension này đi kèm sẵn với PostgreSQL, không cần cài thêm package bên ngoài. Quy trình gồm 3 bước bắt buộc theo thứ tự.
Bước 1: Khai báo trong shared_preload_libraries
Tìm file postgresql.conf:
sudo -u postgres psql -c "SHOW config_file;"
# Ubuntu/Debian thường ở: /etc/postgresql/15/main/postgresql.conf
# CentOS/RHEL thường ở: /var/lib/pgsql/15/data/postgresql.confMở file và thêm hoặc sửa dòng:
# Nếu chưa có library nào:
shared_preload_libraries = 'pg_stat_statements'
# Nếu đã có library khác, thêm vào — ngăn cách bằng dấu phẩy:
shared_preload_libraries = 'pg_stat_statements,ten_library_khac'Đây là bước bắt buộc — extension phải được load từ lúc PostgreSQL khởi động, không thể chỉ CREATE EXTENSION mà bỏ qua bước này.
Bước 2: Restart PostgreSQL
sudo systemctl restart postgresqlBước 3: Tạo extension trong database cần theo dõi
-- Kết nối vào database cần theo dõi
\c ten_database
CREATE EXTENSION pg_stat_statements;
-- Kiểm tra hoạt động
SELECT * FROM pg_stat_statements LIMIT 5;Nếu trả về dữ liệu (dù chỉ vài dòng) là extension đã hoạt động. Nếu báo lỗi “could not open file”, khả năng cao bạn bỏ qua bước restart ở bước 2.
Cấu hình chi tiết
Cài xong là dùng được ngay. Nhưng có vài tham số nên điều chỉnh trong postgresql.conf tùy nhu cầu:
# Số lượng query template tối đa được lưu (mặc định: 5000)
pg_stat_statements.max = 10000
# TOP: chỉ query gọi trực tiếp từ client (nhẹ hơn, đủ dùng cho hầu hết trường hợp)
# ALL: bao gồm cả nested query bên trong stored procedure/function
pg_stat_statements.track = top
# Bao gồm utility commands như VACUUM, ANALYZE, CREATE INDEX...
pg_stat_statements.track_utility = on
# Theo dõi thời gian planning riêng biệt (PostgreSQL 13+)
pg_stat_statements.track_planning = onSau khi sửa, reload config không cần restart:
sudo -u postgres psql -c "SELECT pg_reload_conf();"Mình thường để track = top trong production bình thường — nhẹ hơn và đủ để tìm ra hầu hết vấn đề. Chỉ chuyển sang all khi cần debug performance bên trong stored procedure cụ thể, rồi chuyển lại sau.
Về pg_stat_statements.max: khi đạt giới hạn, query ít gặp nhất bị đẩy ra khỏi thống kê — data thật không mất, chỉ mất record thống kê thôi. Nếu hệ thống có nhiều loại query (microservices với hàng trăm API endpoint chẳng hạn), nên tăng lên 10000–20000.
Kiểm tra và Monitoring
View pg_stat_statements có nhiều cột. Dưới đây là các query thực tế theo từng mục đích điều tra.
Top 10 query ngốn thời gian nhất (tổng cộng)
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_cpu,
left(query, 100) AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Query này là cái đầu tiên mình mở ra mỗi khi nhận báo cáo “database chậm”. Cột pct_cpu lộ ngay thủ phạm — thường top 3 query chiếm 80%+ tổng thời gian là bình thường với hệ thống production thực tế.
Top 10 query chậm nhất (trung bình mỗi lần gọi)
SELECT
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(total_exec_time::numeric, 2) AS total_ms,
left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;Lọc calls > 10 để tránh false alarm từ các query admin hay maintenance chỉ chạy 1–2 lần. Query chạy 1 lần mất 30 giây không nguy hiểm bằng query chạy 10.000 lần mất trung bình 50ms.
Query đọc nhiều block nhất — phát hiện vấn đề I/O
SELECT
calls,
shared_blks_hit,
shared_blks_read,
round(
shared_blks_hit * 100.0 /
nullif(shared_blks_hit + shared_blks_read, 0)
, 2) AS cache_hit_pct,
left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;cache_hit_pct dưới 90% là dấu hiệu query đang phải đọc nhiều từ disk thay vì shared buffer. Nguyên nhân thường gặp: thiếu index khiến phải sequential scan toàn bảng, hoặc shared_buffers quá nhỏ so với working set.
Query tốn nhiều thời gian planning (PostgreSQL 13+)
SELECT
calls,
round(mean_plan_time::numeric, 2) AS plan_ms,
round(mean_exec_time::numeric, 2) AS exec_ms,
left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE mean_plan_time > 1
ORDER BY mean_plan_time DESC
LIMIT 10;Plan time cao (trên vài millisecond) thường do query có nhiều JOIN phức tạp, hoặc table statistics cũ khiến planner phải tính toán nhiều hơn. Chạy ANALYZE ten_bang; để cập nhật statistics — nhiều trường hợp plan time giảm từ vài chục ms xuống dưới 1ms chỉ với lệnh này.
Reset thống kê khi cần đo lại
-- Reset toàn bộ (sau khi deploy fix hoặc thay đổi config)
SELECT pg_stat_statements_reset();
-- Reset chỉ một query cụ thể (PostgreSQL 14+)
SELECT pg_stat_statements_reset(0, 0, queryid);
-- queryid lấy từ cột queryid trong view pg_stat_statementsXem query đang chạy thực thời
Muốn biết ngay lúc này có gì đang chạy, kết hợp thêm pg_stat_activity:
SELECT
pid,
now() - query_start AS duration,
state,
left(query, 120) AS query_snippet
FROM pg_stat_activity
WHERE query_start IS NOT NULL
AND state != 'idle'
ORDER BY duration DESC;Nếu thấy query treo quá lâu và chắc chắn cần dừng:
SELECT pg_cancel_backend(pid); -- gửi cancel request (graceful, ưu tiên dùng cái này)
SELECT pg_terminate_backend(pid); -- force terminate nếu cancel không ănWorkflow thực tế khi điều tra performance
Quy trình mình dùng mỗi khi nhận báo cáo database chậm:
- Chạy query tổng thời gian → xác định top 3 query ngốn nhất
- Lấy query text đầy đủ → chạy
EXPLAIN (ANALYZE, BUFFERS)trên từng cái - Kiểm tra cache hit ratio → nếu thấp, xem lại index hoặc tăng
shared_buffers - Kiểm tra
pg_stat_activityxem có query nào đang bị block không - Reset thống kê → deploy fix → đo lại sau 24h để xác nhận cải thiện thực sự
Khi cần xử lý kết quả từ pg_stat_statements offline (export CSV từ psql rồi phân tích bằng script), mình hay dùng converter tại toolcraft.app/vi/tools/data/csv-to-json để chuyển sang JSON trước khi đưa vào Python — tiện là chạy hoàn toàn trên trình duyệt nên không lo data query log bị gửi ra ngoài.
Những điểm cần lưu ý khi vận hành
- Overhead thấp: khoảng 1–5% — đủ nhỏ để bật thường xuyên trong production
- Query được chuẩn hóa tự động: Giá trị literal được thay bằng
$1,$2… để gộp các query cùng template vào một record. Thiết kế này có chủ ý — 1000 lần gọiSELECT * FROM users WHERE id = $1với 1000 ID khác nhau vẫn chỉ là một dòng thống kê - Thống kê không tồn tại qua restart: Data không persist qua restart PostgreSQL. Nếu cần giữ lịch sử, export ra file trước khi bảo trì theo lịch
- Quyền truy cập: Superuser thấy tất cả query của mọi user. User thường chỉ thấy query của chính mình (PostgreSQL 14+)
