Ghi log thực thi truy vấn

Như bất kỳ hệ thống phức tạp nào, trong PostgreSQL cũng có lúc mọi thứ không ổn: truy vấn chạy quá lâu, server bị tải nặng, còn user thì bắt đầu sốt ruột. Ghi log là cách để quan sát bên trong database, nhằm:

1. Phát hiện truy vấn chậm — những truy vấn làm chậm app của bạn.
2. Tối ưu hiệu năng — nhờ phân tích hành vi truy vấn.
3. Chẩn đoán lỗi — như vi phạm constraint hoặc sai cú pháp.
4. Đảm bảo audit — theo dõi ai và thao tác gì đã thực hiện trên server.

Các tham số ghi log quan trọng

File cấu hình PostgreSQL (postgresql.conf) có rất nhiều tham số liên quan đến ghi log. Cùng xem qua những cái quan trọng nhất nhé.

1. log_statement — ghi log truy vấn SQL

Tham số log_statement xác định truy vấn SQL nào sẽ được ghi vào log. Nó có thể nhận các giá trị sau:

• none — tắt ghi log truy vấn.
• ddl — chỉ ghi log các lệnh định nghĩa dữ liệu (ví dụ, CREATE, ALTER, DROP).
• mod — ghi log tất cả lệnh thay đổi dữ liệu (ví dụ, INSERT, UPDATE, DELETE).
• all — ghi log mọi truy vấn SQL (kể cả SELECT đơn giản).

Ví dụ:

Để bật ghi log tất cả truy vấn, trong file postgresql.conf hãy đặt:

log_statement = 'all'

Nếu bạn chỉ muốn ghi log các lệnh thay đổi dữ liệu:

log_statement = 'mod'

Sau khi thay đổi cấu hình, nhớ restart lại server nhé:

sudo systemctl restart postgresql

2. log_duration — ghi log thời gian thực thi

Tham số log_duration cho phép ghi vào log thời gian thực thi của từng truy vấn. Cái này hữu ích để biết truy vấn nào tốn thời gian nhất.

Ví dụ:

Để bật ghi log thời lượng thực thi truy vấn, hãy đặt:

log_duration = on

Nếu bạn chỉ muốn ghi log truy vấn chậm, hãy dùng tham số log_min_duration_statement (mình sẽ nói ở phần sau).

3. log_min_duration_statement — ghi log truy vấn chậm

Tham số này cho phép chỉ ghi log những truy vấn có thời gian thực thi lớn hơn giá trị chỉ định (tính bằng mili giây). Cực kỳ tiện để phát hiện "thủ phạm" làm database chậm.

Ví dụ:

Để ghi log các truy vấn chạy lâu hơn 1 giây:

log_min_duration_statement = 1000

Nếu bạn muốn ghi log TẤT CẢ truy vấn bất kể thời lượng, chỉ cần đặt giá trị 0:

log_min_duration_statement = 0

Giá trị -1 sẽ tắt ghi log theo thời lượng.

4. log_line_prefix — cấu trúc message trong log

Tham số log_line_prefix cho phép tùy chỉnh format của message ghi vào log. Cái này tiện để thêm context cho mỗi truy vấn được log (ví dụ, tên user, PID, ngày giờ).

Ví dụ:

Để ghi tên user, database, thời gian và process, hãy dùng:

log_line_prefix = '%t [%p]: [%d]: [%u]: '

Ở đây:

• %t — thời gian truy vấn.
• %p — PID của process.
• %d — tên database.
• %u — tên user.

Danh sách tất cả option: Tài liệu PostgreSQL.

5. logging_collector — bộ thu thập log

Tham số logging_collector bật cơ chế ghi log ra file. Nếu tắt, log chỉ gửi ra stdout, khá bất tiện.

Để bật bộ thu thập log:

logging_collector = on

Nhớ chỉ định đường dẫn file log bằng log_directory và log_filename:

log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'

Cách dùng tham số trong thực tế

Cùng xem cách dùng các tham số log trong tình huống thực tế nhé.

Tình huống 1: ghi log tất cả truy vấn

Nếu bạn mới bắt đầu với database mới và muốn thấy mọi thứ đang diễn ra, hãy cấu hình:

log_statement = 'all'
log_line_prefix = '%t [%p]: [%d]: [%u]: '

Bây giờ mỗi truy vấn thực thi trong database sẽ được ghi vào file log — rất tiện để debug!

Tình huống 2: tìm truy vấn chậm

Nếu bạn thấy server đôi khi "lag", muốn tìm truy vấn gây vấn đề, hãy cấu hình:

log_min_duration_statement = 500  # Ghi log truy vấn lâu hơn 500 ms
log_line_prefix = '%t [%p]: [%d]: [%u]: [%r] '

Trong đó %r thêm địa chỉ IP client. Sau khi cấu hình, bạn sẽ dễ dàng phát hiện truy vấn làm server quá tải.

Tình huống 3: chế độ ghi log tối thiểu

Nếu server của bạn chạy ổn định, chỉ cần log tối thiểu để audit:

log_statement = 'mod'
log_duration = off

Lúc này chỉ log các thay đổi dữ liệu (ví dụ, INSERT, UPDATE).

Phân tích log

Khi log bắt đầu đầy dữ liệu, quan trọng là biết cách phân tích nó! Bạn có thể:

Mở file log bằng editor:

cat /var/log/postgresql/postgresql.log

Dùng tool như grep để tìm truy vấn chậm:

grep "duration: " /var/log/postgresql/postgresql.log

Dùng các tool phân tích log nâng cao như pgBadger.

Mẹo hữu ích

Ghi log quá chi tiết có thể làm tăng tải ổ cứng. Chỉ dùng các tham số cần thiết cho hệ thống production thôi nhé.

Thường xuyên dọn dẹp hoặc lưu trữ log cũ để tránh đầy ổ cứng.

Ghi log hiệu quả là dùng các tham số chỉ thu thập dữ liệu cần thiết. Ví dụ, log_min_duration_statement giúp bạn chính xác và tiết kiệm tài nguyên.

Sao nào? Giờ thì việc cấu hình log trong PostgreSQL và dùng nó để phân tích truy vấn, tăng hiệu năng không còn là vấn đề nữa. Như một (thật ra không chỉ một) dev giỏi từng nói: "Log là cơ hội để nhìn lại quá khứ truy vấn và sửa tương lai database".