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.

pg_stat_statements là một tiện ích mở rộng (extension), thường đi kèm ngay trong các bản PostgreSQL.

Đó 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;

Đọc thêm Review công cụ quản trị PostgreSQL – DBeaver

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é:

Đọc thêm PostgreSQL – Làm sao để xem hiệu năng trong quá khứ của câu lệ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.

Đọc thêm Cấu hình tham số trong PostgreSQL

Bài viết cũng dài rồi, tôi xin dừng lại tại đây, nếu có câu hỏi hay thắc mắc nào, các bạn có thể để lại comment bên dưới, hoặc tham gia vào group “Kho tài liệu kiến thức database” nhé”. Hẹn gặp lại các bạn ở bài viết tiếp theo.

Nguồn: https://dangxuanduy.com/

Hiện tại, tôi có tổ chức đều đặn các khóa học về quản trị Oracle Database, tôi sẽ để thông tin ở đây, để bạn nào quan tâm về lịch học cũng như chương trình học có thể theo dõi nhé.

KHOÁ DÀNH CHO NGƯỜI MỚI

KHÓA HỌC: QUẢN TRỊ ORACLE DATABASE THẬT LÀ ĐƠN GIẢN (ADMIN 1)

CÁC KHOÁ NÂNG CAO:

KHÓA HỌC ORACLE NÂNG CAO: QUẢN TRỊ KIẾN TRÚC MULTITENANT 12c

KHÓA HỌC ORACLE NÂNG CAO: QUẢN TRỊ HỆ THỐNG DATA GUARD

CÁC KHOÁ COMBO:

COMBO 1: ADMIN 1 + MULTITENANT 12c

COMBO 2: ADMIN 1 + DATA GUARD

COMBO 3: ADMIN 1 + MULTITENANT 12c + DATA GUARD

LỊCH HỌC:

Mời bạn xem tại đây: LỊCH HỌC CÁC LỚP ORACLE

ĐĂNG KÝ: