Giới thiệu về pg_stat_statements: cài đặt và cấu hình extension

Extension pg_stat_statements trong PostgreSQL là một công cụ để thu thập thống kê truy vấn. Nó cho phép bạn xem truy vấn nào được chạy nhiều nhất, truy vấn nào tốn nhiều thời gian nhất, và tài nguyên database được dùng hiệu quả ra sao. Thay vì phải phân tích từng truy vấn thủ công với EXPLAIN, bạn có thể có cái nhìn tổng thể về hiệu năng database.

Lợi ích khi dùng pg_stat_statements:

Giám sát realtime: bạn có thể thấy truy vấn nào đang làm nặng database ngay lúc này.

Phân tích hiệu năng toàn hệ thống: thông tin có cho tất cả truy vấn trong database, không chỉ những truy vấn bạn tự chọn để phân tích.

Tìm truy vấn chậm: dễ dàng xác định truy vấn nào tốn nhiều thời gian nhất.

Phát hiện truy vấn lặp lại: giúp tối ưu cache và thêm index cho các truy vấn phổ biến.

Cài đặt và cấu hình pg_stat_statements

Giờ khi bạn đã hiểu tại sao cần pg_stat_statements, cùng xem cách cài đặt và cấu hình nó từng bước nhé.

1. Kiểm tra PostgreSQL đã sẵn sàng chưa. Hãy chắc là PostgreSQL của bạn hỗ trợ extension pg_stat_statements. Extension này có sẵn từ PostgreSQL 9.2 trở lên. Để kiểm tra, chạy lệnh sau:

SELECT extname FROM pg_extension;

Nếu pg_stat_statements không có trong danh sách, có thể là admin chưa cài nó.

Đây là ví dụ extension đã được cài và active:

2. Thêm extension.

Để kích hoạt pg_stat_statements, bạn cần thêm nó vào danh sách thư viện preload của PostgreSQL. Làm việc này trong file cấu hình postgresql.conf.

Các bước:

1. Tìm file postgresql.conf. Thường nó nằm trong thư mục data của PostgreSQL.
2. Mở file để sửa.
3. Thêm hoặc sửa dòng sau:

   shared_preload_libraries = 'pg_stat_statements'

Tại sao phải làm vậy? Vì pg_stat_statements cần preload để theo dõi truy vấn ở cấp hệ thống.

4. Lưu lại và restart server PostgreSQL để áp dụng thay đổi. Dưới đây là lệnh cho Linux:

   sudo systemctl restart postgresql
   

Nếu bạn dev hoặc test local, chỉ cần restart server là được.

3. Tạo extension trong database. Sau khi restart PostgreSQL, bạn có thể tạo extension pg_stat_statements trong database cụ thể. Kết nối vào database qua psql hoặc tool khác rồi chạy:

CREATE EXTENSION pg_stat_statements;

Nếu mọi thứ ok, lệnh sẽ chạy không lỗi. Giờ pg_stat_statements đã active cho database của bạn.

4. Cấu hình tham số pg_stat_statements.

Sau khi cài extension, nên cấu hình các tham số để thu thập thống kê chuẩn. Các tham số chính chỉnh trong file postgresql.conf.

Các tham số chính

• pg_stat_statements.track
• Xác định truy vấn nào sẽ được theo dõi.
• Giá trị:
  • all — theo dõi tất cả truy vấn (nên dùng khi debug và phân tích).
  • top — chỉ theo dõi truy vấn cấp cao nhất.
  • none — tắt theo dõi.
• Ví dụ cấu hình:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Chỉ định số lượng truy vấn tối đa sẽ lưu trong thống kê.
  • Mặc định: 5000.
  • Nếu hệ thống bạn có nhiều truy vấn, nên tăng lên, ví dụ:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Xác định có lưu thống kê giữa các lần restart server không.
  • Giá trị: on hoặc off.
  • Nên để on:

    pg_stat_statements.save = on
    

Sau khi đổi tham số, hãy restart lại server PostgreSQL.

Kiểm tra hoạt động của pg_stat_statements

Giờ extension đã cài và cấu hình xong, cùng kiểm tra nó hoạt động chưa nhé. Để xem thống kê truy vấn đã thu thập, chạy truy vấn sau:

SELECT
    queryid,        -- Định danh duy nhất của truy vấn
    query,          -- Nội dung truy vấn
    calls,          -- Số lần gọi truy vấn
    total_time,     -- Tổng thời gian thực thi (ms)
    rows            -- Số dòng trả về bởi truy vấn
FROM pg_stat_statements
ORDER BY total_time DESC;

Các cột này nghĩa là gì?

• queryid: định danh duy nhất của truy vấn, hữu ích để tìm các truy vấn giống nhau với tham số khác nhau.
• query: nội dung truy vấn SQL đã chạy.
• calls: số lần truy vấn được gọi.
• total_time: tổng thời gian (cộng tất cả lần gọi truy vấn).
• rows: tổng số dòng trả về bởi truy vấn.

Ví dụ, nếu bạn thấy truy vấn có calls = 100 và total_time = 50000 (50 giây) chiếm phần lớn thời gian hệ thống, đó là dấu hiệu rõ ràng cần tối ưu nó.

Các kịch bản dùng pg_stat_statements phổ biến

1. Tìm truy vấn chậm nhất. Để tìm truy vấn tốn nhiều thời gian nhất, hãy sort theo total_time:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Phát hiện truy vấn chạy nhiều nhất. Để tìm truy vấn được chạy nhiều nhất, sort theo calls:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Phân tích việc dùng index. Nếu bạn thấy nhiều truy vấn chạy chậm, hãy kiểm tra việc dùng index. Ví dụ, truy vấn có WHERE mà không có index thường là lý do hiệu năng kém.

Xoá dữ liệu pg_stat_statements

Đôi khi bạn muốn reset thống kê đã thu thập để bắt đầu phân tích lại từ đầu. Làm việc này bằng lệnh:

SELECT pg_stat_statements_reset();

Sau khi reset, toàn bộ thống kê sẽ bị xoá và bắt đầu thu thập lại từ đầu.

Mẹo thực tế

Giới hạn lượng thống kê thu thập: nếu bạn làm ở hệ thống tải cao với hàng triệu truy vấn, hãy để pg_stat_statements.max ở mức hợp lý để tránh quá tải.

Thường xuyên xoá thống kê: nên làm trước khi bắt đầu phân tích hiệu năng để không lẫn dữ liệu cũ và mới.

Chú ý truy vấn chậm: dù truy vấn đó ít khi chạy, chỉ một lần chậm cũng có thể làm database của bạn nặng nề.

Giờ bạn đã biết cách cài, cấu hình và dùng extension pg_stat_statements để phân tích hiệu năng truy vấn rồi nhé. Ở bài sau tụi mình sẽ đi sâu vào cách tìm truy vấn chậm với extension này và tối ưu chúng.