postgresql pg_buffercache

pg_buffercache模块是用于查看shared buffer cache信息，决定shared buffer cache大还是小。

Installing pg_buffercache into a database：

$ createdb pgbench
$ psql -d pgbench -f /usr/share/postgresql/contrib/pg_buffercache.sql

两步即可完成

pg_buffercache.sql内容：

/* contrib/pg_buffercache/pg_buffercache--1.0.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_buffercache" to load this file. \quit

-- Register the function.
CREATE FUNCTION pg_buffercache_pages()
RETURNS SETOF RECORD
AS 'MODULE_PATHNAME', 'pg_buffercache_pages'
LANGUAGE C;

-- Create a view for convenient access.
CREATE VIEW pg_buffercache AS
SELECT P.* FROM pg_buffercache_pages() AS P
(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
relforknumber int2, relblocknumber int8, isdirty bool, usagecount int2);

-- Don't want these to be available to public.
REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
REVOKE ALL ON pg_buffercache FROM PUBLIC;

创建函数和视图，回收PUBLIC 权限。

Name	Type	References	Description
bufferid	integer		ID, in the range 1..shared_buffers
relfilenode	oid	pg_class.relfilenode	Filenode number of the relation
reltablespace	oid	pg_tablespace.oid	Tablespace OID of the relation
reldatabase	oid	pg_database.oid	Database OID of the relation
relblocknumber	bigint		Page number within the relation
relforknumber	smallint		Fork number within the relation
isdirty	boolean		Is the page dirty?
usagecount	smallint		Page LRU count

pg_buffercache使用：

查看shared buffers大小：

postgres=# SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='shared_buffers';
name | setting | unit | current_setting
----------------+---------+------+-----------------
shared_buffers | 4096 | 8kB | 32MB
(1 row)

postgres=# select count(*) from pg_buffercache;

count
-------
4096
(1 row)

可见block数量一致，大小一致。

查看当前数据库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;

relname | buffers
---------------------------+---------
pg_statistic | 15
pg_operator | 13
pg_depend_reference_index | 13
pg_depend | 13
pg_rewrite | 8
pg_depend_depender_index | 6
pg_toast_2619 | 6
pg_index | 6
pg_extension | 5
pg_namespace | 5
(10 rows)

使用pg_buffercache比较灵活，可以通过isdirty字段查询脏块，如果是未使用的buffer，那么除了bufferid，其他字段都为空值。

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

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;

查看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;

relname | buffered | buffers_percent | percent_of_relation
----------------------------------+----------+-----------------+---------------------
pg_statistic | 120 kB | 0.4 | 100.0
pg_depend | 104 kB | 0.3 | 29.5
pg_operator | 104 kB | 0.3 | 100.0
pg_depend_reference_index | 104 kB | 0.3 | 50.0
pg_rewrite | 64 kB | 0.2 | 66.7
pg_operator_oid_index | 32 kB | 0.1 | 100.0
pg_statistic_relid_att_inh_index | 32 kB | 0.1 | 100.0
pg_operator_oprname_l_r_n_index | 40 kB | 0.1 | 100.0
pg_depend_depender_index | 48 kB | 0.1 | 22.2
pg_amop_fam_strat_index | 32 kB | 0.1 | 100.0

缓冲区使用分布：

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;

relname | buffers | usagecount
-----------------------------------+---------+------------
pg_aggregate | 1 | 5
pg_aggregate_fnoid_index | 1 | 4
pg_aggregate_fnoid_index | 1 | 5
pg_am | 1 | 5
pg_amop | 3 | 5
pg_amop_fam_strat_index | 1 | 1
pg_amop_fam_strat_index | 3 | 5
pg_amop_opr_fam_index | 3 | 5
pg_amproc | 1 | 4
pg_amproc | 1 | 5
pg_amproc_fam_proc_index | 2 | 5
pg_attrdef | 1 | 3
pg_attrdef_adrelid_adnum_index | 2 | 3
pg_attrdef_oid_index | 1 | 1
pg_attrdef_oid_index | 1 | 2
pg_cast | 2 | 5
pg_cast_source_target_index | 2 | 5
pg_collation | 1 | 1
pg_collation_oid_index | 1 | 3
pg_collation_oid_index | 2 | 5
pg_constraint | 1 | 1
pg_default_acl_role_nsp_obj_index | 1 | 5
pg_depend | 3 | 1
pg_depend | 1 | 2
pg_depend | 9 | 5
pg_depend_depender_index | 1 | 4
pg_depend_depender_index | 5 | 5
pg_depend_reference_index | 2 | 1
pg_depend_reference_index | 1 | 2
pg_depend_reference_index | 1 | 4
pg_depend_reference_index | 9 | 5

太长这里截取部分。