pgStatsPack 的介绍和使用说明

pgStatsPack 的介绍和使用说明

目录

• pgStatsPack 的介绍和使用说明
  • pgStatPack 全面介绍与使用指南
  • 一、pgStatPack 核心特性与价值
    • 1. 核心功能
    • 2. 与同类工具的差异
  • 二、前置依赖与部署步骤
    • 1. 环境要求
    • 2. 步骤 1：部署 pg_stat_statements 扩展
    • 3. 步骤 2：部署 pgStatPack
  • 三、pgStatPack 核心使用流程
    • 1. 步骤 1：创建性能快照
    • 2. 步骤 2：生成性能报告
    • 3. 步骤 3：报告关键指标解读
  • 四、常见问题与解决方案
    • 1. 安装脚本执行失败：“psql: command not found”
    • 2. 生成快照时无返回结果，或报告无数据
    • 3. 报告中无 SQL 统计数据
    • 4. 清理旧快照报错：“permission denied”
  • 五、注意事项与最佳实践
  • 总结

pgStatPack 全面介绍与使用指南

pgStatPack 是 PostgreSQL 生态中一款经典的性能分析工具，定位类似 Oracle 的 Statspack/AWR 报告，旨在通过定期采集数据库快照、汇总核心性能指标，帮助 DBA 监控数据库运行状态、定位性能瓶颈（如慢查询、高 IO、锁等待等）。它依赖 PostgreSQL 官方扩展 pg_stat_statements 实现 SQL 语句级统计，同时补充了表、索引、会话、系统资源等维度的监控数据，形成完整的性能分析闭环。

一、pgStatPack 核心特性与价值

1. 核心功能

pgStatPack 并非独立工具，而是基于 pg_stat_statements 扩展的 “增强封装”，主要功能包括：

• 多维度快照采集：定期收集数据库级（如 TPS、缓存命中率）、表级（如读写次数、数据块 IO）、索引级（如扫描次数、命中次数）、SQL 级（如执行耗时、调用次数）的统计数据；
• 自动化快照管理：支持通过定时任务（如 Cron）按固定间隔（如 15 分钟）生成快照，避免手动操作；
• 对比分析报告：基于 “起始快照 - 结束快照” 的时间窗口，生成结构化文本报告，展示期间数据库的性能变化（如 Top 慢查询、高 IO 表、索引使用效率等）；
• 轻量无侵入：采集过程消耗资源低，无需修改应用代码，仅需数据库层面配置即可启用。

2. 与同类工具的差异

工具	核心优势	局限性	适用场景
pgStatPack	兼容低版本 PostgreSQL（9.0+）、报告简洁、部署轻量	无可视化界面、功能较基础（无实时监控）	中小规模 PostgreSQL 集群、基础性能诊断
pg_stat_statements	官方原生扩展、SQL 语句级统计精准	仅聚焦 SQL，缺乏表 / 索引 / 系统维度数据	单独分析 SQL 性能瓶颈
pg_statsinfo	支持 Web 可视化、数据维度更丰富	部署复杂、依赖额外组件（如 Web 服务）	大规模集群、需可视化监控的场景
AWR（Oracle）	企业级功能（如 ADDM 自动诊断）	仅支持 Oracle、商业闭源	Oracle 数据库环境

二、前置依赖与部署步骤

pgStatPack 依赖 pg_stat_statements 扩展（用于 SQL 统计），因此部署需分两步：先安装 pg_stat_statements，再部署 pgStatPack 本身。

1. 环境要求

• PostgreSQL 版本：9.0+（推荐 9.6+，兼容性更好）；
• 权限：需 postgres 超级用户权限（修改配置文件、创建扩展）；
• 操作系统：Linux（如 CentOS、Ubuntu），Windows 环境需手动适配脚本路径。

2. 步骤 1：部署 pg_stat_statements 扩展

pg_stat_statements 是 PostgreSQL 官方 contrib 模块，负责跟踪 SQL 语句的执行统计（如执行次数、耗时、返回行数），是 pgStatPack 的核心依赖。

（1）安装扩展库文件
根据 PostgreSQL 安装方式，选择对应的部署方式：
源码编译安装（适用于源码安装的 PostgreSQL）：
1.进入 PostgreSQL 源码的 contrib/pg_stat_statements 目录：

cd $PGHOME/contrib/pg_stat_statements  # $PGHOME 是PostgreSQL安装目录，如 /usr/pgsql-11

2.编译并安装扩展库：

make && make install

执行成功后，会在 $PGHOME/lib 目录下生成 pg_stat_statements.so 库文件。

RPM/YUM 安装（适用于 yum 安装的 PostgreSQL，如 CentOS）：
直接通过系统包管理器安装预编译的扩展包：

yum install postgresql11-contrib  # 对应PostgreSQL 11版本，需与数据库版本一致

（2）配置扩展参数
1.编辑 PostgreSQL 主配置文件 postgresql.conf（路径通常为 $PGDATA/postgresql.conf）：

# 1. 加载 pg_stat_statements 扩展（需重启数据库生效）
shared_preload_libraries = 'pg_stat_statements'  # 若已有其他扩展，用逗号分隔（如 'pg_stat_statements,pg_buffercache'）
# 2. 可选：设置SQL统计的最大缓存条数（默认5000，根据业务量调整）
pg_stat_statements.max = 10000
# 3. 可选：设置跟踪的SQL类型（默认all，包括SELECT/INSERT/UPDATE/DELETE）
pg_stat_statements.track = all

2.重启 PostgreSQL 服务，使配置生效：

# 系统服务方式（CentOS 7+）
systemctl restart postgresql-11
# 手动重启方式
pg_ctl -D $PGDATA restart

（3）创建扩展（数据库级）
登录目标数据库（如 hm 库），执行 SQL 创建 pg_stat_statements 扩展（需超级用户权限）：

# 切换为 postgres 操作系统用户
su - postgres
# 登录目标数据库（如 hm 库）
psql -d hm
# 执行创建扩展命令
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

验证是否生效：查询 pg_stat_statements 视图，若返回结果则表示部署成功：

SELECT query, calls, total_time FROM pg_stat_statements LIMIT 5;

3. 步骤 2：部署 pgStatPack

（1）下载 pgStatPack
pgStatPack 托管于 pgFoundry（PostgreSQL 第三方项目平台），推荐下载稳定版本（如 2.3.1，兼容 9.0+ 版本）：

# 下载压缩包（2.3.1版本为例）
wget http://pgfoundry.org/frs/download.php/3151/pgstatspack_version_2.3.1.tar.gz
# 解压到指定目录（如 /opt/pgstatspack）
tar -xzvf pgstatspack_version_2.3.1.tar.gz -C /opt/
cd /opt/pgstatspack

（2）修改安装脚本（关键！适配 psql 路径）
pgStatPack 安装脚本 install_pgstats.sh 默认使用 /usr/bin/psql，若你的 psql 路径不同（如 /usr/pgsql-11/bin/psql），需手动修改脚本：
1.编辑 install_pgstats.sh：

vi install_pgstats.sh

2.找到 PSQL 变量，修改为实际 psql 路径：

# 原配置（默认）
# PSQL="/usr/bin/psql"
# 修改后（示例：PostgreSQL 11的psql路径）
PSQL="/usr/pgsql-11/bin/psql"

（3）执行安装脚本
运行 install_pgstats.sh 脚本，自动在所有数据库（包括 template1、用户数据库）创建 pgStatPack 所需的表、视图和存储过程：

# 赋予脚本执行权限
chmod +x install_pgstats.sh
# 执行安装（需 postgres 用户权限，无需登录数据库）
./install_pgstats.sh

安装成功后，目标数据库中会创建以 pgstatspack_ 开头的表（如 pgstatspack_snap 存储快照信息、pgstatspack_tables 存储表统计数据）。

三、pgStatPack 核心使用流程

pgStatPack 的使用围绕 “创建快照 → 生成报告 → 分析报告” 三步展开，核心是通过 “快照对比” 定位性能问题。

1. 步骤 1：创建性能快照

快照是 pgStatPack 分析的基础，需至少创建两个快照（起始 + 结束）才能生成对比报告。

（1）手动创建快照
进入 pgStatPack 的 bin 目录，执行 snapshot.sh 脚本：

# 进入 bin 目录（如 /opt/pgstatspack/bin）
cd /opt/pgstatspack/bin
# 赋予脚本执行权限（首次执行）
chmod +x snapshot.sh
# 执行快照脚本（自动对所有数据库生成快照）
./snapshot.sh

执行成功后，会返回各数据库的快照 ID（如 pgstatspack_snap = 1），表示快照已生成。

（2）自动创建快照（推荐）
生产环境中，需通过 Cron 定时任务按固定间隔（如 15 分钟）生成快照，避免手动操作：
切换为 postgres 操作系统用户（Cron 任务需与数据库用户一致）：

su - postgres

编辑 Cron 任务：
crontab -e

添加以下内容（每 15 分钟生成一次快照，每天凌晨 3:02 清理 30 天前的旧快照）：
# 每15分钟生成快照，日志输出到 /opt/pgstatspack/logs/snap.log
*/15 * * * * /opt/pgstatspack/bin/snapshot.sh 1>> /opt/pgstatspack/logs/snap.log 2>&1
# 每天3:02清理30天前的快照，日志输出到 /opt/pgstatspack/logs/delete.log
2 3 * * * /opt/pgstatspack/bin/delete_snapshot.sh -d 30 1>> /opt/pgstatspack/logs/delete.log 2>&1

创建日志目录（避免日志写入失败）：
mkdir -p /opt/pgstatspack/logs

2. 步骤 2：生成性能报告

基于两个快照的 ID（如起始 ID=1，结束 ID=2），执行 pgstatspack_report.sh 脚本生成对比报告。
（1）执行报告脚本

# 进入 bin 目录
cd /opt/pgstatspack/bin
# 赋予脚本执行权限（首次执行）
chmod +x pgstatspack_report.sh
# 执行报告脚本
./pgstatspack_report.sh

（2）交互式配置报告参数
脚本会引导输入以下参数，按提示操作即可：

• 1.输入数据库用户名：默认 postgres（需超级用户权限）；
• 2.选择目标数据库：脚本会列出所有可用数据库（如 1. hm），输入编号（如 1）；
• 3.输入起始快照 ID：如 1（较早的快照）；
• 4.输入结束快照 ID：如 2（较新的快照）；
• 5.确认报告路径：默认生成到 /tmp/pgstatreport_数据库名_起始ID_结束ID.txt（如 /tmp/pgstatreport_hm_1_2.txt）。

（3）示例报告输出
报告为结构化文本格式，核心模块包括：

• 快照基础信息：起始 / 结束时间、快照间隔（如 11 秒）、PostgreSQL 版本；
• 数据库整体性能：TPS（事务 / 秒）、缓存命中率、逻辑 IO / 物理 IO 每秒（lio_ps/pio_ps）；
• Top 表统计：按 “表 - 索引读比”“更新次数”“删除次数” 排序的表列表（如高 IO 表 public.pgstatspack_indexes）；
• Top 索引统计：按 “扫描次数”“数据块命中次数” 排序的索引列表（如低效索引 pg_toast.pg_toast_2619_index）；
• Top SQL 统计：按 “总耗时”“平均耗时” 排序的 SQL 语句（依赖 pg_stat_statements 数据）。

3. 步骤 3：报告关键指标解读

报告中的核心指标是性能诊断的关键，需重点关注以下维度：
（1）数据库整体健康度

指标	含义	健康阈值	异常排查方向
TPS	每秒事务数（含读写）	依业务而定（如 OLTP 需 > 100）	TPS 骤降可能是锁等待或资源瓶颈
hitrate	缓存命中率（逻辑读 /(逻辑读 + 物理读)）	>95%	命中率低需增大 shared_buffers
pio_ps	每秒物理 IO 次数	依磁盘性能而定（如 < 100）	高物理 IO 需检查全表扫描或索引缺失

（2）表与索引效率

• 表 - 索引读比（table to index read ratio）：比值越高，说明表的 “全表扫描” 越多，需为 WHERE 条件列添加索引；
• 索引 hitrate：索引缓存命中率（索引命中次数 /(索引读 + 索引命中)），低命中率说明索引使用频率低，可考虑删除冗余索引；
• 表更新 / 删除次数：高频更新表需关注锁等待（如 pg_locks 视图）。

（3）SQL 性能瓶颈
重点关注 “总耗时 Top 10” 的 SQL，核心指标：

• total_time：总执行时间（毫秒），占比高的 SQL 是优化重点；
• mean_time：平均执行时间，长期高于 100ms 的 SQL 需分析执行计划（如 EXPLAIN ANALYZE）；
• calls：调用次数，高频低耗时 SQL（如每秒调用 1000 次，每次 10ms）累计耗时可能很高，需优化逻辑（如增加缓存）。

四、常见问题与解决方案

1. 安装脚本执行失败：“psql: command not found”

原因：install_pgstats.sh 中 PSQL 变量路径错误，找不到 psql 命令。
解决方案：修改脚本中的 PSQL 路径为实际路径（如 /usr/pgsql-11/bin/psql），参考 “步骤 2-（2）修改安装脚本”。

2. 生成快照时无返回结果，或报告无数据

原因 1：pg_stat_statements 扩展未正确加载。排查：执行 SELECT * FROM pg_stat_statements LIMIT 1，若报错 “relation does not exist”，需重新创建扩展（CREATE EXTENSION pg_stat_statements）。
原因 2：快照间隔过短，无数据变化。
解决方案：延长快照间隔（如等待 5 分钟后再创建第二个快照）。

3. 报告中无 SQL 统计数据

原因：pg_stat_statements 未加入 shared_preload_libraries，或配置未重启生效。
解决方案：检查 postgresql.conf 中 shared_preload_libraries 是否包含 pg_stat_statements，并重启数据库。

4. 清理旧快照报错：“permission denied”

原因：执行 delete_snapshot.sh 的用户无 pgstatspack_snap 表的删除权限。
解决方案：确保以 postgres 用户执行脚本，或为当前用户授予 pgstatspack_* 表的 DELETE 权限：

GRANT DELETE ON ALL TABLES IN SCHEMA public TO 用户名;

五、注意事项与最佳实践

1.快照间隔设置：

• OLTP 环境（高并发小事务）：建议 15 分钟 / 次，便于定位短期瓶颈；
• OLAP 环境（大查询）：建议 1 小时 / 次，减少快照对查询的影响。
  2.快照保留策略：
• 生产环境建议保留 30 天快照（通过 delete_snapshot.sh -d 30 定期清理），避免占用过多磁盘空间；
• 重要业务时段（如促销）可临时缩短快照间隔（如 5 分钟 / 次），便于事后追溯。
  3.报告分析频率：
• 日常运维：每天查看前一天的 “日报告”（如选取 00:00 和 23:59 的快照）；
• 性能异常时：生成 “异常时段报告”（如故障发生前后 1 小时的快照），精准定位问题。
  4.与其他工具结合：
• 若需可视化：将 pgStatPack 报告数据导入 Grafana（通过自定义脚本解析文本报告）；
• 若需实时监控：搭配 pg_stat_activity 视图（查看当前会话）和 pgBadger（日志分析工具），形成 “历史快照 + 实时监控” 的完整监控体系。

总结

pgStatPack 是 PostgreSQL 低版本环境（9.0+）的 “轻量性能诊断利器”，部署简单、报告直观，适合中小规模集群的日常运维。其核心价值在于 “通过快照对比发现性能变化”，尤其擅长定位慢查询、高 IO 表、低效索引等基础瓶颈。若需更复杂的功能（如实时监控、可视化界面），可考虑在 pgStatPack 基础上集成 pg_statsinfo 或 Prometheus+Grafana，但对于多数场景，pgStatPack 已能满足核心性能诊断需求。