PostgreSQL pg_stat_kcache 安装与配置
1 背景知识
本文主要介绍如何在 PostgreSQL16 环境下使用源码安装 pg_stat_kcache 插件。
2 pg_stat_kcache安装
2.1 源码下载
下载 pg_stat_kcache 最新源码，到 /soft 文件夹。
su - postgres
#postgre>
cd /soft
git clone https://github.com/powa-team/pg_stat_kcache.git
cd pg_stat_kcache
make
make install
2.2 数据库配置
此插件在启动数据库时，必须使用共享缓存用于存放计数器。所以需要配置 shared_preload_libraries 参数。并且还需要配置 pg_stat_statements 插件。
vi $PGDATA/postgresql.conf
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'
重启数据库之后，在每个数据库中安装扩展。
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_stat_kcache CASCADE;"
psql -U postgres -d postgres -c "CREATE EXTENSION pg_stat_kcache CASCADE;"
psql -U postgres -d template1 -c "CREATE EXTENSION pg_stat_kcache CASCADE;"
3 pg_stat_kcache 配置
psql -U postgres -d testdb -c "ALTER SYSTEM SET pg_stat_kcache.track_planning=on;"
pg_ctl reload -D $PGDATA
psql -U postgres -d testdb -c "SHOW pg_stat_kcache.track_planning;"
pg_stat_kcache.track_planning
-------------------------------
on
(1 row)
关于 pg_stat_kcache.track_planning 参数的详细说明，请参考 PostgreSQL pg_stat_kcache 参数。