pg_stat_monitor

pg_stat_monitor 是 percona 开源的一款 extension，用于监控 postgresql 的性能。
os: centos 7.6
db: postgresql 13.2

版本

# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core) 
# 
# 
# su - postgres
Last login: Wed May 26 08:53:22 CST 2021 on pts/0
$ 
$ 
$ psql 
psql (13.2)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

下载安装

# cd /opt 
# wget https://github.com/percona/pg_stat_monitor/archive/refs/tags/REL0_9_1.tar.gz
# tar -zxvf ./REL0_9_1.tar.gz
# cd pg_stat_monitor-REL0_9_1/

# export PGHOME=/usr/pgsql-13;
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${PGHOME}/lib;
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib;
export PATH=${PGHOME}/bin:$PATH;
export MANPATH=${PGHOME}/share/man:$MANPATH;

# make USE_PGXS=1
# make USE_PGXS=1 install

/usr/bin/mkdir -p '/usr/pgsql-13/lib'
/usr/bin/mkdir -p '/usr/pgsql-13/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-13/share/extension'
/usr/bin/install -c -m 755  pg_stat_monitor.so '/usr/pgsql-13/lib/pg_stat_monitor.so'
/usr/bin/install -c -m 644 .//pg_stat_monitor.control '/usr/pgsql-13/share/extension/'
/usr/bin/install -c -m 644 .//pg_stat_monitor--1.0.sql  '/usr/pgsql-13/share/extension/'
/usr/bin/mkdir -p '/usr/pgsql-13/lib/bitcode/pg_stat_monitor'
/usr/bin/mkdir -p '/usr/pgsql-13/lib/bitcode'/pg_stat_monitor/
/usr/bin/install -c -m 644 hash_query.bc '/usr/pgsql-13/lib/bitcode'/pg_stat_monitor/./
/usr/bin/install -c -m 644 guc.bc '/usr/pgsql-13/lib/bitcode'/pg_stat_monitor/./
/usr/bin/install -c -m 644 pg_stat_monitor.bc '/usr/pgsql-13/lib/bitcode'/pg_stat_monitor/./
cd '/usr/pgsql-13/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_stat_monitor.index.bc pg_stat_monitor/hash_query.bc pg_stat_monitor/guc.bc pg_stat_monitor/pg_stat_monitor.bc

修改参数文件

# cd $PGDATA
# vi postgresql.conf
shared_preload_libraries = 'pg_stat_monitor, pg_stat_statements'

# systemctl restart postgresql-13

创建 extension

# su - postgres
$ psql
psql (13.2)
Type "help" for help.

postgres=# CREATE EXTENSION pg_stat_monitor;

postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description                               
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_monitor    | 1.0     | public     | track execution statistics of all SQL statements executed
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# \dx+ pg_stat_monitor
   Objects in extension "pg_stat_monitor"
             Object description             
--------------------------------------------
 function decode_error_level(integer)
 function get_cmd_type(integer)
 function get_histogram_timings()
 function get_state(bigint)
 function histogram(integer,text)
 function pg_stat_monitor_internal(boolean)
 function pg_stat_monitor_reset()
 function pg_stat_monitor_settings()
 function pg_stat_monitor_version()
 function range()
 view pg_stat_monitor
 view pg_stat_monitor_settings
(12 rows)

postgres=# select pg_stat_monitor_settings();
                                                                pg_stat_monitor_settings                               
                                 
--------------------------------------------------------------------------------------------------------------------------------------------------------
 (pg_stat_monitor.pgsm_max,100,100,"Sets the maximum size of shared memory in (MB) used for statement's metadata tracked by pg_stat_monitor.",1,1000,1)
 (pg_stat_monitor.pgsm_query_max_len,1024,1024,"Sets the maximum length of query.",1024,2147483647,1)
 (pg_stat_monitor.pgsm_enable,1,1,"Enable/Disable statistics collector.",0,0,0)
 (pg_stat_monitor.pgsm_track_utility,1,1,"Selects whether utility commands are tracked.",0,0,0)
 (pg_stat_monitor.pgsm_normalized_query,1,1,"Selects whether save query in normalized format.",0,0,0)
 (pg_stat_monitor.pgsm_max_buckets,10,10,"Sets the maximum number of buckets.",1,10,1)
 (pg_stat_monitor.pgsm_bucket_time,300,300,"Sets the time in seconds per bucket.",1,2147483647,1)
 (pg_stat_monitor.pgsm_histogram_min,0,0,"Sets the time in millisecond.",0,2147483647,1)
 (pg_stat_monitor.pgsm_histogram_max,100000,100000,"Sets the time in millisecond.",10,2147483647,1)
 (pg_stat_monitor.pgsm_histogram_buckets,10,10,"Sets the maximum number of histogram buckets",2,2147483647,1)
 (pg_stat_monitor.pgsm_query_shared_buffer,20,20,"Sets the maximum size of shared memory in (MB) used for query tracked by pg_stat_monitor.",1,10000,1)
 (pg_stat_monitor.pgsm_overflow_target,0,1,"Sets the overflow target for pg_stat_monitor",0,1,1)
 (pg_stat_monitor.pgsm_enable_query_plan,0,0,"Enable/Disable query plan monitoring",0,0,0)
 (pg_stat_monitor.pgsm_track_planning,1,1,"Selects whether planning statistics are tracked.",0,0,0)
(14 rows)

postgres=# select pg_stat_monitor_version();
 pg_stat_monitor_version 
-------------------------
 0.9.1
(1 row)

通常只需要执行 pg_stat_monitor 即可

postgres=# select * from pg_stat_monitor;

参考:
https://github.com/percona/pg_stat_monitor
https://www.percona.com/doc/percona-monitoring-and-management/2.x/setting-up/client/postgresql.html