postgresqlpg_buffercache

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了postgresqlpg_buffercache，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6854字，纯文字阅读大概需要10分钟。

内容图文

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

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

内容总结

以上是互联网集市为您收集整理的postgresqlpg_buffercache全部内容，希望文章能够帮你解决postgresqlpg_buffercache所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---