常见监控扩展

pg_top

使用pg_top扩展可以将IO, IOPS, memory, cpu等信息结合起来看。

安装pg_top

# 1. download
wget https://gitlab.com/pg_top/pg_top/-/archive/main/pg_top-main.tar.gz

# [Debian]install dependencies
sudo apt install -y cmake libbsd0 libbsd-dev

# 2. 配置安装路径，指向postgres的安装路径，不是postgres数据目录路径
cmake -DCMAKE_INSTALL_PREFIX=/home/postgres/pgsql CMakeLists.txt

# 3. 安装
make install

# uninstall. 如果要卸载，需要保留pg_top的源码目录
`xargs rm < install_manifest.txt`

# 如果之前添加过pgsql/bin到PATH环境变量，那么直接运行pg_top验证即可
pg_top --help

pg_stat_statements

pg_stat_statements是postgres源码包中提供的一个扩展，通常用于统计数据库的资源开销和收集数据库中运行的SQL信息，从而帮助分析出现频率较高的SQL语句。

安装pg_stat_statements

# 1. 进入postgres源码目录，找到pg_stat_statements目录
cd contrib/pg_stat_statements
make
make install

# 2. 修改postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# 3. 重启postgres
pg_ctl -D /home/postgres/pgsql/data -l /home/postgres/pgsql/logs/start.log restart

# 查看shared_preload_libraries的值
show shared_preload_libraries;

# 创建pg_stat_statements扩展
create extension pg_stat_statements;

使用pg_stat_statements扩展

-- 查看pg_stat_statements扩展的参数
-- max: 跟踪语句的最大数目
-- save: 数据库服务关闭后是否保存统计信息
-- track: 控制哪些语句会被统计, top: 直接由客户端发出的语句, all: 跟踪嵌套语句, none: 禁止语句统计信息收集
-- track_planning: 是否跟踪计划操作和持续时间
-- track_utility: 是否跟踪工具命令
postgres=# select name,setting from pg_settings where name like 'pg_stat_statements%';
               name                | setting
-----------------------------------+---------
 pg_stat_statements.max            | 5000
 pg_stat_statements.save           | on
 pg_stat_statements.track          | top
 pg_stat_statements.track_planning | off
 pg_stat_statements.track_utility  | on
(5 rows)


-- 查询包含"emp"的SQL语句情况
select query, calls, total_exec_time, min_exec_time, max_exec_time, mean_exec_time from pg_stat_statements where query like '%emp%';

-- 查看总执行时间最长的5条sql
select query, calls, total_exec_time, rows, 100.0*shared_blks_hit/nullif(shared_blks_hit + shared_blks_read, 0) 'hit_rare' from pg_stat_statements order by total_exec_time desc limit 5;