Tập Thống Kế Thống Kê PostgreSQL

pg_stat_user_tables và pg_stat_user_indexes

Trong PostgreSQL, pg_stat_user_tables và pg_stat_user_indexes là hai chế độ xem hệ thống quan trọng được sử dụng để giám sát hoạt động quét bảng và sử dụng chỉ mục. Chúng thuộc về chức năng Bộ Thu Thập Thống Kế (Statistics Collector) của PostgreSQL, mặc định được kích hoạt (cần đảm bảo tham số track_counts được đặt là on). Dưới đây là giải thích chi tiết và cách sử dụng của chúng:

1. `pg_stat_user_tables`

Chức năng

Ghi lại thông tin thống kê về việc quét các bảng của người dùng trong cơ sở dữ liệu hiện tại (không bao gồm bảng hệ thống).

Các trường thường dùng

Tên trường	Kiểu dữ liệu	Mô tả
`ten\_schema`	`name`	Tên schema chứa bảng (ví dụ: `public`)
`ten\_bang`	`name`	Tên bảng
`quet\_toan\_bo`	`bigint`	Số lần quét toàn bộ bảng (Sequential Scan)
`dong\_doc\_quet`	`bigint`	Số dòng được đọc khi quét toàn bộ bảng
`quet\_chi\_muc`	`bigint`	Số lần quét bằng chỉ mục (Index Scan)
`dong\_lay\_tu\_chi\_muc`	`bigint`	Số dòng được lấy thông qua quét chỉ mục
`dong\_chen`	`bigint`	Số dòng được chèn vào bảng
`dong\_cap\_nhat`	`bigint`	Số dòng được cập nhật
`dong\_xoa`	`bigint`	Số dòng bị xóa
`dong\_song`	`bigint`	Số dòng hiện còn trong bảng (ước tính)
`dong\_chet`	`bigint`	Số dòng chết (đã xóa nhưng chưa được thu hồi) trong bảng (ước tính)
`cuot\_vacuum\_cuoi`	`timestamp`	Thời điểm lần cuối cùng chạy thủ công `VACUUM`
`cuot\_vacuum\_tu\_dong\_cuoi`	`timestamp`	Thời điểm lần cuối cùng chạy tự động `VACUUM`
`cuot\_analyze\_cuoi`	`timestamp`	Thời điểm lần cuối cùng chạy thủ công `ANALYZE`
`cuot\_analyze\_tu\_dong\_cuoi`	`timestamp`	Thời điểm lần cuối cùng chạy tự động `ANALYZE`

Ví dụ truy vấn

(1) Xem tần suất quét bảng

SELECT 
    ten_schema,
    ten_bang,
    quet_toan_bo AS so_lan_quet_toan_bo,
    quet_chi_muc AS so_lan_quet_chi_muc,
    round(quet_chi_muc * 100.0 / NULLIF(quet_toan_bo + quet_chi_muc, 0), 2) AS ty_le_quet_chi_muc
FROM 
    pg_stat_user_tables
WHERE 
    ten_schema = 'public'  -- Thay bằng schema của bạn
ORDER BY 
    quet_chi_muc DESC NULLS LAST;  -- Sắp xếp theo số lần quét chi mục giảm dần

Giải thích:

ty_le_quet_chi_muc tính tỷ lệ phần trăm quét bằng chỉ mục, giúp xác định bảng có được truy vấn hiệu quả hay không.
Nếu quet_toan_bo cao hơn nhiều so với quet_chi_muc, có thể thiếu chỉ mục phù hợp.

(2) Kiểm tra dòng chết và tình trạng tự động dọn dẹp

SELECT 
    ten_schema,
    ten_bang,
    dong_chet AS so_dong_chet,
    cuot_vacuum_tu_dong_cuoi,
    pg_size_pretty(pg_total_relation_size(ten_schema || '.' || ten_bang)) AS kich_thuoc_tong
FROM 
    pg_stat_user_tables
WHERE 
    dong_chet > 10000  -- Số dòng chết vượt quá 10,000
ORDER BY 
    dong_chet DESC;

Giải thích:

Dòng chết (dong_chet) là những dòng đã bị xóa nhưng chưa được VACUUM thu hồi, có thể gây ra sự phình to của bảng.
Kết hợp cuot_vacuum_tu_dong_cuoi để kiểm tra việc dọn dẹp tự động có hoạt động bình thường hay không.

2. `pg_stat_user_indexes`

Chức năng

Ghi lại thông tin thống kê về việc sử dụng các chỉ mục của người dùng trong cơ sở dữ liệu hiện tại (không bao gồm chỉ mục của bảng hệ thống).

Các trường thường dùng

Tên trường	Kiểu dữ liệu	Mô tả
`ten\_schema`	`name`	Tên schema chứa chỉ mục
`ten\_bang`	`name`	Tên bảng chứa chỉ mục
`ten\_chi\_muc`	`name`	Tên chỉ mục
`lan\_quet\_chi\_muc`	`bigint`	Số lần chỉ mục được quét
`dong\_doc\_tu\_chi\_muc`	`bigint`	Số dòng được đọc từ chỉ mục
`dong\_lay\_tu\_bang`	`bigint`	Số dòng được lấy từ bảng (có thể khác với `dong\_doc\_tu\_chi\_muc`, ví dụ như quét bao phủ chỉ mục)

Ví dụ truy vấn

(1) Xem tần suất sử dụng chỉ mục

SELECT 
    ten_schema,
    ten_bang AS ten_bang_chua_chi_muc,
    ten_chi_muc,
    lan_quet_chi_muc AS so_lan_quet,
    pg_size_pretty(pg_relation_size(ten_chi_muc::regclass)) AS kich_thuoc_chi_muc
FROM 
    pg_stat_user_indexes
WHERE 
    ten_schema = 'public'  -- Thay bằng schema của bạn
ORDER BY 
    lan_quet_chi_muc DESC NULLS LAST;  -- Sắp xếp theo số lần quét giảm dần

Giải thích:

Chỉ mục có số lần quét (lan_quet_chi_muc) thấp có thể là dư thừa, có thể xem xét xóa.
Kết hợp pg_relation_size để xem kích thước chỉ mục, tối ưu hóa lưu trữ.

(2) Kiểm tra chỉ mục không sử dụng

SELECT 
    ten_schema,
    ten_bang AS ten_bang_chua_chi_muc,
    ten_chi_muc,
    lan_quet_chi_muc AS so_lan_quet
FROM 
    pg_stat_user_indexes
WHERE 
    ten_schema = 'public'
    AND lan_quet_chi_muc = 0  -- Chỉ mục chưa từng được quét
ORDER BY 
    pg_relation_size(ten_chi_muc::regclass) DESC;  -- Sắp xếp theo kích thước chỉ mục giảm dần

Giải thích:

Chỉ mục không sử dụng (lan_quet_chi_muc = 0) sẽ chiếm dung lượng và làm giảm hiệu suất ghi, nên cân nhắc xóa.
Tuy nhiên, cần xác định xem đó có phải là chỉ mục dự phòng hoặc cho các truy vấn trong tương lai hay không.

(3) Phân tích hiệu suất quét chỉ mục

SELECT 
    ten_schema,
    ten_bang AS ten_bang_chua_chi_muc,
    ten_chi_muc,
    lan_quet_chi_muc,
    dong_doc_tu_chi_muc,
    dong_lay_tu_bang,
    CASE 
        WHEN dong_lay_tu_bang = 0 THEN 0
        ELSE round(dong_doc_tu_chi_muc * 100.0 / dong_lay_tu_bang, 2)
    END AS ty_le_doc_lay  -- Tỷ lệ giữa số dòng đọc và số dòng lấy
FROM 
    pg_stat_user_indexes
WHERE 
    ten_schema = 'public'
ORDER BY 
    ty_le_doc_lay DESC;

Giải thích:

ty_le_doc_lay gần 100% cho biết quét bao phủ chỉ mục (hiệu quả).
Tỷ lệ thấp có thể có nghĩa là khả năng chọn lọc của chỉ mục kém hoặc truy vấn cần truy cập lại bảng nhiều dữ liệu.

3. Kết hợp `pg_stat_user_tables` và `pg_stat_user_indexes`

Ví dụ: Phân tích tình trạng sử dụng chỉ mục của bảng

SELECT 
    t.ten_schema,
    t.ten_bang,
    t.quet_toan_bo AS so_lan_quet_toan_bo,
    t.quet_chi_muc AS so_lan_quet_chi_muc_tren_bang,
    i.ten_chi_muc,
    i.lan_quet_chi_muc AS so_lan_quet_chi_muc,
    pg_size_pretty(pg_relation_size(i.ten_chi_muc::regclass)) AS kich_thuoc_chi_muc
FROM 
    pg_stat_user_tables t
LEFT JOIN 
    pg_stat_user_indexes i ON t.ten_schema = i.ten_schema AND t.ten_bang = i.ten_bang
WHERE 
    t.ten_schema = 'public'
ORDER BY 
    t.ten_bang, i.lan_quet_chi_muc DESC NULLS LAST;

4. Lưu ý quan trọng

Đặt lại thống kê:

Thống kê sẽ được đặt lại khi khởi động lại cơ sở dữ liệu hoặc thực thi pg_stat_reset().
Có thể sử dụng pg_stat_reset_shared('bgwriter') để đặt lại một phần thống kê.

Tự động dọn dẹp (Autovacuum):

Đảm bảo autovacuum hoạt động bình thường, tránh tích tụ dòng chết ảnh hưởng đến hiệu suất truy vấn.

Bảo trì chỉ mục:

Thường xuyên kiểm tra các chỉ mục không sử dụng (lan_quet_chi_muc = 0) và xóa chúng.
Đối với các chỉ mục được quét thường xuyên nhưng có khả năng chọn lọc thấp, cân nhắc xây dựng lại hoặc tối ưu hóa truy vấn.

Mở rộng thống kê:

Đối với các truy vấn phức tạp, hãy kích hoạt track_io_timing và track_functions để có dữ liệu hiệu suất chi tiết hơn.

Tóm tắt

pg_stat_user_tables: Giám sát cách quét bảng (toàn bộ bảng so với chỉ mục), thay đổi dòng và tình trạng dòng chết.
pg_stat_user_indexes: Giám sát tần suất sử dụng và hiệu suất của chỉ mục, xác định chỉ mục dư thừa.
Sử dụng kết hợp: Phân tích liên kết để tối ưu hóa thiết kế bảng và chiến lược chỉ mục, nâng cao hiệu suất truy vấn.

Bằng cách kiểm tra thường xuyên các chế độ xem này, bạn có thể nhanh chóng xác định các điểm nghẽn hiệu suất của cơ sở dữ liệu và tiến hành tối ưu hóa.

pg_stat_statements

pg_stat_statements là một tiện ích mở rộng giám sát hiệu suất cốt lõi trong PostgreSQL, được sử dụng để theo dõi và phân tích thông tin thống kê thực thi câu lệnh SQL, giúp quản trị viên nhanh chóng xác định các điểm nghẽn hiệu suất. Dưới đây là giải thích chi tiết về các chức năng cốt lõi, phương pháp cấu hình và kịch bản sử dụng của nó:

Một. Chức năng cốt lõi

Tổng hợp thống kê thực thi

Ghi lại số lần thực thi, tổng thời gian, thời gian trung bình/thời gian tối đa/thời gian tối thiểu của tất cả các câu lệnh SQL, tự động chuẩn hóa các truy vấn tương tự (ví dụ: truy vấn với các tham số khác nhau được gộp thống kê), tránh làm phình to nhật ký.
Ví dụ truy vấn: ``` SELECT cau_lenh, so_lan_thuc_thi, tong_thoi_gian, thoi_gian_trung_binh FROM pg_stat_statements ORDER BY tong_thoi_gian DESC LIMIT 10;


Trả về 10 câu lệnh có tổng thời gian thực thi lâu nhất, nhanh chóng xác định các điểm nóng hiệu suất.
2. **Phân tích tiêu thụ tài nguyên**


- Theo dõi tiêu thụ I/O (như `thoi_gian_doc_vung_nho`, `thoi_gian_vung_nho`), tỷ lệ命中 bộ nhớ dùng chung (`vung_nho_dung_chung_hit`), sử dụng bộ đệm tạm thời, v.v., hỗ trợ tối ưu hóa kế hoạch truy vấn.
- **Ví dụ**: ```
SELECT cau_lenh, 
       round(thoi_gian_doc_vung_nho + thoi_gian_vung_nho, 2) AS thoi_gian_io 
FROM pg_stat_statements 
ORDER BY thoi_gian_io DESC 
LIMIT 5;

Lọc ra các truy vấn tập trung vào I/O, tối ưu hóa chỉ mục hoặc chiến lược phân vùng. 3. Lưu trữ bền vững

Mặc định giữ lại thông tin thống kê sau khi khởi động lại máy chủ (thông qua cấu hình pg_stat_statements.save = on), hỗ trợ phân tích xu hướng dài hạn.

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

Kích hoạt tiện ích mở rộng

Chỉnh sửa postgresql.conf: ``` shared_preload_libraries = 'pg_stat_statements' # Tải trước module pg_stat_statements.max = 10000 # Số tối đa câu lệnh theo dõi (mặc định 5000) pg_stat_statements.track = all # Theo dõi câu lệnh cấp cao và lồng nhau pg_stat_statements.track_utility = on # Theo dõi các câu lệnh DDL và công cụ

- **Khởi động lại dịch vụ PostgreSQL** để cấu hình có hiệu lực.
2. **Tạo tiện ích mở rộng**
Thực thi trong cơ sở dữ liệu mục tiêu:

CREATE EXTENSION pg_stat_statements;

3. **Đặt lại dữ liệu thống kê**

SELECT pg_stat_statements_reset(); -- Xóa dữ liệu thống kê hiện tại


### **Ba. Kịch bản sử dụng điển hình**

1. **Xác định truy vấn chậm**


- **Kịch bản**: Người dùng phản hồi ứng dụng phản hồi chậm.
- **Thao tác**: ```
SELECT cau_lenh, 
       round(tong_thoi_gian::numeric, 2) AS tong_millis,
       round(thoi_gian_trung_binh::numeric, 2) AS trung_binh_millis
FROM pg_stat_statements
WHERE tong_thoi_gian > 1000  -- Lọc các truy vấn tổng thời gian vượt quá 1 giây
ORDER BY tong_thoi_gian DESC;

Kết quả: Phát hiện một câu lệnh JOIN phức tạp chiếm 80% thời gian, phân tích thêm kế hoạch thực thi thông qua EXPLAIN ANALYZE, tối ưu hóa chỉ mục hoặc viết lại SQL.

Giám sát truy vấn thường xuyên

Kịch bản: Tải cơ sở dữ liệu tăng đột ngột.
Thao tác: ``` SELECT cau_lenh, so_lan_thuc_thi, round(so_lan_thuc_thi::numeric / extract(epoch FROM now() - pg_postmaster_start_time()) * 60, 2) AS qps FROM pg_stat_statements ORDER BY so_lan_thuc_thi DESC LIMIT 10;

- **Kết quả**: Nhận diện các truy vấn nhẹ nhàng được gọi hơn 1000 lần mỗi giây, xem xét cache hoặc xử lý hàng loạt.
3. **Xác minh tối ưu hóa chỉ mục**


- **Kịch bản**: Cần xác minh hiệu quả sau khi tạo chỉ mục mới.
- **Thao tác**: ```
-- So sánh thời gian truy vấn trước và sau khi tạo chỉ mục
SELECT cau_lenh, 
       round(tong_thoi_gian::numeric, 2) AS tong_millis,
       dong_lay AS so_dong_lay
FROM pg_stat_statements
WHERE cau_lenh LIKE '%FROM orders WHERE user_id%';

Kết quả: Nếu tổng thời gian giảm đáng kể và số dòng quét giảm, chứng tỏ chỉ mục có hiệu quả.

Bốn. Thủ thuật nâng cao

Kết hợp track_io_timing

Trong postgresql.conf, kích hoạt track_io_timing = on để thống kê chính xác thời gian chờ I/O, phân biệt giữa truy vấn tập trung CPU và truy vấn tập trung I/O.

Lưu trữ thống kê định kỳ

Thường xuyên xuất dữ liệu pg_stat_statements ra hệ thống bên ngoài (như Prometheus + Grafana) thông qua script, thực hiện giám thị trực quan và cảnh báo.

Loại trừ truy vấn gây nhiễu

Sử dụng WHERE cau_lenh NOT LIKE '%pg_stat_statements%' để lọc các truy vấn từ công cụ giám sát chính, tránh làm ô nhiễm dữ liệu.

Năm. Lưu ý quan trọng

Tổn thất hiệu suất: Theo dõi tất cả các câu lệnh sẽ tăng khoảng 5%-10% tải CPU, trong môi trường sản xuất, chỉ nên kích hoạt track_utility khi cần thiết.
Chuẩn hóa câu lệnh: Các câu lệnh tham số hóa (như WHERE id = $1) sẽ được gộp thống kê, nếu cần phân tích chính xác các truy vấn với tham số cụ thể, cần kết hợp nhật ký hoặc tiện ích mở rộng auto_explain.
Kiểm soát quyền: Chỉ người dùng siêu cấp có thể sửa đổi tham số cấu hình, người dùng thông thường cần truy cập chế độ xem thông qua GRANT.

Thẻ: PostgreSQL thống kê giám sát hiệu suất chỉ mục truy vấn

Đăng vào ngày 6 tháng 6 lúc 02:00

Thành phố Cuồng loạn

Tập Thống Kế Thống Kê PostgreSQL

pg_stat_user_tables và pg_stat_user_indexes

1. `pg_stat_user_tables`

Chức năng

Các trường thường dùng

Ví dụ truy vấn

(1) Xem tần suất quét bảng

(2) Kiểm tra dòng chết và tình trạng tự động dọn dẹp

2. `pg_stat_user_indexes`

Chức năng

Các trường thường dùng

Ví dụ truy vấn

(1) Xem tần suất sử dụng chỉ mục

(2) Kiểm tra chỉ mục không sử dụng

(3) Phân tích hiệu suất quét chỉ mục

3. Kết hợp `pg_stat_user_tables` và `pg_stat_user_indexes`

Ví dụ: Phân tích tình trạng sử dụng chỉ mục của bảng

4. Lưu ý quan trọng

Tóm tắt

pg_stat_statements

Một. Chức năng cốt lõi

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

Bốn. Thủ thuật nâng cao

Năm. Lưu ý quan trọng

Thẻ Phổ Biến

Thành phố Cuồng loạn

Tập Thống Kế Thống Kê PostgreSQL

pg_stat_user_tables và pg_stat_user_indexes

1. pg_stat_user_tables

Chức năng

Các trường thường dùng

Ví dụ truy vấn

(1) Xem tần suất quét bảng

(2) Kiểm tra dòng chết và tình trạng tự động dọn dẹp

2. pg_stat_user_indexes

Chức năng

Các trường thường dùng

Ví dụ truy vấn

(1) Xem tần suất sử dụng chỉ mục

(2) Kiểm tra chỉ mục không sử dụng

(3) Phân tích hiệu suất quét chỉ mục

3. Kết hợp pg_stat_user_tables và pg_stat_user_indexes

Ví dụ: Phân tích tình trạng sử dụng chỉ mục của bảng

4. Lưu ý quan trọng

Tóm tắt

pg_stat_statements

Một. Chức năng cốt lõi

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

Bốn. Thủ thuật nâng cao

Năm. Lưu ý quan trọng

Thẻ Phổ Biến

1. `pg_stat_user_tables`

2. `pg_stat_user_indexes`

3. Kết hợp `pg_stat_user_tables` và `pg_stat_user_indexes`