PostgreSQL 性能模式（Performance Schema）

性能模式配置

1. 启用性能模式

PostgreSQL 从 13 版本开始，性能模式默认启用。可以通过以下方式检查和配置：

sql

-- 检查性能模式是否启用
SHOW pg_stat_statements.max;

-- 启用性能模式（如果未启用）
ALTER SYSTEM SET shared_preload_libraries = '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;
ALTER SYSTEM SET pg_stat_statements.save = on;

-- 重启数据库使配置生效
-- sudo systemctl restart postgresql

2. 扩展安装

需要安装 pg_stat_statements 扩展才能使用性能模式：

sql

-- 安装 pg_stat_statements 扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 检查扩展是否安装成功
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

3. 性能模式参数配置

参数名	默认值	描述
pg_stat_statements.max	5000	存储的语句历史记录数量
pg_stat_statements.track	top	跟踪级别：none, top, all
pg_stat_statements.track_utility	off	是否跟踪工具命令（如CREATE, ALTER等）
pg_stat_statements.save	on	服务器关闭时是否保存统计信息
pg_stat_statements.track_planning	off	是否跟踪查询计划阶段的时间

性能模式监控指标

1. 语句执行统计

sql

-- 查看语句执行统计信息
SELECT 
    queryid, 
    query, 
    calls, 
    total_exec_time, 
    mean_exec_time, 
    min_exec_time, 
    max_exec_time, 
    rows, 
    shared_blks_hit, 
    shared_blks_read, 
    shared_blks_dirtied, 
    shared_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查看语句执行计划统计（PostgreSQL 14+）
SELECT 
    queryid, 
    query, 
    calls, 
    planning_time, 
    total_plan_time,
    mean_plan_time
FROM pg_stat_statements
WHERE planning_time > 0
ORDER BY total_plan_time DESC
LIMIT 10;

2. 等待事件统计

sql

-- 查看等待事件统计（需要pg_wait_sampling扩展）
CREATE EXTENSION IF NOT EXISTS pg_wait_sampling;

-- 查看等待事件采样配置
SHOW pg_wait_sampling.history_size;
SHOW pg_wait_sampling.profile_period;

-- 查看等待事件历史
SELECT * FROM pg_wait_sampling_history;

-- 查看等待事件概要
SELECT * FROM pg_wait_sampling_profile;

3. 锁统计

sql

-- 查看锁等待统计
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

4. 缓冲区统计

sql

-- 查看缓冲区使用情况
SELECT 
    datname, 
    buffers_alloc, 
    buffers_hit, 
    buffers_miss, 
    buffers_dirtied, 
    buffers_written
FROM pg_stat_database
ORDER BY datname;

-- 计算缓冲区命中率
SELECT 
    datname, 
    buffers_alloc, 
    buffers_hit, 
    buffers_miss, 
    CASE 
        WHEN buffers_hit + buffers_miss = 0 THEN 0
        ELSE buffers_hit / (buffers_hit + buffers_miss)::float * 100
    END AS hit_ratio
FROM pg_stat_database
ORDER BY datname;

性能模式最佳实践

1. 合理配置参数

根据数据库负载调整 pg_stat_statements.max 参数
对于生产环境，建议设置 track = all 以获取完整的统计信息
启用 track_utility 以监控管理命令的执行情况
对于大型数据库，考虑增加 pg_stat_statements.max 的值

2. 定期清理统计信息

sql

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

-- 定期执行（可以放在cron任务中）
-- 例如：每月1号凌晨2点重置
-- 0 2 1 * * psql -c "SELECT pg_stat_statements_reset();"

3. 结合监控工具使用

将性能模式与 Prometheus + Grafana 结合使用，实现可视化监控
配置告警规则，当性能指标超过阈值时发送告警
使用 pgBadger 等工具生成性能报告

4. 分析慢查询

sql

-- 查找执行时间最长的10条语句
SELECT 
    query, 
    calls, 
    total_exec_time, 
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 查找调用次数最多的10条语句
SELECT 
    query, 
    calls, 
    total_exec_time, 
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- 查找扫描行数最多的10条语句
SELECT 
    query, 
    calls, 
    total_exec_time, 
    rows,
    shared_blks_read
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

性能模式性能影响

1. 性能开销

启用性能模式会带来一定的性能开销，通常在 1-5% 之间
开销主要来自于语句跟踪和统计信息存储
对于高并发环境，建议评估性能开销后再决定是否启用

2. 优化性能开销

调整 pg_stat_statements.max 参数，避免存储过多历史记录
合理设置 track 级别，只跟踪必要的语句
定期清理统计信息，减少存储空间占用
考虑在非高峰期启用性能模式进行临时监控

常见问题及解决方案

1. 性能模式未收集到统计信息

可能原因：

性能模式未正确启用
扩展未安装
参数配置不当

解决方案：

sql

-- 检查扩展是否安装
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- 检查参数配置
SHOW pg_stat_statements.track;
SHOW pg_stat_statements.max;

-- 重新安装扩展
DROP EXTENSION IF EXISTS pg_stat_statements;
CREATE EXTENSION pg_stat_statements;

2. 统计信息不准确

可能原因：

统计信息未定期清理
语句被截断或参数化
跟踪级别设置不当

解决方案：

sql

-- 清理统计信息
SELECT pg_stat_statements_reset();

-- 调整参数
ALTER SYSTEM SET pg_stat_statements.max = 20000;
ALTER SYSTEM SET pg_stat_statements.track = all;

3. 性能模式导致数据库性能下降

可能原因：

性能模式参数配置不当
统计信息存储过多
跟踪级别过高

解决方案：

sql

-- 调整参数降低性能开销
ALTER SYSTEM SET pg_stat_statements.track = top;
ALTER SYSTEM SET pg_stat_statements.max = 5000;
ALTER SYSTEM SET pg_stat_statements.track_utility = off;

-- 清理统计信息
SELECT pg_stat_statements_reset();

常见问题（FAQ）

Q1：PostgreSQL 性能模式与慢查询日志有什么区别？

A1：性能模式与慢查询日志的主要区别：

收集范围：性能模式收集所有语句的统计信息，慢查询日志只记录超过阈值的语句
统计粒度：性能模式提供更详细的统计信息，如执行次数、平均执行时间、缓冲区使用等
查询方式：性能模式可以通过SQL查询实时获取统计信息，慢查询日志需要解析日志文件
性能开销：性能模式的性能开销相对较高，慢查询日志的开销较低
持久化：性能模式的统计信息可以持久化保存，慢查询日志需要单独管理

Q2：如何选择合适的性能模式跟踪级别？

A2：根据业务需求选择合适的跟踪级别：

none：不跟踪任何语句，适用于性能敏感的生产环境
top：只跟踪顶层语句，不跟踪嵌套语句，适用于大多数生产环境
all：跟踪所有语句，包括嵌套语句，适用于开发和测试环境，或需要详细统计的场景

Q3：性能模式的统计信息会占用多少存储空间？

A3：性能模式的存储空间占用取决于：

pg_stat_statements.max 参数值
语句的复杂程度和长度
跟踪级别
数据库的负载情况

通常情况下，性能模式的存储空间占用较小，对于大多数环境，设置 pg_stat_statements.max = 10000 大约占用几 MB 到几十 MB 的存储空间。

Q4：如何备份和恢复性能模式的统计信息？

A4：性能模式的统计信息默认保存在 pg_stat_statements 系统表中，会随着数据库备份一起备份。可以通过以下方式备份和恢复：

sql

-- 备份统计信息（可选）
CREATE TABLE pg_stat_statements_backup AS SELECT * FROM pg_stat_statements;

-- 恢复统计信息（可选）
INSERT INTO pg_stat_statements SELECT * FROM pg_stat_statements_backup;

Q5：性能模式可以跟踪哪些类型的语句？

A5：性能模式可以跟踪：

SELECT、INSERT、UPDATE、DELETE 等数据操作语句
CREATE、ALTER、DROP 等数据库对象管理语句
GRANT、REVOKE 等权限管理语句
EXPLAIN、ANALYZE 等工具语句
存储过程和函数的执行

可以通过 pg_stat_statements.track_utility 参数控制是否跟踪工具语句。

PostgreSQL 性能模式（Performance Schema） ​

性能模式配置 ​

1. 启用性能模式 ​

2. 扩展安装 ​

3. 性能模式参数配置 ​

性能模式监控指标 ​

1. 语句执行统计 ​

2. 等待事件统计 ​

3. 锁统计 ​

4. 缓冲区统计 ​

性能模式最佳实践 ​

1. 合理配置参数 ​

2. 定期清理统计信息 ​

3. 结合监控工具使用 ​

4. 分析慢查询 ​

性能模式性能影响 ​

1. 性能开销 ​

2. 优化性能开销 ​

常见问题及解决方案 ​

1. 性能模式未收集到统计信息 ​

2. 统计信息不准确 ​

3. 性能模式导致数据库性能下降 ​

常见问题（FAQ） ​

Q1：PostgreSQL 性能模式与慢查询日志有什么区别？ ​

Q2：如何选择合适的性能模式跟踪级别？ ​

Q3：性能模式的统计信息会占用多少存储空间？ ​

Q4：如何备份和恢复性能模式的统计信息？ ​

Q5：性能模式可以跟踪哪些类型的语句？ ​

PostgreSQL 性能模式（Performance Schema）

性能模式配置

1. 启用性能模式

2. 扩展安装

3. 性能模式参数配置

性能模式监控指标

1. 语句执行统计

2. 等待事件统计

3. 锁统计

4. 缓冲区统计

性能模式最佳实践

1. 合理配置参数

2. 定期清理统计信息

3. 结合监控工具使用

4. 分析慢查询

性能模式性能影响

1. 性能开销

2. 优化性能开销

常见问题及解决方案

1. 性能模式未收集到统计信息

2. 统计信息不准确

3. 性能模式导致数据库性能下降

常见问题（FAQ）

Q1：PostgreSQL 性能模式与慢查询日志有什么区别？

Q2：如何选择合适的性能模式跟踪级别？

Q3：性能模式的统计信息会占用多少存储空间？

Q4：如何备份和恢复性能模式的统计信息？

Q5：性能模式可以跟踪哪些类型的语句？