性能分析工具pg_stat_monitor的使用

简介

pg_stat_monitor 是 PostgreSQL 的查询性能监控工具。它试图通过在单个视图中提供急需的query performance insights 来提供更全面的信息。pg_stat_monitor 是在 pg_stat_statements 的基础上开发的，作为其更高级的替代品。
虽然 pg_stat_statements 提供了不断增加的指标，但 pg_stat_monitor 聚合了收集的数据，从而节省了用户自己做的工作。 pg_stat_monitor 以可配置的基于时间的单位存储统计信息——存储桶。这允许专注于为较短时间段生成的统计信息，并使查询时间信息（例如最大/最小/平均时间）更加准确。

pg_stat_monitor 支持 PostgreSQL 版本 11 及以上。它与 PostgreSQL Global Development Group (PGDG) 提供的 PostgreSQL 和 Percona Distribution for PostgreSQL 兼容。

特性

pg_stat_monitor 通过从性能、应用程序和分析角度提供更全面的查询视图来简化查询可观察性。这是通过将数据分组到可配置的时间桶中来实现的，这些时间桶允许捕获较小时间窗口的负载和性能信息。因此，可以根据时间和工作量来识别性能问题和模式。
• 时间间隔分组： pg_stat_monitor 不是提供一组不断增加的计数，而是计算配置数量的时间间隔 - 时间桶的统计信息。这允许更好的数据准确性，特别是在高分辨率或不可靠网络的情况下。
• 多维分组：虽然 pg_stat_statements 按 userid、dbid、queryid 对计数器进行分组，但 pg_stat_monitor 使用更详细的组来获得更高的精度。这允许用户深入了解查询的性能。
◇ 存储桶 ID（存储桶），
◇ 用户 ID（用户 ID），
◇ 数据库 ID (dbid),
◇ 查询 ID（queryid），
◇ 客户端 IP 地址 (client_ip)，
◇ 计划 ID (planid),
◇ 应用程序名称 (application_name)。
• 捕获查询中的实际参数：pg_stat_monitor 允许您选择是否要查看带有参数占位符的查询或实际参数数据。通过使用户能够执行相同的查询，这简化了调试和分析过程。
• 查询计划：现在，每个 SQL 都附有为执行而构建的实际计划。如果您想了解为什么特定查询比预期慢，这是一个巨大的优势。
• 语句的表访问统计信息：这使我们能够轻松识别访问给定表的所有查询。该集合与 pg_stat_statements 提供的信息相当。
• 直方图：分析SQL在一段时间内的调用频率,这非常有用，因为它可以帮助识别问题。借助直方图功能，现在可以查看响应 SQL 查询的计时/调用数据直方图。是的，它甚至可以在 psql 中使用。
• 关系名称：pg_stat_monitor有专门一列relation用于统计SQL涉及到的表，这样就可以基于该字段只统计感兴趣的高频表了，不过是个数组，得用包含符。
• 函数：这可能会让人感到惊讶，但我们确实理解函数可以在内部执行语句！！！为了帮助简化跟踪和分析，pg_stat_monitor 现在提供了一个列，专门帮助跟踪语句的顶部查询，以便您可以回溯到原始函数。
• 查询类型：查询分类为 SELECT、INSERT、UPDATE 或 DELETE，分析变得更简单。这是您最终减少的另一项工作，也是 pg_stat_monitor 的另一项简化。
• 查询comment元数据：您可以将任何键值数据放在SQL 语句中 /* … */ 语法的注释中，并且该信息将由 pg_stat_monitor 解析并在 pg_stat_monitor 视图的注释列中可用。
• 记录错误和警告：pg_stat_monitor 不仅监控 ERROR/WARNINGS/LOG 还收集有关这些查询的统计信息。在带有 ERROR/WARNING 的 PostgreSQL 查询中，有错误级别 (elevel)、SQL 代码 (sqlcode)，并附有错误消息。Pg_stat_monitor 收集所有这些信息及其聚合。

相关文档
https://docs.percona.com/pg-stat-monitor/index.html
https://github.com/percona/pg_stat_monitor/

安装

测试环境：
操作系统：centos 7.9
数据库：PostgreSQL 14.7
pg_stat_monitor插件：2.0.2

–root用户安装

source /home/postgres/.bash_profile
unzip pg_stat_monitor-2.0.2.zip
cd pg_stat_monitor-2.0.2
make USE_PGXS=1
make USE_PGXS=1 install
1
2
3
4
5

配置

要更改默认配置，请参考官方文档为所需参数指定新值。

[postgres@du101 ~]$ tail -2 /data/pgdata/postgresql.conf 
增加
shared_preload_libraries = 'pg_stat_monitor'
pg_stat_monitor.pgsm_bucket_time= 300s
pg_stat_monitor.pgsm_max_buckets=10
pg_stat_monitor.pgsm_normalized_query= on
pg_stat_monitor.pgsm_enable_query_plan=on
pg_stat_monitor.pgsm_track_planning=on

[postgres@du101 ~]$ pg_ctl restart
[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# create extension pg_stat_monitor;
CREATE EXTENSION
postgres=# SELECT name,setting,unit,short_desc FROM pg_settings WHERE name like 'pg_stat_monitor.%';
                   name                    | setting | unit |                                                           short_desc                                       
                    
-------------------------------------------+---------+------+------------------------------------------------------------------------------------------------------------
--------------------
 pg_stat_monitor.pgsm_bucket_time          | 300     | s    | Sets the time in seconds per bucket.
 pg_stat_monitor.pgsm_enable_overflow      | on      |      | Enable/Disable pg_stat_monitor to grow beyond shared memory into swap space.
 pg_stat_monitor.pgsm_enable_pgsm_query_id | on      |      | Enable/disable PGSM specific query id calculation which is very useful in comparing same query across datab
ases and clusters..
 pg_stat_monitor.pgsm_enable_query_plan    | on      |      | Enable/Disable query plan monitoring.
 pg_stat_monitor.pgsm_extract_comments     | off     |      | Enable/Disable extracting comments from queries.
 pg_stat_monitor.pgsm_histogram_buckets    | 20      |      | Sets the maximum number of histogram buckets.
 pg_stat_monitor.pgsm_histogram_max        | 100000  | ms   | Sets the time in millisecond.
 pg_stat_monitor.pgsm_histogram_min        | 1       | ms   | Sets the time in millisecond.
 pg_stat_monitor.pgsm_max                  | 256     | MB   | Sets the maximum size of shared memory in (MB) used for statement's metadata tracked by pg_stat_monitor.
 pg_stat_monitor.pgsm_max_buckets          | 10      |      | Sets the maximum number of buckets.
 pg_stat_monitor.pgsm_normalized_query     | on      |      | Selects whether save query in normalized format.
 pg_stat_monitor.pgsm_overflow_target      | 1       |      | Sets the overflow target for pg_stat_monitor. (Deprecated, use pgsm_enable_overflow)
 pg_stat_monitor.pgsm_query_max_len        | 2048   |      | Sets the maximum length of query.
 pg_stat_monitor.pgsm_query_shared_buffer  | 20      | MB   | Sets the maximum size of shared memory in (MB) used for query tracked by pg_stat_monitor.
 pg_stat_monitor.pgsm_track                | top     |      | Selects which statements are tracked by pg_stat_monitor.
 pg_stat_monitor.pgsm_track_planning       | on      |      | Selects whether planning statistics are tracked.
 pg_stat_monitor.pgsm_track_utility        | on      |      | Selects whether utility commands are tracked.
(17 rows)

postgres=#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

参数说明：
pgsm_max 和 pgsm_query_shared_buffer 指定占据的共享内存大小，在v13长新增了一个pg_shmem_allocations的视图，可以看到有哪些PostgreSQL自己使用的共享内存段以及插件使用到的

postgres=# select * from pg_shmem_allocations where name like '%monitor%';
               name                |    off    |   size   | allocated_size 
-----------------------------------+-----------+----------+----------------
 pg_stat_monitor: bucket hashtable | 167906944 |     4944 |           4992
 pg_stat_monitor                   | 146935296 | 20971584 |       20971648
(2 rows)

postgres=# 
1
2
3
4
5
6
7
8

enable_query_plan和track_planning 则是跟踪执行计划的，这样的话，追溯历史执行计划的阵营除了pg_show_plans、auto_explain、pg_store_plans外，再添加一员大将pg_stat_monitor。

max_buckets 指定保留多少个快照，bucket_time 指定多久采集获取一次快照，设置300s采集一次，保留10个快照，也就是说最多能追溯到过去50分钟内的状态信息。

pgsm_normalized_query参数开启后，可以记录查询中的实际参数。

更多参数：
https://docs.percona.com/pg-stat-monitor/configuration.html

使用示例

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# create table t1(id int ,info varchar);
CREATE TABLE
postgres=# create table t2(id int ,info varchar); 
CREATE TABLE
postgres=# insert into t1 select id,'test'||id from  generate_series(1,1000000) id;    
INSERT 0 1000000
postgres=# select t1.*,t2.info from t1,t2  where t1.id=t2.id and t1.id=99;
 id |  info  |  info  
----+--------+--------
 99 | test99 | duqk99
(1 row)

postgres=# SELECT application_name, userid AS user_name, datname AS database_name, substr(query,0, 50) AS query, calls, client_ip,relations FROM pg_stat_monitor;
 application_name | user_name | database_name |                       query                       | calls |    client_ip    |         relations         
------------------+-----------+---------------+---------------------------------------------------+-------+-----------------+---------------------------
 psql             |        10 | postgres      | SELECT name,setting,unit,short_desc FROM pg_setti |     1 | 255.255.255.255 | {pg_catalog.pg_settings*}
 psql             |        10 | postgres      | SELECT application_name, userid AS user_name, dat |     1 | 255.255.255.255 | {public.pg_stat_monitor*}
 psql             |        10 | postgres      | select t1.*,t2.info from t1,t2  where t1.id=t2.id |     1 | 255.255.255.255 | {public.t1,public.t2}
(3 rows)

postgres=#  SELECT bucket,bucket_start_time,query, client_ip,relations, query_plan FROM pg_stat_monitor offset 2 limit 1 \gx 
-[ RECORD 1 ]-----+---------------------------------------------------------------
bucket            | 9
bucket_start_time | 2023-10-25 15:25:00+08
query             | select t1.*,t2.info from t1,t2  where t1.id=t2.id and t1.id=$1
client_ip         | 255.255.255.255
relations         | {public.t1,public.t2}
query_plan        | Nested Loop                                                   +
                  |   ->  Gather                                                  +
                  |         Workers Planned: 2                                    +
                  |         ->  Parallel Seq Scan on t1                           +
                  |               Filter: (id = 99)                               +
                  |   ->  Seq Scan on t2                                          +
                  |         Filter: (id = 99)

postgres=# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

修改参数pg_stat_monitor.pgsm_normalized_query为off之后查看；

postgres=# show pg_stat_monitor.pgsm_normalized_query;
 pg_stat_monitor.pgsm_normalized_query 
---------------------------------------
 off
(1 row)

postgres=# insert into t2 values(1,'test data');      
INSERT 0 1
postgres=# SELECT bucket,bucket_start_time,query, client_ip,relations, query_plan FROM pg_stat_monitor \gx 
-[ RECORD 1 ]-----+-------------------------------------------
bucket            | 9
bucket_start_time | 2023-10-25 16:15:00+08
query             | show pg_stat_monitor.pgsm_normalized_query
client_ip         | 255.255.255.255
relations         | 
-[ RECORD 2 ]-----+-------------------------------------------
bucket            | 9
bucket_start_time | 2023-10-25 16:15:00+08
query             | insert into t2 values(1,'test data')
client_ip         | 255.255.255.255
relations         | {public.t2}

postgres=# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23