PostgreSQL pg_profile 安装与配置

#PostgreSQL #pg_profile #安装与配置

1 本章背景知识

本文介绍如何在 PostgreSQL 数据库安装和配置 pg_profile。

2 先决条件

2.1 其他依赖扩展安装

2.2 PostgreSQL 数据库参数配置

编辑 postgresql.conf 配置文件。

su  - postgres 
#postgres>
vi $PGDATA/postgresql.conf

配置数据库需要收集的统计信息参数。


//输入内容：
track_activities = on
track_counts = on
track_io_timing = on
track_wal_io_timing = on      # Since Postgres 14
track_functions = all

参数	说明
track_activities	收集每个会话当前正在执行的SQL 语句的相关信息。
track_counts	收集整个数据库的统计信息。
track_io_timing	收集I/O 调度相关的统计信息。
track_wal_io_timing	收集 WAL 日志的 I/O 调度相关的统计信息。
track_functions	启用跟踪函数调用计数和用时。

关于这些参数请参考 19.9. 运行时统计数据 (postgres.cn)

pg_ctl reload -D $PGDATA

su  - postgres 
#postgres>
psql -U postgres -d testdb -c "SHOW track_activities;"
psql -U postgres -d testdb -c "SHOW track_counts;"
psql -U postgres -d testdb -c "SHOW track_io_timing;"
psql -U postgres -d testdb -c "SHOW track_wal_io_timing;"
psql -U postgres -d testdb -c "SHOW track_functions;"

2.3 创建pg_profile 专用模式

psql -U postgres -d testdb -c "CREATE SCHEMA profile"
psql -U postgres -d postgres -c "CREATE SCHEMA profile"
psql -U postgres -d template1 -c "CREATE SCHEMA profile"

3 pg_profile 安装

3.1 源码下载

下载 pg_profile 最新源码，到 /soft 文件夹。

su - postgres 
cd /soft
git clone https://github.com/zubkov-andrei/pg_profile.git
cd pg_profile
make USE_PGXS=y install && make USE_PGXS=y installcheck

3.2 数据库配置

此插件在启动数据库时，必须使用共享缓存用于存放计数器。所以需要配置 shared_preload_libraries 参数。并且还需要配置 PostgreSQL pg_profile 插件。

vi $PGDATA/postgresql.conf

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache,dblink,pg_wait_sampling'

重启数据库之后，在每个数据库中安装扩展。

pg_ctl restart -D $PGDATA

waiting for server to shut down.... done
server stopped
waiting for server to start....2024-05-29 16:24:30.762 CST   [1700]LOG:  00000: Auto detecting pg_stat_kcache.linux_hz parameter...
2024-05-29 16:24:30.762 CST   [1700]LOCATION:  pgsk_assign_linux_hz_check_hook, pg_stat_kcache.c:376
2024-05-29 16:24:30.762 CST   [1700]LOG:  00000: pg_stat_kcache.linux_hz is set to 500000
2024-05-29 16:24:30.762 CST   [1700]LOCATION:  pgsk_assign_linux_hz_check_hook, pg_stat_kcache.c:386
2024-05-29 16:24:30.784 CST   [1700]LOG:  00000: redirecting log output to logging collector process
2024-05-29 16:24:30.784 CST   [1700]HINT:  Future log output will appear in directory "pg_log".
2024-05-29 16:24:30.784 CST   [1700]LOCATION:  SysLogger_Start, syslogger.c:712
 done
server started

su - postgres
#postgres>
psql -U postgres -d testdb -c "CREATE EXTENSION pg_profile SCHEMA profile CASCADE ;"
psql -U postgres -d postgres -c "CREATE EXTENSION pg_profile SCHEMA profile  CASCADE;"
psql -U postgres -d template1 -c "CREATE EXTENSION pg_profile SCHEMA profile  CASCADE;"

3.3 配置定时任务

su - postgres 
#postgres>
crontab  -e

*/30 * * * *   . /home/postgres/.bash_profile;psql -U postgres -d testdb -c 'SELECT profile.take_sample()' > /dev/null 2>&1

Note

由于 pg_profile 不支持定时任务，所以需要使用 Linux 的 crontab 功能来定时收集统计信息。

4 pg_profile 配置

vi $PGDATA/postgressql.conf

//输入内容：
pg_profile.max_query_length=20000
pg_profile.track_sample_timings = on
pg_profile.max_sample_age = 7
pg_profile.topn = 20

请参考 pg_profile 参数，这里大部分采用了默认值。