PostgreSQL pg_stat_statements 与 pg_stat_monitor

pg_stat_statements 和 pg_stat_monitor 是 PostgreSQL 中用于监控和分析查询性能的两个重要扩展。pg_stat_statements 是官方内置扩展，提供基础的查询统计功能；pg_stat_monitor 是 Percona 开发的增强型扩展，提供更丰富的监控和分析功能。

pg_stat_statements

pg_stat_statements 是 PostgreSQL 官方提供的扩展，用于跟踪所有 SQL 语句的执行统计信息，包括执行次数、总执行时间、平均执行时间、内存使用等。

主要功能

跟踪 SQL 语句的执行统计信息
支持按查询类型、用户、数据库等维度统计
提供详细的执行时间分布
支持查询计划缓存统计
支持查询的内存和磁盘使用统计

pg_stat_monitor

pg_stat_monitor 是 Percona 开发的增强型查询监控扩展，基于 pg_stat_statements 扩展，提供更丰富的监控和分析功能。

主要功能

提供更详细的查询统计信息
支持查询执行计划的捕获
支持查询的绑定变量信息
提供直方图统计功能
支持查询采样和聚合
支持更细粒度的时间窗口统计
提供查询的等待事件统计

安装与配置

1. pg_stat_statements 安装与配置

安装

bash

# 1. 在 RHEL/CentOS 上安装
# pg_stat_statements 通常包含在 postgresql-contrib 包中
yum install postgresql14-contrib

# 2. 在 Debian/Ubuntu 上安装
apt install postgresql-contrib

配置

sql

-- 1. 在 postgresql.conf 中启用扩展
-- shared_preload_libraries = 'pg_stat_statements'

-- 2. 重启数据库后，创建扩展
CREATE EXTENSION pg_stat_statements;

-- 3. 配置参数
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.track_utility = 'on';
ALTER SYSTEM SET pg_stat_statements.save = 'on';

-- 4. 重新加载配置
SELECT pg_reload_conf();

2. pg_stat_monitor 安装与配置

安装

bash

# 1. 在 RHEL/CentOS 上安装
# 从 Percona 仓库安装
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-pg-stat-monitor14

# 2. 在 Debian/Ubuntu 上安装
apt install gnupg2
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
apt update
apt install percona-pg-stat-monitor14

配置

sql

-- 1. 在 postgresql.conf 中启用扩展
-- shared_preload_libraries = 'pg_stat_monitor'

-- 2. 重启数据库后，创建扩展
CREATE EXTENSION pg_stat_monitor;

-- 3. 配置参数
ALTER SYSTEM SET pg_stat_monitor.pgsm_max = 10000;
ALTER SYSTEM SET pg_stat_monitor.pgsm_track = 'all';
ALTER SYSTEM SET pg_stat_monitor.pgsm_max_contexts = 100;
ALTER SYSTEM SET pg_stat_monitor.pgsm_max_relations = 1000;
ALTER SYSTEM SET pg_stat_monitor.pgsm_query_max_len = 1024;

-- 4. 重新加载配置
SELECT pg_reload_conf();

使用方法

1. pg_stat_statements 使用示例

sql

-- 1. 查看执行时间最长的前10个查询
SELECT 
  queryid,
  query,
  calls,
  total_time,
  mean_time,
  rows,
  100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

-- 2. 查看执行次数最多的前10个查询
SELECT 
  queryid,
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements 
ORDER BY calls DESC 
LIMIT 10;

-- 3. 查看特定数据库的查询统计
SELECT 
  queryid,
  query,
  calls,
  total_time,
  mean_time
FROM pg_stat_statements 
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'mydb') 
ORDER BY total_time DESC 
LIMIT 10;

-- 4. 重置统计信息
SELECT pg_stat_statements_reset();

2. pg_stat_monitor 使用示例

sql

-- 1. 查看执行时间最长的前10个查询
SELECT 
  queryid,
  query,
  calls,
  total_time,
  min_time,
  max_time,
  mean_time,
  rows
FROM pg_stat_monitor 
ORDER BY total_time DESC 
LIMIT 10;

-- 2. 查看带有执行计划的查询
SELECT 
  queryid,
  query,
  planid,
  plan,
  calls,
  total_time
FROM pg_stat_monitor 
WHERE plan IS NOT NULL 
ORDER BY total_time DESC 
LIMIT 5;

-- 3. 查看查询的等待事件
SELECT 
  queryid,
  query,
  wait_event_type,
  wait_event,
  sum(wait_time) AS total_wait_time
FROM pg_stat_monitor 
GROUP BY queryid, query, wait_event_type, wait_event 
ORDER BY total_wait_time DESC 
LIMIT 10;

-- 4. 按时间窗口查看查询统计
SELECT 
  bucket,
  query,
  calls,
  total_time
FROM pg_stat_monitor 
ORDER BY bucket DESC, total_time DESC 
LIMIT 10;

-- 5. 重置统计信息
SELECT pg_stat_monitor_reset();

功能对比

1. 核心功能对比

功能特性	pg_stat_statements	pg_stat_monitor
官方支持	是	否（Percona 开发）
查询统计	基础统计	增强统计
执行计划捕获	否	是
绑定变量信息	否	是
直方图统计	否	是
时间窗口统计	否	是
等待事件统计	否	是
查询采样	否	是
查询聚合	否	是
内存使用	低	中
性能影响	低	中

2. 适用场景对比

扩展	适用场景	不适用场景
pg_stat_statements	基础查询监控、简单性能分析、生产环境稳定监控	复杂查询分析、需要执行计划的场景、需要详细等待事件的场景
pg_stat_monitor	复杂查询分析、性能调优、需要详细统计信息的场景	资源受限的环境、需要极致稳定性的环境

最佳实践

1. 配置最佳实践

sql

-- 1. pg_stat_statements 最佳配置
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.track_utility = 'on';

-- 2. pg_stat_monitor 最佳配置
ALTER SYSTEM SET pg_stat_monitor.pgsm_max = 10000;
ALTER SYSTEM SET pg_stat_monitor.pgsm_track = 'all';
ALTER SYSTEM SET pg_stat_monitor.pgsm_query_max_len = 2048;
ALTER SYSTEM SET pg_stat_monitor.pgsm_bucket_time = 60; -- 60秒一个时间窗口
ALTER SYSTEM SET pg_stat_monitor.pgsm_max_buckets = 10080; -- 保存7天的数据

2. 使用最佳实践

sql

-- 1. 定期清理旧的统计信息
-- 对于 pg_stat_statements
SELECT pg_stat_statements_reset();

-- 对于 pg_stat_monitor
SELECT pg_stat_monitor_reset();

-- 2. 结合慢查询日志使用
-- 启用慢查询日志
ALTER SYSTEM SET log_min_duration_statement = '1000ms';

-- 3. 定期分析查询统计
-- 创建一个定期分析任务
CREATE OR REPLACE FUNCTION analyze_query_stats()
RETURNS void AS $$
BEGIN
  -- 分析代码...
END;
$$ LANGUAGE plpgsql;

-- 4. 结合可视化工具使用
-- 将统计信息导出到 Grafana 等可视化工具

3. 性能优化建议

sql

-- 1. 优化查询统计的存储
-- 对于 pg_stat_statements
ALTER SYSTEM SET pg_stat_statements.max = 5000; -- 根据实际需求调整

-- 2. 减少不必要的统计
-- 对于 pg_stat_statements
ALTER SYSTEM SET pg_stat_statements.track = 'top'; -- 只跟踪顶层查询

-- 3. 调整采样率
-- 对于 pg_stat_monitor
ALTER SYSTEM SET pg_stat_monitor.pgsm_track = 'top';

-- 4. 调整时间窗口
ALTER SYSTEM SET pg_stat_monitor.pgsm_bucket_time = 300; -- 5分钟一个时间窗口

常见问题处理

1. 扩展加载失败

问题：无法加载 pg_stat_statements 或 pg_stat_monitor 扩展

解决方法：

检查 shared_preload_libraries 参数是否正确配置
确保扩展已经正确安装
检查数据库日志，查找具体的错误信息
确保数据库版本与扩展版本兼容

2. 统计信息不准确

问题：查询统计信息与实际情况不符

解决方法：

检查 track 参数是否设置为 'all'
确保扩展已经正确启用
尝试重置统计信息，重新开始统计
检查是否有其他工具或扩展影响了统计

3. 性能影响过大

问题：启用扩展后，数据库性能下降

解决方法：

减少跟踪的查询数量（调整 max 参数）
只跟踪顶层查询（设置 track = 'top'）
调整采样率
考虑使用 pg_stat_statements 代替 pg_stat_monitor

4. 统计信息丢失

问题：重启数据库后，统计信息丢失

解决方法：

确保 pg_stat_statements.save 参数设置为 'on'
对于 pg_stat_monitor，统计信息默认保存在内存中，重启后会丢失
考虑定期导出统计信息到外部存储

常见问题（FAQ）

Q1：pg_stat_statements 和 pg_stat_monitor 可以同时使用吗？

A1：可以同时使用，但需要注意：

两者都会消耗系统资源，可能会对性能产生影响
需要在 shared_preload_libraries 中同时配置两个扩展
建议根据实际需求选择合适的扩展，避免同时使用

Q2：如何选择合适的扩展？

A2：根据实际需求选择：

如果只需要基础的查询统计，选择 pg_stat_statements
如果需要详细的查询分析、执行计划或等待事件，选择 pg_stat_monitor
如果追求稳定性，选择 pg_stat_statements
如果需要高级分析功能，选择 pg_stat_monitor

Q3：如何查看查询的执行计划？

A3：使用 pg_stat_monitor 扩展可以查看查询的执行计划：

sql

SELECT queryid, query, planid, plan FROM pg_stat_monitor WHERE plan IS NOT NULL;

Q4：如何重置统计信息？

A4：可以使用以下命令重置统计信息：

对于 pg_stat_statements：SELECT pg_stat_statements_reset();
对于 pg_stat_monitor：SELECT pg_stat_monitor_reset();

Q5：如何查看特定用户的查询统计？

A5：可以根据 userid 过滤查询统计：

sql

-- 对于 pg_stat_statements
SELECT query, calls, total_time FROM pg_stat_statements WHERE userid = (SELECT oid FROM pg_user WHERE usename = 'myuser');

-- 对于 pg_stat_monitor
SELECT query, calls, total_time FROM pg_stat_monitor WHERE userid = (SELECT oid FROM pg_user WHERE usename = 'myuser');

Q6：如何导出统计信息？

A6：可以使用以下方法导出统计信息：

使用 COPY 命令导出到文件
使用第三方工具（如 Prometheus + Grafana）收集和展示
使用自定义脚本定期导出到外部存储

Q7：pg_stat_monitor 的统计信息保存多久？

A7：pg_stat_monitor 的统计信息保存时间取决于以下参数：

pgsm_bucket_time：每个时间窗口的大小（秒）
pgsm_max_buckets：最大的时间窗口数量
保存时间 = pgsm_bucket_time * pgsm_max_buckets

Q8：如何优化扩展的性能影响？

A8：可以采取以下措施：

减少跟踪的查询数量
只跟踪顶层查询
调整采样率
定期清理旧的统计信息
考虑在非高峰时段启用详细监控

PostgreSQL pg_stat_statements 与 pg_stat_monitor ​

pg_stat_statements ​

主要功能 ​

pg_stat_monitor ​

主要功能 ​

安装与配置 ​

1. pg_stat_statements 安装与配置 ​

安装 ​

配置 ​

2. pg_stat_monitor 安装与配置 ​

安装 ​

配置 ​

使用方法 ​

1. pg_stat_statements 使用示例 ​

2. pg_stat_monitor 使用示例 ​

功能对比 ​

1. 核心功能对比 ​

2. 适用场景对比 ​

最佳实践 ​

1. 配置最佳实践 ​

2. 使用最佳实践 ​

3. 性能优化建议 ​

常见问题处理 ​

1. 扩展加载失败 ​

2. 统计信息不准确 ​

3. 性能影响过大 ​

4. 统计信息丢失 ​

常见问题（FAQ） ​

Q1：pg_stat_statements 和 pg_stat_monitor 可以同时使用吗？ ​

Q2：如何选择合适的扩展？ ​

Q3：如何查看查询的执行计划？ ​

Q4：如何重置统计信息？ ​

Q5：如何查看特定用户的查询统计？ ​

Q6：如何导出统计信息？ ​

Q7：pg_stat_monitor 的统计信息保存多久？ ​

Q8：如何优化扩展的性能影响？ ​

PostgreSQL pg_stat_statements 与 pg_stat_monitor

pg_stat_statements

主要功能

pg_stat_monitor

主要功能

安装与配置

1. pg_stat_statements 安装与配置

安装

配置

2. pg_stat_monitor 安装与配置

安装

配置

使用方法

1. pg_stat_statements 使用示例

2. pg_stat_monitor 使用示例

功能对比

1. 核心功能对比

2. 适用场景对比

最佳实践

1. 配置最佳实践

2. 使用最佳实践

3. 性能优化建议

常见问题处理

1. 扩展加载失败

2. 统计信息不准确

3. 性能影响过大

4. 统计信息丢失

常见问题（FAQ）

Q1：pg_stat_statements 和 pg_stat_monitor 可以同时使用吗？

Q2：如何选择合适的扩展？

Q3：如何查看查询的执行计划？

Q4：如何重置统计信息？

Q5：如何查看特定用户的查询统计？

Q6：如何导出统计信息？

Q7：pg_stat_monitor 的统计信息保存多久？

Q8：如何优化扩展的性能影响？