postgresql pg_buffercache

191145685

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 权限。

NameTypeReferencesDescriptionbufferidintegerID, in the range 1..shared_buffersrelfilenodeoidpg_class.relfilenodeFilenode number of the relationreltablespaceoidpg_tablespace.oidTablespace OID of the relationreldatabaseoidpg_database.oidDatabase OID of the relationrelblocknumberbigintPage number within the relationrelforknumbersmallintFork number within the relationisdirtybooleanIs the page dirty?usagecountsmallintPage 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
太长这里截取部分。