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"

七、性能注意事项

1. 查询开销：

   • pg_buffercache需要获取共享缓冲区锁
   • 生产环境避免高频查询（>1次/分钟）
   • 建议在维护窗口或低峰期执行

2. 替代方案：

   -- 轻量级替代（不显示具体内容）
   SELECT * FROM pg_stat_bgwriter;-- 检查点活动监控
   SELECT * FROM pg_stat_checkpoints;
   

八、扩展限制

1. 需要超级用户权限
2. 仅显示当前数据库集群的缓存
3. 不显示系统目录缓存细节（除非明确查询）
4. 结果具有瞬时性，可能很快变化

pg_buffercache 是PostgreSQL性能调优的核心工具之一，合理使用可以帮助DBA优化内存配置、识别性能瓶颈，但需要注意查询本身对系统性能的影响。

更详细的内容请查看官方文档：

https://www.postgresql.org/docs/17/pgbuffercache.html