pg_buffercache安装及使用

pg_buffercache模块提供了一种方法实时检查共享缓冲区。默认情况下，使用仅限于超级用户和pg_read_all_stats 角色的成员。可以使用GRANT给其他人授予访问权限。pg_buffercache模块可以用来查看shared buffer cache信息，决定shared buffer cache大还是小。

一、安装

[postgres@localhost pg_buffercache]$ pwd
/opt/postgresql-12.1/contrib/pg_buffercache
[postgres@localhost pg_buffercache]$ gmake
[postgres@localhost pg_buffercache]$ gmake install

进到数据库里

postgres=# create extension pg_buffercache;
CREATE EXTENSION

pg_buffercache安装成功

二、pg_buffercache描述


因为缓冲是所有数据库共享的，通常会有不属于当前数据库的关系的页面。 这意味着对于一些行在pg_class中可能不会有匹配的连接行，或者甚至有错误的连接。 如果试图与pg_class连接，最好将连接限制于reldatabase 等于当前数据库 OID 或零的行。当访问pg_buffercache视图时， 内部缓冲区管理器会被锁住足够长时间来拷贝视图将显示的所有缓冲区状态数据。 这确保了该视图会产生一个一致的结果集合，而不会不必要地长时间阻塞普通的缓冲区活动。 尽管如此，如果经常读取这个视图还是会对数据库性能产生一些影响。pg_buffercache 既使用自身的缓冲区，也使用内核缓冲IO。这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。这被称为双重缓冲区处理。

三、pg_buffercache使用
1.

SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='shared_buffers';

select count(*) from pg_buffercache;

和shared_buffers的block数量一致，大小一致。

2.查看当前数据库buffer的使用情况排名

SELECT
c.relname,
count(*) AS buffers
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())
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

3.可以通过isdirty字段查询脏块，如果是未使用的buffer，那么除了bufferid，其他字段都为空值

select count(*) from pg_buffercache where isdirty is true;

查看未使用buffer占用的大小

select count(*)*8/1024||'MB' from pg_buffercache where relfilenode is null and reltablespace is null and reldatabase is null and relforknumber is null and relblocknumber is null and isdirty is null and usagecount is null;

4.查看buffercache对象的使用大小以及百分比

SELECT
c.relname,
pg_size_pretty(count(*) * 8192) as buffered,
round(100.0 * count(*) /
(SELECT setting FROM pg_settings
WHERE name='shared_buffers')::integer,1)
AS buffers_percent,
round(100.0 * count(*) * 8192 /
pg_relation_size(c.oid),1)
AS percent_of_relation
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())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 10;

5.缓冲区使用分布

SELECT
c.relname, count(*) AS buffers,usagecount
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())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount;

6.检查缓冲区缓存的内容

select case
       when pg_buffercache.reldatabase = 0
            then '- global'
       when pg_buffercache.reldatabase <> (select pg_database.oid from pg_database where pg_database.datname = current_database())
            then '- database ' || quote_literal(pg_database.datname)
       when pg_namespace.nspname = 'pg_catalog'
            then '- system catalogues'
       when pg_class.oid is null and pg_buffercache.relfilenode > 0
            then '- unknown file ' || pg_buffercache.relfilenode
       when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+$'
            then (substring(pg_class.relname,10)::oid)::regclass || ' TOAST'::text
       when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+_index$'
            then ((rtrim(substring(pg_class.relname,10),'_index'))::oid)::regclass || ' TOAST index'
       else pg_class.oid::regclass::text
       end as key,count(*) as buffers,sum(case when pg_buffercache.isdirty then 1 else 0 end) as dirty_buffers,round(count(*) / (SELECT pg_settings.setting FROM pg_settings WHERE pg_settings.name = 'shared_buffers')::numeric,4) as hog_factor
from pg_buffercache
     left join pg_database on pg_database.oid = pg_buffercache.reldatabase
     left join pg_class on pg_class.relfilenode = pg_buffercache.relfilenode
     left join pg_namespace on pg_namespace.oid = pg_class.relnamespace
group by 1
order by 2 desc;