postgresql性能监控工具之pg-m6米乐安卓版下载

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)