PostgreSQL Bài 39: Giám sát hệ thống và phân tích hiệu năng với pg_stat_statements và Prometheus

Khi hệ thống bước vào giai đoạn Production với hàng ngàn request mỗi giây, bạn không thể tối ưu hóa cơ sở dữ liệu bằng cách "đoán" xem câu lệnh nào đang chạy chậm. Việc ngồi canh màn hình terminal và chạy lệnh EXPLAIN thủ công chỉ giải quyết được các lỗi đã biết. Để duy trì một hệ thống khỏe mạnh, bạn cần khả năng giám sát toàn diện (Observability).

Bài học này sẽ trang bị cho bạn hai vũ khí mạnh nhất trong giới quản trị PostgreSQL: pg_stat_statements để soi chiếu từng câu truy vấn bên trong database, và bộ đôi Prometheus + Grafana để theo dõi bức tranh toàn cảnh của cả cụm máy chủ.

1. pg_stat_statements: Camera an ninh của Query Planner

pg_stat_statements là một extension chính thức (official) đi kèm với PostgreSQL. Khi được kích hoạt, nó âm thầm ghi lại thống kê chi tiết của mọi câu lệnh SQL đã được thực thi trên server: câu lệnh đó chạy bao nhiêu lần, tốn tổng cộng bao nhiêu mili-giây, ngốn bao nhiêu CPU và đọc bao nhiêu block từ RAM/Đĩa.

1.1. Cách kích hoạt (Bắt buộc restart server)

Vì extension này cần cấp phát vùng nhớ dùng chung (shared memory) để theo dõi các tiến trình, bạn phải cấu hình nó trong postgresql.conf và khởi động lại database.

Ini, TOML

# Thêm pg_stat_statements vào danh sách thư viện nạp sẵn
shared_preload_libraries = 'pg_stat_statements'

# Cấu hình giới hạn số lượng câu query khác nhau được lưu vết (mặc định 5000)
pg_stat_statements.max = 10000

# Theo dõi mọi câu lệnh (kể cả câu lệnh nội bộ của hàm procedure)
pg_stat_statements.track = all

Sau khi restart PostgreSQL, kết nối vào database và tạo extension:

SQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

1.2. Phân tích hiệu năng bằng SQL Thực chiến

Mọi thông số được lưu trong view pg_stat_statements. Lưu ý rằng extension này tự động chuẩn hóa (normalize) câu lệnh. Các câu SELECT * FROM users WHERE id = 1 và id = 2 sẽ được gộp chung thành SELECT * FROM users WHERE id = $1.

Truy vấn 1: Tìm Top 5 câu lệnh ngốn nhiều thời gian nhất hệ thống (Kẻ thù số 1)

SQL

SELECT 
    calls, 
    total_exec_time / 1000 AS total_time_seconds, 
    mean_exec_time AS avg_time_ms, 
    query 
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 5;

Ghi chú: Đôi khi một câu query cực nhanh (1ms) nhưng bị gọi 1 triệu lần/ngày sẽ làm nghẽn CPU hơn là một câu query chậm (1 giây) nhưng chỉ gọi 10 lần/ngày. Sắp xếp theo total_exec_time giúp bạn tìm ra gốc rễ gây quá tải.

Truy vấn 2: Đo lường Cache Hit Ratio cho từng câu lệnh (Tìm thủ phạm cày nát ổ cứng)

SQL

SELECT 
    query, 
    shared_blks_hit, 
    shared_blks_read,
    ROUND(
        (shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read + 0.01)) * 100, 2
    ) AS cache_hit_ratio_percent
FROM pg_stat_statements 
WHERE shared_blks_hit + shared_blks_read > 1000
ORDER BY cache_hit_ratio_percent ASC 
LIMIT 5;

Ghi chú: Những câu lệnh có cache_hit_ratio thấp (< 90%) nghĩa là chúng đang liên tục phải đọc dữ liệu từ ổ đĩa (Disk I/O) thay vì RAM. Đây là ứng cử viên hàng đầu để bạn thêm Index hoặc tối ưu lại WHERE.

Reset thống kê: Khi bạn vừa tạo xong Index và muốn đo lường lại từ đầu, hãy chạy lệnh:

SQL

SELECT pg_stat_statements_reset();

2. Kiến trúc Giám sát Thời gian thực với Prometheus & Grafana

pg_stat_statements rất tuyệt để phân tích SQL, nhưng nó không cho bạn thấy biểu đồ lượng RAM đang tiêu thụ, số kết nối đang mở, hay độ trễ Replication hiện tại. Đối với hệ thống lớn, bạn cần kiến trúc Time-Series Monitoring.

[ POSTGRESQL ] ◄── (Chạy SQL thu thập số liệu) ── [ POSTGRES_EXPORTER ] (Port 9187)
                                                           ▲
                                                           │ (Kéo dữ liệu mỗi 15s)
[ GRAFANA ] ◄──── (Vẽ biểu đồ trực quan) ──────── [ PROMETHEUS ] (Time-series DB)

2.1. Thành phần lõi: Postgres Exporter

Prometheus không hiểu giao thức kết nối của PostgreSQL. Vì vậy, chúng ta cần cài đặt một dịch vụ trung gian gọi là Postgres Exporter (phát triển bằng Go). Dịch vụ này kết nối vào PostgreSQL, liên tục chạy các câu query hệ thống (vào pg_stat_activity, pg_stat_bgwriter...), sau đó xuất dữ liệu ra một endpoint HTTP /metrics theo chuẩn cấu trúc văn bản của Prometheus.

2.2. Prometheus và Grafana

• Prometheus: Đóng vai trò là máy chủ trung tâm, định kỳ 15 giây/lần sẽ gọi HTTP GET đến cổng 9187 của Postgres Exporter để "cào" (scrape) dữ liệu và lưu vào cơ sở dữ liệu chuỗi thời gian của riêng nó.

• Grafana: Kết nối vào Prometheus để hiển thị các số liệu này lên các Dashboard trực quan. Bạn không cần tự xây biểu đồ từ đầu, cộng đồng đã cung cấp sẵn hàng trăm template chuẩn (ví dụ: Dashboard ID 9628 - PostgreSQL Database).

3. Các chỉ số (Metrics) sinh tử cần đưa lên màn hình cảnh báo (Alerts)

Khi xây dựng Dashboard trên Grafana, hãy thiết lập cảnh báo tự động (gửi qua Slack/Telegram) cho 4 nhóm chỉ số sau:

4. Tối ưu Autovacuum thông qua Monitoring

Rất nhiều DBA bỏ quên việc giám sát Autovacuum cho đến khi ổ cứng đầy 100%. Thông qua Prometheus, bạn cần theo dõi sát sao số liệu pg_stat_user_tables.n_dead_tup (số dòng dữ liệu chết).

Nếu đường đồ thị của Dead Tuples liên tục đi lên mà không có những pha "rớt thẳng đứng" (chứng tỏ VACUUM đã chạy và dọn rác thành công), hệ thống của bạn đang gặp nguy. Giải pháp không phải là tắt Autovacuum, mà là làm cho nó chạy nhanh hơn và quyết liệt hơn bằng cách chỉnh cấu hình:

Ini, TOML

# Giảm ngưỡng kích hoạt (Mặc định 20% dòng thay đổi mới chạy, giảm xuống 5% để chạy sớm hơn)
autovacuum_vacuum_scale_factor = 0.05

# Tăng số lượng worker chạy ngầm dọn rác cùng lúc
autovacuum_max_workers = 5

# Tăng chi phí giới hạn để Worker dọn rác nhanh hơn (không bị ngủ đông chờ I/O)
autovacuum_vacuum_cost_limit = 2000

Nhờ có hệ thống giám sát, bạn sẽ trực tiếp thấy biểu đồ Dead Tuples quay đầu đi xuống ngay sau khi áp dụng các cấu hình trên, đảm bảo hệ thống luôn trong trạng thái lý tưởng.