Tối ưu 4 — Giám sát & phát hiện bất thường

Lưu ý đọc bài: Các view pg_stat_*, extension pg_stat_statements/pgstattuple và một số hàm chẩn đoán yêu cầu quyền hoặc extension mà sandbox chỉ-đọc có thể không có. Vì vậy mọi câu truy vấn giám sát trong bài được đánh dấu (minh hoạ) — đây là kiến thức vận hành để bạn mang lên máy chủ thật, không phải nơi để chạy. Không có block nào đánh dấu "▶ Chạy được".

Một buổi sáng, tin nhắn dội về: "Hệ thống chậm." Câu hỏi không phải "sửa thế nào" mà là "chậm ở đâu?". Người thiếu kinh nghiệm sẽ đoán — restart, thêm RAM, dựng thêm index bừa. Người có kinh nghiệm sẽ đo. Bài này là bộ đồ nghề chẩn đoán: nhìn vào đâu, đọc con số gì, để chuyển từ "cảm giác chậm" sang "truy vấn X đang giữ khóa trên bảng Y suốt 40 phút".

Triết lý: đo trước khi tối ưu

Có một câu kinh điển trong ngành: "Đừng tối ưu cái bạn chưa đo." Tối ưu dựa trên linh cảm thường sửa nhầm chỗ — bạn tốn buổi chiều đánh index cho một bảng, trong khi thủ phạm thật là một giao dịch bị treo idle in transaction chặn VACUUM.

Điều đầu tiên cần làm khi chẩn đoán là phân loại vấn đề:

• Theo truy vấn (query-level): một hoặc vài câu SQL cụ thể ngốn phần lớn thời gian. Sửa bằng cách viết lại truy vấn hoặc thêm index. → xem Đọc EXPLAIN và Viết lại truy vấn.
• Theo hệ thống (system-level): không câu nào đặc biệt tệ, nhưng tổng thể nghẽn — hết kết nối, thiếu RAM (work_mem), đĩa quá tải, checkpoint dồn dập, replica trễ, hoặc bảng phình (bloat) do thiếu bảo trì.

Hai nhóm này cần công cụ khác nhau. Nhầm nhóm là mất thời gian. Sơ đồ dưới là cây chẩn đoán bạn sẽ đi theo mỗi khi DB "ốm".

pg_stat_statements — tìm "top offenders"

Đây là công cụ số một để trả lời câu hỏi "truy vấn nào tốn tài nguyên nhất?". Extension pg_stat_statements gom thống kê tích lũy cho từng "hình dạng" câu lệnh: tổng thời gian thực thi, số lần gọi, thời gian trung bình, số dòng trả về.

Điểm mấu chốt: nó chuẩn hóa (normalize) truy vấn. SELECT * FROM accounts WHERE id = 42 và ... WHERE id = 99 được gom về một dòng thống kê với dạng ... WHERE id = $1. Nhờ vậy bạn thấy được tổng chi phí của một mẫu truy vấn dù nó chạy triệu lần với tham số khác nhau.

Cần bật trước qua shared_preload_libraries (yêu cầu restart) rồi tạo extension:

-- (minh hoạ) bật extension — cần shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Xếp hạng theo tổng thời gian — thủ phạm số một thường không phải câu chậm nhất mỗi lần, mà là câu hơi chậm nhưng chạy rất nhiều:

-- (minh hoạ) Top 10 truy vấn theo TỔNG thời gian thực thi
SELECT
    substring(query, 1, 80)         AS truy_van,
    calls,
    round(total_exec_time::numeric, 1)          AS tong_ms,
    round(mean_exec_time::numeric, 2)           AS trung_binh_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Lưu ý phiên bản: từ PostgreSQL 13 cột đổi thành total_exec_time / mean_exec_time. Bản cũ (≤ 12) dùng total_time / mean_time.

Ba góc nhìn thường soi, mỗi góc lộ một loại vấn đề khác nhau:

• ORDER BY total_exec_time DESC — câu ngốn nhiều nhất về tổng. Sửa câu này giảm tải toàn hệ thống nhiều nhất.
• ORDER BY mean_exec_time DESC — câu chậm nhất mỗi lần. Ứng viên tốt cho EXPLAIN và thêm index.
• ORDER BY calls DESC — câu được gọi nhiều bất thường; đôi khi lộ ra lỗi N+1 từ tầng ứng dụng.

Nếu bản Postgres đủ mới, pg_stat_statements còn tách thời gian I/O đọc/ghi block (shared_blk_read_time), giúp phân biệt câu chậm vì CPU/plan xấu hay vì đọc đĩa.

Sau khi vá xong một truy vấn, reset bộ đếm để đo lại từ đầu:

-- (minh hoạ) xóa thống kê tích lũy, bắt đầu đo lại
SELECT pg_stat_statements_reset();

pg_stat_activity — ai đang làm gì ngay lúc này

Nếu pg_stat_statements là lịch sử, thì pg_stat_activity là hiện tại: mỗi dòng là một phiên (backend) đang kết nối, cùng state, câu lệnh đang chạy, và thời điểm bắt đầu.

Các cột đáng quan tâm:

Long-running query — câu đã chạy quá lâu, dấu hiệu của plan xấu, thiếu index, hoặc bị chặn:

-- (minh hoạ) truy vấn đang chạy > 30 giây
SELECT
    pid,
    now() - query_start        AS chay_bao_lau,
    state,
    wait_event_type,
    wait_event,
    substring(query, 1, 100)   AS truy_van
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '30 seconds'
ORDER BY chay_bao_lau DESC;

Idle in transaction — kẻ giết thầm lặng. Một phiên mở BEGIN, làm một việc, rồi... quên commit (thường do bug ứng dụng hoặc kết nối treo). Giao dịch mở này giữ khóa và, tệ hơn, giữ một snapshot khiến VACUUM không dọn được dead tuples cũ hơn nó — nguyên nhân gốc của bloat leo thang. Câu sau tìm chúng:

-- (minh hoạ) phiên "idle in transaction" mở lâu — nghi phạm giữ khóa & chặn VACUUM
SELECT
    pid,
    now() - xact_start   AS giao_dich_mo_bao_lau,
    now() - state_change AS idle_bao_lau,
    usename,
    substring(query, 1, 80) AS cau_cuoi_cung
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Khi tìm ra kẻ gây hại, có thể yêu cầu hủy nhẹ nhàng hoặc chấm dứt phiên:

-- (minh hoạ) hủy câu đang chạy của một pid (không đóng kết nối)
SELECT pg_cancel_backend(12345);
-- (minh hoạ) chấm dứt hẳn phiên (mạnh tay hơn, rollback giao dịch)
SELECT pg_terminate_backend(12345);

Từ PostgreSQL 14, idle_in_transaction_session_timeout giúp tự động ngắt các phiên treo — một biện pháp phòng ngừa nên bật ở mức hợp lý (ví dụ vài phút).

Khóa & deadlock — ai chặn ai

Khi một câu UPDATE/DELETE "treo" mà không báo lỗi, gần như chắc chắn nó đang chờ khóa do phiên khác giữ. Postgres phơi bày trạng thái khóa qua view pg_locks, nhưng đọc thô rất khó. Cách thực dụng nhất là hàm pg_blocking_pids():

-- (minh hoạ) ai đang bị chặn, và bị chặn bởi PID nào
SELECT
    blocked.pid                       AS pid_bi_chan,
    blocked.usename                   AS user_bi_chan,
    substring(blocked.query, 1, 60)   AS cau_bi_chan,
    blocking_pid,
    substring(blocker.query, 1, 60)   AS cau_dang_chan
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS blocking_pid
JOIN pg_stat_activity AS blocker ON blocker.pid = blocking_pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Kết quả cho bạn chuỗi phụ thuộc: phiên A chờ khóa mà phiên B đang giữ. Từ đó quyết định: đợi B xong, hay hủy B (pg_cancel_backend).

Deadlock khác với chờ khóa thông thường. Deadlock là vòng tròn: A giữ khóa r1 và xin r2; B giữ r2 và xin r1 — không ai nhường. Điểm hay của Postgres: nó tự phát hiện deadlock sau deadlock_timeout (mặc định 1 giây), rồi tự hủy một trong hai giao dịch để phá vỡ vòng, giao dịch bị hủy nhận lỗi deadlock detected. Bạn không cần can thiệp lúc đó, nhưng cần nhìn log để biết deadlock có tái diễn không (thường do thứ tự truy cập tài nguyên không nhất quán giữa các giao dịch).

Để nhìn thấy các cuộc chờ khóa trong log, bật:

-- (minh hoạ) ghi log mỗi khi một câu phải CHỜ khóa quá deadlock_timeout
SET log_lock_waits = on;   -- thường đặt ở postgresql.conf, phạm vi toàn cục

Wait events — chờ vì cái gì

Cột wait_event_type trong pg_stat_activity (và trong pg_stat_statements bản mới) trả lời câu hỏi cốt lõi: khi backend không tiến triển, nó đang chờ tài nguyên nào? Các nhóm phổ biến:

Một wait_event null với state = active nghĩa là backend đang thực sự dùng CPU — nếu đó là số đông, vấn đề thiên về CPU/plan hơn là I/O hay khóa.

Cache hit ratio & thống kê bảng/chỉ mục

Cache hit ratio

Postgres đọc dữ liệu theo block. Nếu block đã nằm trong shared_buffers (RAM) thì tính là hit; nếu phải đọc từ đĩa (qua OS cache) thì là read. Tỷ lệ hit cao (thường mong muốn > 95–99% với tải OLTP) cho biết bộ nhớ đang phục vụ tốt.

-- (minh hoạ) cache hit ratio toàn database hiện tại
SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_ratio_pct
FROM pg_stat_database
WHERE datname = current_database();

Cẩn trọng diễn giải: con số này tích lũy từ lúc reset, nên hit ratio thấp có thể chỉ vì mới khởi động (cache còn lạnh), không nhất thiết là thiếu RAM. Hãy xem xu hướng, đừng phán xét một lần chụp.

pg_stat_user_tables — seq scan vs index scan

View này cho biết mỗi bảng bị quét tuần tự (seq_scan) bao nhiêu lần so với quét index (idx_scan), và bao nhiêu dead tuple đang tồn đọng:

-- (minh hoạ) bảng bị quét tuần tự nhiều — ứng viên cần index
SELECT
    relname                 AS bang,
    seq_scan,
    idx_scan,
    n_live_tup,
    n_dead_tup,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 15;

seq_scan cao trên bảng lớn là cờ đỏ: có thể thiếu index cho các truy vấn nóng. (Trên bảng nhỏ, seq scan lại là bình thường và thường nhanh hơn index — đừng đánh index vô tội vạ; hãy kiểm chứng bằng EXPLAIN.)

pg_stat_user_indexes — index thừa

Index không miễn phí: mỗi lần INSERT/UPDATE/DELETE phải cập nhật cả index, và index chiếm dung lượng. Index không bao giờ được dùng (idx_scan = 0) là gánh nặng thuần túy:

-- (minh hoạ) index chưa từng được dùng — ứng viên để loại bỏ
SELECT
    s.relname       AS bang,
    s.indexrelname  AS chi_muc,
    s.idx_scan      AS so_lan_dung,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS kich_thuoc
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique          -- đừng xóa index phục vụ ràng buộc unique/PK
ORDER BY pg_relation_size(s.indexrelid) DESC;

Lưu ý: chỉ tin idx_scan = 0 khi thống kê đã tích lũy đủ lâu (qua đủ chu kỳ tải, kể cả báo cáo cuối tháng). Một index chỉ dùng cho job hàng quý sẽ trông "vô dụng" trong 30 ngày.

Bloat — khi bảng và index phình ra

Đây là hệ quả trực tiếp của mô hình MVCC (đã bàn ở Giao dịch & ACID). Khi bạn UPDATE một dòng, Postgres không sửa tại chỗ mà tạo phiên bản mới và đánh dấu phiên bản cũ là dead tuple. DELETE cũng chỉ đánh dấu chết. Các dead tuple này chiếm chỗ cho đến khi VACUUM thu hồi.

Nếu VACUUM không theo kịp (autovacuum bị tắt, ngưỡng quá cao, hoặc một giao dịch dài chặn việc dọn), dead tuple tích tụ → bảng và index phình to dù dữ liệu sống không tăng. Hậu quả: đọc chậm hơn (phải quét cả phần chết), tốn đĩa, cache kém hiệu quả.

Dấu hiệu nhanh: n_dead_tup lớn so với n_live_tup trong pg_stat_user_tables. Đo chính xác hơn thì dùng extension pgstattuple:

-- (minh hoạ) đo tỷ lệ không gian chết thực tế của một bảng
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('accounts');
-- cột dead_tuple_percent, free_percent cho biết mức phình

Xử lý bloat (autovacuum, VACUUM, VACUUM FULL, pg_repack, khi nào cần REINDEX) là chủ đề riêng — xem Bảo trì & VACUUM.

Chỉ số hệ thống

Không phải mọi vấn đề đều nằm trong SQL. Vài chỉ số cấp hệ thống cần theo dõi liên tục:

• Kết nối gần max_connections. Mỗi kết nối tốn RAM và tài nguyên; đầy kết nối gây lỗi "too many clients". Đếm nhanh:

-- (minh hoạ) số kết nối theo trạng thái so với giới hạn
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;
-- so với: SHOW max_connections;

Nếu thường xuyên chạm trần, cân nhắc connection pooler (PgBouncer) thay vì cứ nâng max_connections.

• Replication lag. Trên hệ có replica, độ trễ replica quyết định dữ liệu đọc từ replica "cũ" bao nhiêu. Cách đo và xử lý — xem Replication.

• Checkpoint dồn dập. Checkpoint quá thường xuyên (do max_wal_size nhỏ) gây spike I/O ghi. Theo dõi qua pg_stat_bgwriter (hoặc pg_stat_checkpointer ở bản mới): nếu checkpoint chủ yếu do "hết WAL" (checkpoints_req) thay vì theo lịch (checkpoints_timed), nên tăng max_wal_size.

• Temp files. Khi một sort/hash vượt work_mem, Postgres tràn ra file tạm trên đĩa — chậm. Nếu temp_files/temp_bytes (trong pg_stat_database) tăng mạnh, đó là tín hiệu work_mem thiếu cho các truy vấn nặng sort/hash. Có thể bật log_temp_files để biết câu nào gây tràn.

Cảnh báo & công cụ

Chẩn đoán thủ công tốt cho tình huống cháy nhà. Nhưng vận hành trưởng thành cần giám sát chủ động:

• log_min_duration_statement — ghi log mọi câu chạy lâu hơn ngưỡng (ví dụ = 1000 cho 1 giây). Đây là cách rẻ nhất để bắt slow query mà không cần công cụ ngoài.
• auto_explain — module bổ trợ tự động ghi kế hoạch thực thi của các câu chậm vào log, kể cả EXPLAIN ANALYZE. Vô giá khi câu chậm không tái hiện được lúc bạn ngồi soi.
• Prometheus postgres_exporter + Grafana — thu các chỉ số pg_stat_* theo thời gian, dựng dashboard và cảnh báo (alert) khi hit ratio tụt, kết nối tăng vọt, replication lag vượt ngưỡng. Giám sát theo xu hướng mạnh hơn nhiều so với chụp một lần.
• pgBadger — phân tích file log Postgres và sinh báo cáo HTML: truy vấn chậm nhất, phân bố lỗi, khóa, temp files... Chạy định kỳ trên log để "khám sức khỏe".

Use case thực tế: một buổi sáng DB chậm

9h05 sáng, kênh vận hành báo: API thanh toán phản hồi chậm bất thường. Quy trình khoanh vùng theo cây chẩn đoán:

1. Có phiên bị chặn không? Chạy truy vấn pg_blocking_pids ở trên. → Ra ngay: 12 phiên active đang bị chặn, tất cả trỏ về một blocking_pid.
2. Kẻ chặn là ai? Tra pid đó trong pg_stat_activity. → state = 'idle in transaction', xact_start cách đây 41 phút, câu cuối là một UPDATE accounts SET ... WHERE id = $1. Rõ: một giao dịch quên commit đang giữ khóa dòng.
3. Vì sao nó treo? wait_event = ClientRead — nghĩa là Postgres đang chờ ứng dụng gửi lệnh tiếp theo. Thủ phạm gốc nằm ở tầng app (một request bị treo giữ kết nối mở).
4. Xử lý tức thời: SELECT pg_terminate_backend(<pid>); — hủy phiên treo, khóa được nhả, 12 phiên kia thoát hàng đợi, API hồi phục trong vài giây.
5. Xử lý gốc: đặt idle_in_transaction_session_timeout để lần sau tự ngắt; kiểm tra tại sao request app không kết thúc giao dịch (thiếu try/finally đóng transaction?).
6. Đề phòng tái diễn: bật log_lock_waits và thêm alert Grafana khi có phiên idle in transaction > 5 phút.

Tổng thời gian khoanh vùng: dưới 5 phút — vì đi theo cây chẩn đoán chứ không đoán mò. Nếu bước 1 rỗng (không ai bị chặn), ta rẽ nhánh sang pg_stat_statements (theo truy vấn), rồi wait events (I/O), rồi n_dead_tup (bloat), đúng thứ tự trong sơ đồ.

Ghi nhớ

• Đo trước, tối ưu sau. Phân loại ngay: vấn đề theo truy vấn hay theo hệ thống — mỗi loại có bộ công cụ riêng.
• pg_stat_statements là điểm khởi đầu cho câu hỏi "truy vấn nào tốn nhất". Soi cả total_exec_time, mean_exec_time và calls — mỗi cột lộ một loại vấn đề.
• pg_stat_activity cho ảnh chụp hiện tại: bắt long-running query và đặc biệt là idle in transaction (giữ khóa + chặn VACUUM).
• pg_blocking_pids() biến pg_locks khó đọc thành chuỗi "ai chặn ai" rõ ràng. Deadlock được Postgres tự phát hiện và phá; việc của bạn là đọc log để nó không tái diễn.
• Wait events trả lời "chờ vì cái gì": Lock (tranh chấp), IO (đĩa/RAM), LWLock (nội bộ), Client (app giữ).
• Cache hit ratio, seq_scan vs idx_scan, idx_scan = 0, n_dead_tup là các chỉ số sức khỏe — nhưng nhớ chúng tích lũy, hãy đọc theo xu hướng, không phán xét một lần chụp.
• Bloat là hệ quả của MVCC khi VACUUM không theo kịp; nhận biết qua n_dead_tup và pgstattuple, xử lý ở bài VACUUM.
• Giám sát trưởng thành = chủ động: log_min_duration_statement, auto_explain, postgres_exporter + Grafana, pgBadger — cảnh báo trước khi người dùng phàn nàn.

Đọc tiếp trong series:

• Tối ưu 1 — Đọc EXPLAIN
• Tối ưu 3 — Viết lại truy vấn
• Tối ưu 5 — Bảo trì & VACUUM
• Tối ưu 6 — Replication