Sử dụng pg_stat_statements để giám sát câu lệnh trên PostgreSQL

 1. Giới thiệu

Để giám sát session đang chạy trên PostgreSQL, thông thường chúng ta sử dụng view pg_catalog.pg_stat_activity. Tuy nhiên, để theo dõi kỹ hơn các chỉ số cho từng câu lệnh, thì pg_stat_activity lại không có đủ thông tin.

 

 

Đó là 1 công cụ có thể nói là không thể thiếu, nếu như bạn đang quản trị 1 database PostgreSQL. Nó cho phép bạn theo dõi hoạt động của các câu lệnh đang diễn ra trong database.

Trong bài viết này, tôi sẽ hướng dẫn bạn cách cài đặt công cụ pg_stat_statements rất hữu ích này nhé.

2. Cài đặt pg_stat_statements

alter system set shared_preload_libraries = 'pg_stat_statements';

Tham số này sẽ cho phép pg_stat_statement truy cập vào các khu vực trong PostgreSQL để pg_stat_statement có thể hoạt động.

Và restart lại database để giá trị mới của tham số có hiệu lực.

-bash-4.1$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2021-08-12 09:59:10.206 +07 [4490] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-08-12 09:59:10.206 +07 [4490] LOG:  listening on IPv6 address "::", port 5432
2021-08-12 09:59:10.207 +07 [4490] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-08-12 09:59:10.208 +07 [4490] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-08-12 09:59:10.416 +07 [4490] LOG:  redirecting log output to logging collector process
2021-08-12 09:59:10.416 +07 [4490] HINT:  Future log output will appear in directory "log".
 done
server started

 

2. Cài đặt extension

Bây giờ bạn có thể cài đặt extension pg_stat_statements cho từng database. Bạn chú ý cụm từ cho từng database nhé, nôm na là, bạn muốn sử dụng pg_stat_statement cho database nào thì enable nó ở database đó.

Ví dụ ở đây tôi sẽ enable nó ở trong database mysite_dev của tôi:

-bash-4.1$ psql 
psql (13.3)
Type "help" for help.

postgres=# c mysite_dev
You are now connected to database "mysite_dev" as user "postgres".
mysite_dev=# 
mysite_dev=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
mysite_dev=#

Chú ý: Công cụ này nằm trong gói postgresql13-contrib (tùy vào phiên bản thì con số sẽ thay đổi tương ứng nhé). Thường nó đã được cài đặt khi bạn cài đặt PostgreSQL rồi.

Để cài đặt nó, rất đơn giản thôi, bạn gõ lệnh sau:

yum install postgresql13-contrib

Như vậy là xong rồi đó.

3. Cách sử dụng pg_stat_statements

Ngay khi pg_stat_statements được cài đặt, nó sẽ âm thầm hoạt động ở chế độ background. Pg_stat_statements ghi lại các truy vấn được chạy trên database của bạn.

Và nó cũng tạo ra 1 cái view để cho bạn dễ dàng kiểm tra dữ liệu của nó

 

Bây giờ tôi thử select view này nhé. Ở đây tôi dùng công cụ DBeaver để nhìn cho đẹp.

select * from pg_stat_statements;

 

Bạn sẽ thấy thông tin chi tiết cho từng câu lệnh. Dưới đây là ý nghĩa 1 vài cột mình nghĩ là quan trọng và dễ hiểu nhất:

Tên cột	Ý nghĩa
userid	OID của user chạy câu lệnh
dbid	OID của database mà câu lệnh đang chạy
queryid	Hash value của câu lệnh
query	Nội dung câu lệnh
calls	Số lần chạy của câu lệnh
total_exec_time	Tổng thời gian chạy câu lệnh (cộng dồn theo số lần chạy), tính theo milisecond
min_exec_time	Thời gian 1 lần chạy nhanh nhất, tính theo milisecond
max_exec_time	Thời gian 1 lần chạy lâu nhất, tính theo milisecond
wal_records	Số lượng WAL record sinh ra bởi câu lệnh

 

Nó còn nhiều cột khác nữa, để hiểu toàn bộ ý nghĩa đầy đủ, bạn tham khảo thêm tại đây nhé:

https://www.postgresql.org/docs/current/pgstatstatements.html

Nhìn thông tin cũng khá hữu ích đúng không? Nhưng bạn thấy số liệu trong pg_stat_statements không cho phép bạn xem 1 thời điểm trong quá khứ. Ví dụ: Bạn muốn xem tại thời điểm 2h – 3h đêm hôm qua, có câu lệnh nào đang chạy chiếm nhiều tài nguyên.

Vấn đề này sẽ được giải đáp ở bài viết dưới đây của tôi, mời các bạn đọc nhé:

3. Tinh chỉnh pg_stat_statement

Sau khi cài đặt xong, bạn nên thiết lập lại 1 chút cấu hình pg_stat_statement để sử dụng hiệu quả hơn. Theo kinh nghiệm của mình thì như sau

1. pg_stat_statements.max

Tham số này quy định số câu lệnh tối đa được lưu trong pg_stat_statements. Mặc định thì có 5000 câu thì phải, hơi ít, bạn nên tăng con số này lên

alter system set pg_stat_statements.max=10000;

2. pg_stat_statements.track

Tham số này có các giá trị: none, top, all. Mặc định là top, nghĩa là bạn chỉ có thể giám sát những câu lệnh độc lập, còn những câu lệnh mà chạy ở bên trong 1 thủ tục thì không được. Bạn nên thay đổi tham số này thành all, để có thông tin đầy đủ nhất.

alter system set pg_stat_statements.track = 'all';

 

Sau khi thay đổi các  tham số này, bạn cần phải restart lại database cluster để các giá trị mới có hiệu lực.