Posted inDatabase

Phân Tích Hiệu Năng PostgreSQL với pg_stat_statements: Tìm và Sửa Query Chậm

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Triệu Chứng Mà Không Ai Giải Thích Được

PostgreSQL của bạn đang chạy chậm. Response time tăng đột biến lúc 2 giờ sáng, một số API endpoint bị timeout khi có nhiều request, và log ứng dụng đầy lỗi timeout — nhưng không ai biết query nào thực sự là thủ phạm.

Đoán mò query nào cần tối ưu chỉ tốn thêm ngày công vô ích. Bạn cần dữ liệu, không phải cảm tính. Vậy mà hầu hết các team lại chọn nhầm công cụ ngay từ đầu.

pg_stat_statements là một extension có sẵn trong PostgreSQL, theo dõi thống kê thực thi của mọi query chạy qua database — tổng số lần gọi, tổng thời gian, thời gian trung bình, số hàng trả về, tỷ lệ cache hit. Hãy xem nó như hộp đen ghi lại mọi query. Extension này đã có mặt từ PostgreSQL phiên bản 9.2, và gần như chắc chắn đã được cài sẵn trên server của bạn.

So Sánh Các Phương Pháp: Cách Các Team Thường Debug Database Chậm

Trước khi đi vào cách cài đặt, hãy hiểu rõ các lựa chọn thay thế — vì mỗi cách đều có đánh đổi thực tế mà bạn sẽ gặp phải ở những thời điểm khác nhau.

Cách 1: Logging Ở Tầng Ứng Dụng

Bọc các lời gọi database trong code đo thời gian và log lại những gì vượt ngưỡng. Hầu hết các team bắt đầu từ đây.

  • Ưu điểm: Dễ thêm vào, có thêm ngữ cảnh nghiệp vụ (user nào kích hoạt query), không cần thay đổi database
  • Nhược điểm: Bỏ sót hoàn toàn các query từ background job, cron task, migration và công cụ bên thứ ba. Bạn chỉ thấy những gì ứng dụng đã đo lường. Cách này còn tạo thêm overhead cho code ứng dụng.

Cách 2: Slow Query Log của PostgreSQL

Đặt log_min_duration_statement trong postgresql.conf để log mọi query vượt ngưỡng thời gian.

  • Ưu điểm: Bắt được mọi query, không cần extension, dễ bật lên
  • Nhược điểm: Log ra text query thô kèm giá trị tham số thực tế — đây là rủi ro bảo mật và quyền riêng tư trên production. Lượng log bùng nổ khi có nhiều request. Chỉ cho bạn thấy từng instance query riêng lẻ, không phải thống kê tổng hợp. Không thể trả lời câu hỏi “query nào chạy 50.000 lần mỗi ngày và trung bình mất 200ms?”

Cách 3: pg_stat_statements (Được Khuyến Nghị)

Extension chính thức của PostgreSQL, tổng hợp thống kê theo từng pattern query đã chuẩn hóa, trên tất cả connection, user và công cụ.

  • Ưu điểm: Overhead thấp (~1–5% CPU), bắt được mọi query bất kể nguồn gốc, chuẩn hóa tham số (coi WHERE id = 1 và WHERE id = 2 là cùng một pattern query), cho bạn dữ liệu tổng hợp để ưu tiên xử lý
  • Nhược điểm: Cần quyền superuser để bật, cần restart PostgreSQL lần đầu cài đặt, mặc định không lưu giá trị tham số query

Với công việc production lâu dài, pg_stat_statements là lựa chọn tốt nhất. Slow query log hữu ích cho các phiên debug một lần; còn pg_stat_statements cho bạn bức tranh thống kê để biết nên dành thời gian ở đâu.

Cài Đặt pg_stat_statements

Bước 1: Bật Extension

Thêm vào shared_preload_libraries trong file postgresql.conf. Bắt buộc phải restart — đây là bước không thể tránh duy nhất.

# Tìm file postgresql.conf của bạn
psql -U postgres -c "SHOW config_file;"

# Sửa postgresql.conf và thêm/cập nhật dòng này:
shared_preload_libraries = 'pg_stat_statements'

# Các cài đặt tùy chọn nhưng nên có:
pg_stat_statements.max = 10000       # Số lượng câu lệnh theo dõi tối đa
pg_stat_statements.track = all       # Theo dõi tất cả câu lệnh kể cả câu lệnh lồng nhau
pg_stat_statements.track_utility = on  # Theo dõi COPY, VACUUM, v.v.

# Restart PostgreSQL
sudo systemctl restart postgresql

# Với dịch vụ quản lý (RDS, Cloud SQL), chỉnh sửa parameter group
# rồi áp dụng — thường cần reboot

Bước 2: Tạo Extension Trong Database

-- Chạy với quyền superuser trong từng database muốn theo dõi
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Kiểm tra extension đang hoạt động
SELECT count(*) FROM pg_stat_statements;

Tìm Ra Những Query Tệ Nhất

Để extension thu thập dữ liệu trong vài giờ — lý tưởng nhất là 24–48 giờ với traffic thực tế. Sau đó chạy các query sau.

Top Query Theo Tổng Thời Gian (Nút Thắt Cổ Chai Lớn Nhất)

SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round((100 * total_exec_time /
    sum(total_exec_time) OVER ())::numeric, 2) AS percentage,
  left(query, 120) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Tổng thời gian mới là thứ thực sự ảnh hưởng đến tải database. Một query trung bình 5ms nhưng chạy 100.000 lần mỗi ngày tiêu tốn 500 giây thời gian database. Một query 500ms chỉ chạy một lần mỗi tuần gần như không đáng kể. Hãy tối ưu theo tổng chi phí, không phải theo độ trễ cao nhất.

Top Query Theo Thời Gian Thực Thi Trung Bình (Query Chậm Nhất Từng Lần)

SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  left(query, 120) AS query_preview
FROM pg_stat_statements
WHERE calls > 100  -- Bỏ qua các query chỉ chạy một lần
ORDER BY mean_exec_time DESC
LIMIT 15;

Tỷ Lệ Cache Miss (Buffer Hit Rate)

SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  shared_blks_hit,
  shared_blks_read,
  round(100.0 * shared_blks_hit /
    NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_rate_pct,
  left(query, 100) AS query_preview
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

hit_rate_pct dưới 90% trên một query được gọi thường xuyên đồng nghĩa với việc nó đang liên tục đọc từ đĩa. Nguyên nhân có thể là thiếu index, hoặc shared_buffers quá nhỏ so với working set của bạn. Trên server 32GB RAM, shared_buffers thường vẫn để mặc định 128MB — hãy kiểm tra lại.

Từ Thống Kê Đến Hành Động: Quy Trình Tối Ưu Thực Tế

Bước 1: Lấy Toàn Bộ Text Query

Cột query trong pg_stat_statements đã được chuẩn hóa và cắt bớt. Để lấy đầy đủ text của một query có vấn đề:

SELECT query
FROM pg_stat_statements
WHERE queryid = 1234567890;  -- Dùng queryid từ kết quả truy vấn trước

Bước 2: Chạy EXPLAIN ANALYZE

Thay thế bằng tham số ví dụ thực tế và chạy query qua EXPLAIN ANALYZE. Tùy chọn BUFFERS rất quan trọng — nếu thiếu nó bạn sẽ không thấy được hành vi I/O.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.email, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
  AND o.status = 'pending';

Ba điều cần chú ý: Seq Scan trên bảng lớn (thường cần thêm index), khoảng cách lớn giữa actual rows và estimated rows (thống kê cũ — chạy ANALYZE), và node Hash Join bị tràn ra đĩa.

Bước 3: Tạo Index Có Mục Tiêu

-- Index tổng hợp khớp với điều kiện WHERE + JOIN
CREATE INDEX CONCURRENTLY idx_orders_user_status
  ON orders (user_id, status)
  WHERE status = 'pending';  -- Index một phần nếu bạn chỉ query một status

-- Sau khi tạo, kiểm tra planner có dùng index không
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... -- query của bạn

Luôn dùng CONCURRENTLY trên production. Nếu không, quá trình build index sẽ giữ lock ACCESS EXCLUSIVE — mọi đọc và ghi đều bị chặn cho đến khi hoàn thành.

Bảo Trì: Reset và Theo Dõi Theo Thời Gian

Sau khi tối ưu, hãy reset thống kê để đo lường cải thiện trên baseline sạch:

-- Reset thống kê tất cả query (chỉ superuser)
SELECT pg_stat_statements_reset();

-- Hoặc reset thống kê cho một query cụ thể (PostgreSQL 12+)
SELECT pg_stat_statements_reset(0, 0, queryid)
FROM pg_stat_statements
WHERE query LIKE '%your_table%'
LIMIT 1;

Một cron job đơn giản chạy hàng tuần để export top 20 query ra file CSV sẽ cho bạn đường xu hướng theo thời gian. Khi cần chuyển CSV đó sang JSON để import vào dashboard giám sát, toolcraft.app/vi/tools/data/csv-to-json xử lý ngay trên trình duyệt — không upload lên server bên thứ ba, điều này quan trọng khi CSV chứa các pattern query từ hệ thống production.

Cấu Hình Production Được Khuyến Nghị

Đây là cấu hình postgresql.conf đáng chạy trên mọi instance production:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on  # Giữ lại thống kê khi restart

# Slow query log bổ sung (không thay thế pg_stat_statements)
log_min_duration_statement = 1000  # Log các query chạy trên 1 giây
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h '

pg_stat_statements.save = on dễ bị bỏ qua. Tùy chọn này giữ lại thống kê qua các lần restart — rất quan trọng khi bạn đang theo dõi một pattern tích lũy trong nhiều ngày mà server lại bị reboot.

Với dashboard, cả pgBadger (dựa trên log) và pganalyze (SaaS) đều xây dựng trên nền tảng dữ liệu này. AWS RDS Performance Insights cũng vậy. Nhưng bạn không cần bất kỳ công cụ nào trong số đó để bắt đầu — các câu SQL thuần ở trên đã giải quyết được 80% nhu cầu.

Bắt Tay Vào Làm Ngay

Bật extension lên, thu thập 24–48 giờ traffic thực tế, rồi chạy query theo tổng thời gian. Theo quy luật thường thấy, 80% tải database đến từ 3–5 query. Hãy sửa những query đó trước. Mọi thứ còn lại chỉ là nhiễu cho đến khi bạn giải quyết xong phần đầu danh sách.

Số liệu không biết nói dối. Nếu một query đứng đầu bảng xếp hạng total_exec_time của bạn, đó chính xác là nơi công sức bỏ ra sẽ mang lại hiệu quả cao nhất. Ngừng đoán mò, bắt đầu đo lường.

Share:
Tags: