缓存视图——pg_buffercache

作用: 查看 shared buffers 缓存内容(数据库级别)
大多数情况设置 shared_buffers 为内存的25%
安装方法
```
create extension pg_buffercache;
```
教程

pg_buffercache安装及使用_ITPUB博客
查询缓存命中数
```
SELECT usagecount, count(*), isdirty
FROM pg_buffercache
GROUP BY isdirty, usagecount
ORDER BY isdirty, usagecount
```
- 结果
  - usagecount列大约有5种值: 1、2、3、4、5
  - count列显示了这5种值对应的数量
  - 如果
    - 0，1代表的数量特别多，表明 shared_buffers 过大
    - 4，5代表的数量特别多，表明 shared_buffers 过小

查询数据在缓存中的占比

select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered,
       round(100.0 * count(*) / (select setting from pg_settings  where name='shared_buffers')::integer,1) as pgbuffer_percent,
       round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
       (select round( sum(pages_mem) * 4 /1024,0 ) from pgfincore(c.relname::text) ) as os_cache_MB ,
         round(100 * (  select sum(pages_mem)*4096 from pgfincore(c.relname::text) )/ pg_table_size(c.oid),1) as os_cache_percent_of_relation,
         pg_size_pretty(pg_table_size(c.oid)) as rel_size
 from pg_class c
 inner join pg_buffercache b on b.relfilenode=c.relfilenode
 inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()
            and c.relnamespace=(select oid from pg_namespace where nspname='public'))
 group by c.oid,c.relname
 order by 3 desc limit 30;

Keyboard shortcuts

缓存视图——pg_buffercache