PostgreSQL16 pg_buffercache 知识点汇总

pg_buffercache官方介绍

pg_buffercache 在pg14版本中的官方介绍
官方链接地址：
https://www.postgresql.org/docs/14/pgbuffercache.html

pg_buffercache 在pg15版本中的官方介绍
官方链接地址：
https://www.postgresql.org/docs/15/pgbuffercache.html

pg_buffercache 在pg16版本中的官方介绍
官方链接地址：
https://www.postgresql.org/docs/16/pgbuffercache.html

pg_buffercache 在pg17版本中的官方介绍
官方链接地址：
https://www.postgresql.org/docs/17/pgbuffercache.html

本次测试将会根据pg16版本进行测试，相较于pg14和pg15版本，该扩展增加了
pg_buffercache_pages 函数
pg_buffercache_summary 函数
pg_buffercache_usage_counts 函数

pg_buffercahce介绍

该模块提供了一种实时检查共享缓冲区缓存中发生的情况的方法。

该模块提供了pg_buffercache_pages()函数（在pg_buffercache视图中封装）、pg_buffercache_summary()函数和pg_buffercache_usage_counts()函数。

1. pg_buffercache_pages()函数返回一组记录，每行描述一个共享缓冲区条目的状态。pg_buffercache视图封装了该函数，便于使用。

2. pg_buffercache_summary()函数返回一行，概括共享缓冲区缓存的状态。

3. pg_buffercache_usage_counts()函数返回一组记录，每行描述具有给定使用次数的缓冲区数量。

默认情况下，仅超级用户和pg_monitor角色的成员可以使用。可以使用GRANT语句将访问权限授予其他人。

视图 pg_buffercache 字段说明

官方截图

pg_buffercache安装

前提条件：需要源码包。也就是pg是源码安装的，且安装包还在。

1. 进入标准的 contrib 模块的pg_buffercache目录

cd contrib/pg_buffercache/

2. 编译、安装

gmake
gmake install

3. 进入数据库，添加pg_buffercache扩展

psql test
create extension pg_buffercache ;
\dv pg_buffercache
\df pg_buffercache_pages
\df pg_buffercache_summary
\df pg_buffercache_usage_counts

常见的pg_buffercache使用SQL

查看当前数据库buffer的使用情况排名TOP 10

SELECT n.nspname, c.relname, count(*) AS buffers
FROM pg_buffercache b JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
   b.reldatabase IN (0, (SELECT oid FROM pg_database
                         WHERE datname = current_database()))
JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY n.nspname, c.relname
ORDER BY 3 DESC
LIMIT 10;

查看脏页缓冲区

select count(*), pg_size_pretty(count(*) * 8 * 1024)
from pg_buffercache
where isdirty;

查看未使用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;

查看buffer cache对象的使用大小以及百分比

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;

缓冲区使用分布

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;

检查缓冲区缓存的内容

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;

pg_buffercache_summary 函数

pg_buffercache_summary()函数返回一行数据，概括了所有共享缓冲区的状态。
使用pg_buffercache视图提供的信息更详细的信息。
使用pg_buffercache_summary()函数的开销要显著更低。
与pg_buffercache视图一样，pg_buffercache_summary()函数不会获取缓冲区管理器的锁。因此，并发活动可能会导致结果出现轻微的不准确。

使用方法：

select * from pg_buffercache_summary();

pg_buffercache_usage_counts 函数

pg_buffercache_usage_counts()函数返回一组行，这些行汇总了所有共享缓冲区在不同可能使用次数值下的状态。虽然pg_buffercache视图提供了类似且更详细的信息，但使用pg_buffercache_usage_counts()函数的开销要显著更低。
与pg_buffercache视图一样，pg_buffercache_usage_counts()函数也不会获取缓冲区管理器的锁。因此，并发活动可能会导致结果出现轻微的不准确。
buffers的和与pg_buffercache视图的总行数相同

使用方法：

select * from pg_buffercache_usage_counts();