Theo dõi giao dịch đang hoạt động realtime với pg_stat_activity

pg_stat_activity về cơ bản là một cửa sổ realtime giúp mày hiểu được DB của mình đang có gì xảy ra ngay lúc này. Ở bài trước tụi mình đã xem qua căn bản rồi, giờ đào sâu hơn chút về cách dùng tool mạnh này nha.

Ví dụ query cơ bản tới pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

Query này sẽ trả về tất cả kết nối đang hoạt động và các query hiện tại. Ngon! Nhưng dữ liệu sẽ nhiều lắm, ngồi lọc chắc tới sáng. Vậy nên nên lọc ra info quan trọng nhất thôi.

Các trường chính trong pg_stat_activity

Cùng xem qua mấy trường key mà mày sẽ cần ngoài mấy cái đã biết. query_start cho biết thời điểm query bắt đầu chạy, cực kỳ quan trọng để xác định mấy thao tác lâu. pid chứa ID process của kết nối — dùng để quản lý (ví dụ kill) kết nối đó. state_change cho biết lúc nào trạng thái hiện tại của kết nối được thiết lập, rất hữu ích để phân tích mấy trạng thái "lì đòn" kéo dài.

Ví dụ lấy ra các process đang active:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Làm sao phát hiện query lâu?

Giả sử mày là admin DB, tự nhiên server load tăng vọt. Làm gì tiếp? Đầu tiên phải biết query nào đang ngốn tài nguyên. Dùng pg_stat_activity để tìm mấy query "háu đói" này.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Query này sẽ show ra tất cả query chạy hơn 10 giây. Tuỳ nhu cầu mà chỉnh lại interval cho phù hợp nhé.

Kết thúc query có vấn đề

Giờ thử xem cách kill mấy query chạy lâu quá làm DB bị đơ. Dùng hàm pg_terminate_backend() để ép process dừng lại.

Ví dụ kill process với PID cụ thể:

SELECT pg_terminate_backend(12345);

Trong đó 12345 là ID process (trường pid) lấy từ pg_stat_activity.

Lưu ý: Kill process có thể gây rollback cho transaction chưa hoàn thành, nên cẩn thận nha.

Nếu muốn tự động kill hết mấy process "treo", ví dụ idle transaction, thì chạy block PL/pgSQL sau. Vì mày đã học lập trình rồi, chắc biết loop là gì — nó lặp lại thao tác cho tới khi hết dữ liệu hoặc điều kiện không còn đúng:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Đây là cách động để dọn dẹp mấy transaction có vấn đề. Vòng FOR sẽ duyệt từng dòng kết quả và gọi hàm kill process cho từng PID tìm được.

Sắp tới tụi mình sẽ học PL/pgSQL, đợi xíu nữa thôi :P

Lọc theo trạng thái transaction

Đôi khi mày không chỉ muốn tìm query active, mà còn muốn biết kết nối nào đang ở trạng thái đặc biệt, ví dụ idle hoặc idle in transaction. Cái này giúp phát hiện vấn đề tiềm ẩn trước khi nó thành nghiêm trọng.

Ví dụ query tìm transaction đang ở idle in transaction:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Trường state_change cho biết lúc nào trạng thái này được thiết lập. Nhờ đó mày tìm được transaction "sống dai" mà chẳng làm gì, nhưng lại khoá tài nguyên DB.

Ứng dụng thực tế

Monitor query lâu trên production: mày có thể set up monitor định kỳ cho các query vượt quá ngưỡng thời gian nhất định, rồi gửi cảnh báo qua Slack, Telegram hoặc bất kỳ tool nào. Như vậy sẽ phản ứng nhanh khi có vấn đề hiệu năng.

Phân tích query khi có sự cố: nếu server bắt đầu lag, việc đầu tiên là vào pg_stat_activity để tìm nguyên nhân. Đây nên là quy trình chuẩn khi xử lý sự cố hiệu năng.

Bảo trì database: thường xuyên phân tích pg_stat_activity giúp mày phát hiện query kém hiệu quả để tối ưu (ví dụ thêm index hoặc viết lại query).

Khi monitor, lỗi có thể xuất hiện do lọc hoặc hiểu sai dữ liệu. Ví dụ, nếu chỉ lọc theo trạng thái active, mày có thể bỏ sót mấy query ở trạng thái idle in transaction, mà tụi nó cũng có thể gây khoá tài nguyên. Một lỗi nữa là kill process quá "tay to", dễ gây rollback transaction và mất dữ liệu. Luôn phân tích kỹ trước khi làm gì mạnh tay nha.

Kỹ thuật monitor nâng cao

Để monitor nâng cao hơn, mày có thể viết query phức tạp để show thống kê theo user, database hoặc loại query. Ví dụ, có thể xem mỗi user trung bình tốn bao lâu cho query, hoặc tìm database có nhiều kết nối active nhất.

Cũng nên set up log tự động cho query lâu vào file log của PostgreSQL, dùng config log_min_duration_statement và log_statement. Nhờ đó mày có thể phân tích vấn đề hiệu năng sau này và phát hiện pattern trong hành vi app.