PostgreSQL性能监控工具之pg_stat_monitor插件

pg_stat_monitor是一个基于pg_stat_statements模块的PostgreSQL查询性能监控工具。pg_stat_monitor提供聚合统计信息、客户端信息、计划详细信息(包括计划)和直方图信息。

安装pg_stat_monitor插件

#下载并编译pg_stat_monitor插件
$ wget https://github.com/percona/pg_stat_monitor/archive/refs/tags/1.0.0.tar.gz
$ tar -zxf 1.0.0.tar.gz 
$ cd pg_stat_monitor-1.0.0/
$ make install USE_PGXS=1 pg_config=/opt/pgsql/bin/pg_config
修改
$ cd $PGDATA
$ vi postgresql.conf 
$ pg_ctl restart
$ psql
psql (13.6)
Type "help" for help.

postgres=# create extension pg_stat_monitor;
CREATE EXTENSION
postgres=# \dx
                                                                                                                                    List of installed extensions
      Name       | Version |   Schema   |                                                                                                                        Description                                  
                                                                                      
-----------------+---------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
 pg_stat_monitor | 1.0     | public     | The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated st
atistics, client information, plan details including plan, and histogram information.
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

pg_stat_monitor和pg_stat_statements视图的差异：

注意，根据PG版本的不同可能会存在列名的差异。

注：pg_stat_monitor以桶的形式积累信息。所有聚合信息都是基于桶的。pg_stat_monitor.pgsm_max_buckets参数用于设置系统可以拥有的桶数（默认值为10）。例如，如果此参数设置为 2，则系统将创建两个存储桶。首先，系统会将所有信息添加到第一个桶中。在其生命周期（pg_stat_monitor.pgsm_bucket_time 参数控制）到期后，它将切换到第二个存储桶，重置所有计数器并重复该过程。

查询举例：

查看某用户通过某个IP执行了哪些SQL：

postgres=# select query from pg_stat_monitor where userid ='10' and client_ip ='127.0.0.1';
                                query                                 
----------------------------------------------------------------------
 select bucket,bucket_start_time,calls from pg_stat_monitor
 select now()
 select bucket,bucket_start_time,calls from pg_stat_monitor;
 select * from pg_stat_monitor
 select * from pg_stat_monitor_settings
 select query from pg_stat_monitor where userid =$1 and client_ip =$2
 select * from pg_stat_monitor where userid =$1 and client_ip =$2
(7 rows)

查看某张表相关的SQL语句：

postgres=# select query,array_to_string(relations,',') from pg_stat_monitor where array_to_string(relations,',') like '%public.pg_stat_monitor%';
                                                    query                                                    |                array_to_string                 
-------------------------------------------------------------------------------------------------------------+------------------------------------------------
 select query from pg_stat_monitor where relations = $1                                                      | public.pg_stat_monitor*,pg_catalog.pg_database
 select query,relations from pg_stat_monitor                                                                 | public.pg_stat_monitor*,pg_catalog.pg_database
 select query,relations from pg_stat_monitor where query like $1                                             | public.pg_stat_monitor*,pg_catalog.pg_database
 select query,array_to_string(relations,$1) from pg_stat_monitor where array_to_string(relations,$2) like $3 | public.pg_stat_monitor*,pg_catalog.pg_database
 select query from pg_stat_monitor where array_to_string(relations,$1) like $2                               | public.pg_stat_monitor*,pg_catalog.pg_database
(5 rows)

查询SQL的执行计划:

默认不记录执行计划，记录执行计划需要修改pg_stat_monitor.pgsm_enable_query_plan参数值为yes,并重启数据库。

postgres=# alter system set pg_stat_monitor.pgsm_enable_query_plan ='yes';
ALTER SYSTEM
[postgres@mogdb1 data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-05-11 17:33:24.361 CST [3261] LOG:  pgaudit extension initialized
2022-05-11 17:33:24.453 CST [3261] LOG:  redirecting log output to logging collector process
2022-05-11 17:33:24.453 CST [3261] HINT:  Future log output will appear in directory "log".
 done
server started

postgres=# SELECT queryid,substr(query,0,50), query_plan from pg_stat_monitor where queryid='4B2350A27CE7866' limit 1;
     queryid     |                      substr                       |                             query_plan                              
-----------------+---------------------------------------------------+---------------------------------------------------------------------
 4B2350A27CE7866 | SELECT queryid,substr(query,$1,$2), query_plan fr | Limit                                                              +
                 |                                                   |   ->  Subquery Scan on pg_stat_monitor                             +
                 |                                                   |         ->  Sort                                                   +
                 |                                                   |               Sort Key: p.bucket_start_time                        +
                 |                                                   |               ->  Hash Join                                        +
                 |                                                   |                     Hash Cond: (p.dbid = d.oid)                    +
                 |                                                   |                     ->  Function Scan on pg_stat_monitor_internal p+
                 |                                                   |                     ->  Hash                                       +
                 |                                                   |                           ->  Seq Scan on pg_database d
(1 row)

查询SQL的执行时间相关统计信息：

postgres=# SELECT  queryid,bucket_start_time,userid,calls,total_exec_time, min_exec_time, max_exec_time, mean_exec_time,substr(query,0,50) FROM pg_stat_monitor;
     queryid      |  bucket_start_time  |  userid  | calls | total_exec_time | min_exec_time | max_exec_time | mean_exec_time |                      substr                       
------------------+---------------------+----------+-------+-----------------+---------------+---------------+----------------+---------------------------------------------------
 E2E0AEC763FECFAE | 2022-05-11 17:33:00 | postgres |     1 |               0 |             0 |             0 |              0 | SELECT query_id,substr(query,0,50), query_plan fr
 FC334B073CF9B063 | 2022-05-11 17:37:00 | postgres |     1 |          0.1601 |        0.1601 |        0.1601 |         0.1601 | SELECT queryid,substr(query,$1,$2), query_plan fr
 4B2350A27CE7866  | 2022-05-11 17:37:00 | postgres |     1 |          0.2355 |        0.2355 |        0.2355 |         0.2355 | SELECT queryid,substr(query,$1,$2), query_plan fr
 7CB51E63BFAC1315 | 2022-05-11 17:37:00 | postgres |     1 |          0.2983 |        0.2983 |        0.2983 |         0.2983 | insert into t values($1)
 886013F2242FBFD6 | 2022-05-11 17:37:00 | postgres |     1 |          0.0475 |        0.0475 |        0.0475 |         0.0475 | select * from t
 FC334B073CF9B063 | 2022-05-11 17:38:00 | postgres |     1 |          0.1613 |        0.1613 |        0.1613 |         0.1613 | SELECT queryid,substr(query,$1,$2), query_plan fr
 86D46B2816477138 | 2022-05-11 17:38:00 | postgres |     1 |          0.1871 |        0.1871 |        0.1871 |         0.1871 | SELECT queryid,substr(query,$1,$2), query_plan fr
 F6F72CD88502F995 | 2022-05-11 17:40:00 | postgres |     1 |               0 |             0 |             0 |              0 | SELECT  queryid,bucket_start_time,userid,  total_
 449629D48D66AE31 | 2022-05-11 17:44:00 | postgres |     1 |          0.1883 |        0.1883 |        0.1883 |         0.1883 | SELECT  queryid,bucket_start_time,userid,calls,to
(9 rows)