pg_stat_activity trong PostgreSQL: nhìn database đang chạy gì và ai chặn ai

Database đang chậm, và bạn cần biết ngay lúc này nó đang làm gì. Không phải thống kê lịch sử, mà ảnh chụp tức thời: phiên nào đang chạy query gì, phiên nào đang chờ, và quan trọng nhất — nếu có gì bị treo, ai đang chặn ai. Công cụ số một cho việc đó là view pg_stat_activity. Bài này không giải thích suông: tôi dựng thật ba phiên xung đột với nhau rồi dùng pg_stat_activity để lần ra thủ phạm và xử lý.

Một dòng cho mỗi phiên

pg_stat_activity trả về một dòng cho mỗi kết nối (backend) đang mở, kèm những cột chẩn đoán quan trọng nhất: pid (định danh phiên), state (đang làm gì), wait_event_type/wait_event (đang chờ gì), query (câu lệnh hiện tại), và các mốc thời gian (query_start, xact_start, state_change).

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS thoi_gian, left(query, 50)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY query_start;   -- query chạy lâu nhất lên đầu

Hình 1: pg_stat_activity cho mỗi phiên một dòng. Ba trạng thái quan trọng (active, idle, idle-in-transaction). pg_blocking_pids tìm ai chặn ai. pg_cancel_backend/pg_terminate_backend để can thiệp.

Đo thật: ba phiên xung đột

Tôi dựng ba phiên chạy đồng thời trên một bảng taikhoan: một phiên chạy query dài (pg_sleep), một phiên BEGIN rồi UPDATE hàng id=1 nhưng không commit (idle-in-transaction), và một phiên thứ ba cũng cố UPDATE chính hàng id=1 đó. Rồi soi pg_stat_activity:

Hình 2: Ba phiên — 827 active chạy query dài, 843 active nhưng chờ Lock/transactionid (bị chặn), 836 idle-in-transaction giữ khoá. pg_blocking_pids(843)={836} chỉ đúng thủ phạm. Terminate 836 giải phóng, 843 chạy xong.

Ba trạng thái hiện ra rõ ràng:

• pid 827 — active, chờ Timeout/PgSleep: đang chạy query dài. Không chặn ai, chỉ tốn thời gian.
• pid 843 — active, chờ Lock/transactionid: đang chạy UPDATE nhưng bị chặn, chờ một khoá. Đây là dấu hiệu tắc nghẽn.
• pid 836 — idle in transaction, chờ Client/ClientRead: phiên này không chạy gì cả nhưng đang trong một giao dịch mở — và nó giữ khoá trên hàng id=1.

Lần ra thủ phạm: pg_blocking_pids

Nhìn state thôi chưa đủ — ta thấy 843 bị chặn, nhưng ai chặn? Hàm pg_blocking_pids(pid) trả về mảng các pid đang giữ khoá mà phiên đó phải chờ:

SELECT pid, pg_blocking_pids(pid) AS bi_chan_boi, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Kết quả thật: pid 843 bị chặn bởi {836}. Chính xác. Thủ phạm là phiên idle-in-transaction 836 — nó UPDATE hàng id=1 rồi ngồi im không commit, giữ khoá hàng đó, khiến 843 (cũng muốn sửa id=1) phải xếp hàng chờ vô thời hạn.

Vì sao idle-in-transaction nguy hiểm

Phiên 836 minh hoạ một trong những vấn đề âm thầm tệ nhất. Nó không chạy query nào (nên không xuất hiện trong "query chậm"), nhưng giao dịch của nó vẫn mở — đo thật cho thấy nó giữ backend_xid = 771408 suốt 18 giây và tăng. Một giao dịch mở lâu gây hai tai hại: giữ khoá (chặn phiên khác như ta thấy), và giữ xmin horizon (khiến VACUUM không dọn được dead tuple mới hơn giao dịch đó — bài về bloat đã đo). Một phiên idle-in-transaction bị bỏ quên có thể làm phình cả database.

Can thiệp: cancel vs terminate

PostgreSQL cho hai mức can thiệp:

• pg_cancel_backend(pid): huỷ query đang chạy của phiên, nhưng giữ kết nối. Nhẹ nhàng — dùng khi chỉ muốn dừng một query dài.
• pg_terminate_backend(pid): ngắt hẳn cả phiên. Mạnh hơn — cần cho idle-in-transaction (không có query đang chạy để cancel) hoặc phiên lì không phản hồi.

Với phiên 836 idle-in-transaction, cancel vô dụng (không có query để huỷ), phải terminate. Đo thật: pg_terminate_backend(836) trả t, và ngay sau đó phiên 843 hết bị chặn, UPDATE chạy xong. Khoá được giải phóng, tắc nghẽn tan.

Đánh đổi cần cân nhắc

pg_stat_activity là ảnh chụp tức thời, không phải lịch sử. Nó cho biết bây giờ đang chạy gì, không lưu lại quá khứ. Một query gây chậm rồi kết thúc sẽ không thấy ở đây — muốn phân tích lịch sử cần pg_stat_statements (bài khác). Dùng đúng công cụ: pg_stat_activity cho "đang xảy ra gì", pg_stat_statements cho "cái gì tốn nhất theo thời gian".

Terminate là biện pháp mạnh, cẩn thận với production. pg_terminate_backend ngắt phiên đột ngột, cuộn ngược giao dịch của nó — dữ liệu chưa commit mất. Với một idle-in-transaction bị bỏ quên thì đó chính là điều bạn muốn, nhưng đừng terminate bừa một phiên đang làm việc thật. Xác định đúng thủ phạm bằng pg_blocking_pids trước khi ra tay.

Đặt idle_in_transaction_session_timeout để phòng. Thay vì săn idle-in-transaction bằng tay, đặt tham số này để PostgreSQL tự ngắt các phiên ngồi trong giao dịch quá lâu. Đây là hàng rào phòng thủ chủ động, tốt hơn là chữa cháy khi đã tắc.

Ba ý mang về

1. pg_stat_activity cho mỗi phiên một dòng với state, wait_event, query và mốc thời gian — sắp theo query_start để tìm query chạy lâu, và để ý ba trạng thái: active (đang chạy), idle (rảnh, không sao), idle-in-transaction (nguy hiểm).
2. pg_blocking_pids(pid) chỉ đúng ai chặn ai: đo thật, một UPDATE bị chặn (chờ Lock/transactionid) được lần ra là bị phiên idle-in-transaction giữ khoá — nhìn state thôi không đủ, phải dùng hàm này để tìm thủ phạm thật.
3. Idle-in-transaction giữ khoá và giữ xmin (chặn VACUUM): đo thật một phiên giữ giao dịch mở 18 giây; giải quyết bằng pg_terminate_backend (cancel vô dụng vì không có query), và phòng ngừa bằng idle_in_transaction_session_timeout.

Phần sau ta đào sâu vào chính cột wait_event vừa gặp: Phần sau mổ xẻ wait events — cách đọc hệ thống đang chờ ở đâu (khoá, I/O, mạng), và dùng chúng để tìm nút thắt cổ chai thật sự.