pg_stat_monitor 小鱼儿 2022-10-16 06:10 127阅读 0赞 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
还没有评论，来说两句吧...