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
1. Cấu hình tham số shared_preload_libraries
Đầu tiên bạn phải cấu hình tham số shared_preload_libraries trước bằng câu lệnh sau:
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.
Tuy nhiên, nếu bạn bị lỗi ở bước create extension ở trên, cũng có thể là do gói postgresql13-contrib chưa được cài đặt.
Để 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.
