PostgreSQL的扩展 pg_buffercache
PostgreSQL的扩展 pg_buffercache
pg_buffercache 是 PostgreSQL 提供的一个关键性能监控扩展，用于观察共享缓冲区中的页面缓存情况。
一、扩展概述
- 功能：实时查看共享缓冲区的使用情况
- 用途：
- 性能分析与调优
- 缓存命中率分析
- 识别热点数据
- 内存配置优化
- 版本兼容：PostgreSQL 8.1+
二、安装与基本使用
1. 安装方法
CREATE EXTENSION pg_buffercache;-- 验证安装
SELECT * FROM pg_available_extensions WHERE name = 'pg_buffercache';
2. 核心视图说明
-- 查看视图结构
\d pg_buffercache-- 主要字段说明：
/*
bufferid - 缓冲区ID
relfilenode - 关联的文件节点
reltablespace - 表空间OID
reldatabase - 数据库OID
relblocknumber - 块号
isdirty - 是否为脏页
usagecount - 使用计数(时钟算法)
pinning_backend - 钉住该缓冲区的后端PID
*/
三、关键查询示例
1. 查看缓存分布
SELECT c.relname,count(*) AS buffers,round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer, 2) AS "% of cache"
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;
2. 识别热点表
SELECT c.relname,b.usagecount,count(*) AS buffers
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname, b.usagecount
ORDER BY c.relname, b.usagecount;
3. 检查脏页情况
SELECT c.relname,count(*) FILTER (WHERE b.isdirty) AS dirty_buffers,count(*) AS total_buffers
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
HAVING count(*) FILTER (WHERE b.isdirty) > 0
ORDER BY dirty_buffers DESC;
四、高级分析技术
1. 缓存命中率计算
WITH cache_data AS (SELECT c.relname,count(*) AS cached_blocksFROM pg_buffercache bJOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)GROUP BY c.relname
),
table_stats AS (SELECT relname,heap_blks_read + heap_blks_hit AS total_read,heap_blks_hit AS hitsFROM pg_statio_user_tables
)
SELECT t.relname,t.hits,t.total_read,round(t.hits * 100.0 / NULLIF(t.total_read, 0), 2) AS hit_ratio,c.cached_blocks
FROM table_stats t
JOIN cache_data c ON t.relname = c.relname
ORDER BY t.total_read DESC;
2. 索引缓存效率分析
SELECT c.relname AS index_name,count(*) AS cached_blocks,pg_size_pretty(count(*) * 8192) AS cached_size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relkind = 'i'
GROUP BY c.relname
ORDER BY cached_blocks DESC;
五、生产环境应用
1. 共享内存配置调优
-- 计算当前缓存利用率
SELECT (SELECT setting FROM pg_settings WHERE name='shared_buffers')::int AS shared_buffers,count(*) AS used_buffers,round(count(*) * 100.0 / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::int, 2) AS "% used"
FROM pg_buffercache;-- 调整建议（需重启）：
-- shared_buffers = 25% of total RAM (for dedicated DB servers)
2. 检查缓存竞争
SELECT c.relname,count(DISTINCT b.pinning_backend) AS pinned_by,count(*) AS total_blocks
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.pinning_backend IS NOT NULL
GROUP BY c.relname
ORDER BY pinned_by DESC;
六、与其它工具集成
1. 结合pg_stat_statements
-- 找出高频查询访问的表
WITH top_tables AS (SELECT query,(regexp_matches(query, 'FROM\s+(\w+)', 'gi'))[1] AS tablenameFROM pg_stat_statementsORDER BY calls DESCLIMIT 10
)
SELECT t.tablename,count(*) AS buffers,s.query
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN top_tables t ON c.relname = t.tablename
JOIN pg_stat_statements s ON t.query = s.query
GROUP BY t.tablename, s.query;
2. 导出缓存快照
# 生成缓存报告
psql -c "COPY (SELECT * FROM pg_buffercache) TO '/tmp/buffer_cache_snapshot.csv' WITH CSV HEADER"
七、性能注意事项
-
查询开销：
- pg_buffercache需要获取共享缓冲区锁
- 生产环境避免高频查询（>1次/分钟）
- 建议在维护窗口或低峰期执行
-
替代方案：
-- 轻量级替代（不显示具体内容） SELECT * FROM pg_stat_bgwriter;-- 检查点活动监控 SELECT * FROM pg_stat_checkpoints;
八、扩展限制
- 需要超级用户权限
- 仅显示当前数据库集群的缓存
- 不显示系统目录缓存细节（除非明确查询）
- 结果具有瞬时性，可能很快变化
pg_buffercache 是PostgreSQL性能调优的核心工具之一，合理使用可以帮助DBA优化内存配置、识别性能瓶颈，但需要注意查询本身对系统性能的影响。
更详细的内容请查看官方文档：
https://www.postgresql.org/docs/17/pgbuffercache.html