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;