Các lệnh monitoring cơ bản trong PostgreSQL — pg_stat_activity và pg_stat_user_tables

Monitoring database mà không có pg_stat_activity và pg_stat_user_tables thì giống như kiểm tra sức khỏe mà chỉ nhìn mỗi nhiệt độ thôi ấy. Bạn sẽ không thể biết vấn đề nằm ở đâu nếu chỉ nhìn tổng thể. Hai lệnh chủ chốt này của PostgreSQL sẽ giúp bạn không chỉ quan sát mà còn chủ động phân tích xem database của mình đang có gì diễn ra.

pg_stat_activity là gì?

pg_stat_activity là một view hệ thống của PostgreSQL, nó show info về tất cả các kết nối tới database của bạn. Nó trả lời các câu hỏi như: ai đang kết nối vào database, query nào đang chạy ngay lúc này, và kết nối nào đang "treo" ở trạng thái idle. Đây là công cụ để bạn phân tích hoạt động hiện tại trên server.

Giờ mình giải thích các trường chính trong pg_stat_activity nhé. Trường datname chứa tên database mà client đang kết nối, còn usename là tên user thực hiện kết nối đó. application_name là tên app đang dùng kết nối, client_addr là địa chỉ IP của client kết nối tới server. backend_start là thời điểm client bắt đầu kết nối, state thể hiện trạng thái hiện tại của kết nối (active, idle, idle in transaction), còn query là query đang chạy hoặc vừa chạy gần nhất.

Ví dụ 1: Xem tất cả kết nối đang active

Để xem các kết nối đang active, chạy query sau:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

Lưu ý trường query nhé. Nó show các query đang chạy. Nếu query nào chạy quá lâu thì có thể nó đang có vấn đề đấy.

Ví dụ 2: Phân tích trạng thái transaction

Đôi khi có những kết nối bị "kẹt" ở trạng thái idle in transaction. Nghĩa là transaction đã bắt đầu nhưng chưa kết thúc, và điều này có thể gây ra lock.

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

Làm sao để xử lý? Nếu bạn phát hiện transaction bị "treo", có thể kết thúc nó bằng lệnh sau:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Một số dev thì hơi lạm dụng vụ này. Mình khuyên là nên hỏi team trước khi "kill" process nhé. À nhầm, ý mình là — kết thúc kết nối ấy mà.

Monitoring việc sử dụng bảng: view pg_stat_user_tables

Nếu pg_stat_activity giúp bạn theo dõi kết nối, thì pg_stat_user_tables sẽ kể cho bạn nghe về hiệu năng của các bảng. Nhờ nó, bạn biết được: dữ liệu trong bảng được đọc/ghi thường xuyên thế nào, bảng nào được dùng nhiều nhất, và chỗ nào có thể có vấn đề hiệu năng.

Đây là các trường chính của pg_stat_user_tables giúp bạn phân tích bảng. relname là tên bảng, seq_scan là số lần scan tuần tự bảng, idx_scan là số lần scan dùng index. n_tup_ins là số dòng được insert vào bảng, n_tup_upd là số dòng được update, còn n_tup_del là số dòng bị xóa khỏi bảng.

Ví dụ 1: So sánh việc dùng index và scan tuần tự

Nếu index ít được dùng (idx_scan gần bằng 0), khả năng cao là query tới bảng này cần tối ưu lại.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Ví dụ kết quả:

Nếu bạn thấy bảng orders có rất nhiều lần scan tuần tự (seq_scan), hãy nghĩ tới việc thêm index. Ví dụ bảng orders có 3500 lần scan tuần tự mà chỉ có 100 lần scan qua index, trong khi bảng employees chỉ có 50 lần scan tuần tự nhưng lại có 1000 lần scan qua index — đây là dấu hiệu rõ ràng cần tối ưu.

Ví dụ 2: Phân tích số lượng thao tác với bảng

Để xem dữ liệu trong bảng "sống" thế nào, hãy query info về số dòng được insert, update và delete:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

Bạn sẽ biết được gì? Những bảng có số lần insert (n_tup_ins) và delete (n_tup_del) cao có thể là "điểm nóng" trong database của bạn. Nghĩa là hiệu năng của chúng rất đáng để chú ý.

Ứng dụng thực tế để phân tích hiệu năng: kết hợp dữ liệu từ pg_stat_activity và pg_stat_user_tables

Khi bạn phân tích hiệu năng database, có thể kết hợp dữ liệu từ hai nguồn này. Đầu tiên xác định các query chạy lâu qua pg_stat_activity, sau đó kiểm tra các bảng mà các query đó dùng qua pg_stat_user_tables. Nếu query chạy lâu trên các bảng có seq_scan cao, hãy thử tối ưu query hoặc thêm index.

Ví dụ query:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';