openGauss pg_buffercache_page 知识点汇总

openGauss的pg_buffercache_page官方介绍

openGauss的pg_buffercache_page官方介绍
官方链接地址：
https://docs.opengauss.org/zh/docs/5.0.0/docs/SQLReference/%E5%85%B6%E4%BB%96%E7%B3%BB%E7%BB%9F%E5%87%BD%E6%95%B0.html

有别与pg 14/15，opengauss在pg_buffercache的统计处理上，只有一个pg_buffercache_pages的函数，不需要编译pg_buffercache代码，也不需要单独安装扩展，部署完就自带pg_buffercache_pages函数。

常见的pg_buffercache_pages使用SQL

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

SELECT n.nspname, c.relname, count(*) AS buffers
FROM pg_buffercache_pages() 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_pages()
where isdirty;

查看未使用buffer占用的大小

select count(*)*8/1024||'MB' 
from pg_buffercache_pages() 
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 usage_count 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_pages() 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,usage_count
FROM pg_class c
INNER JOIN pg_buffercache_pages() 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,usage_count
ORDER BY c.relname,usage_count;

检查缓冲区缓存的内容

select case
       when a.reldatabase = 0
            then '- global'
       when a.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 a.relfilenode > 0
            then '- unknown file ' || a.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 a.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_pages() a
     left join pg_database on pg_database.oid = a.reldatabase
     left join pg_class on pg_class.relfilenode = a.relfilenode
     left join pg_namespace on pg_namespace.oid = pg_class.relnamespace
group by 1
order by 2 desc;

参考文章链接：
https://www.modb.pro/db/131371