合 PG使用插件pg_buffercache查询缓冲区数据
Tags： PG优化插件pg_buffercache清空缓存pg_dropcacheshared Buffer
👉 本文共约1563个字，系统预计阅读时间或需6分钟。
pg_buffercache
pg_buffercache模块提供了一种方法实时检查共享缓冲区。
该模块提供了一个 C 函数pg_buffercache_pages，它返回一个记录的集合，外加一个包装了该函数以便于使用的视图pg_buffercache。
默认情况下，使用仅限于超级用户和pg_monitor 角色的成员。可以使用GRANT给其他人授予访问权限。
安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | postgres=# select * from pg_available_extensions where name like '%pg_buffercache%' order by name; name | default_version | installed_version | comment ----------------+-----------------+-------------------+--------------------------------- pg_buffercache | 1.3 | 1.3 | examine the shared buffer cache (1 row) postgres=# create extension pg_buffercache ; CREATE EXTENSION postgres=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ pageinspect | 1.8 | public | inspect the contents of database pages at a low level pg_buffercache | 1.3 | public | examine the shared buffer cache pg_prewarm | 1.2 | public | prewarm relation data pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (5 rows) postgres=# \dx pg_buffercache List of installed extensions Name | Version | Schema | Description ----------------+---------+--------+--------------------------------- pg_buffercache | 1.3 | public | examine the shared buffer cache (1 row) postgres=# \dx+ pg_buffercache Objects in extension "pg_buffercache" Object description --------------------------------- function pg_buffercache_pages() view pg_buffercache (2 rows) |
pg_buffercache视图
表 F.15. pg_buffercache 列
| 列类型描述 |
|---|
bufferid integerID，在范围 1..shared_buffers中 |
relfilenode oid (references pg_class.relfilenode)关系的文件结点编号 |
reltablespace oid (references pg_tablespace.oid)关系的表空间 OID |
reldatabase oid (references pg_database.oid)关系的数据库 OID |
relforknumber smallint关系内的分叉数，见include/common/relpath.h |
relblocknumber bigint关系内的页面数 |
isdirty boolean页面是否为脏？ |
usagecount smallintClock-sweep 访问计数 |
pinning_backends integer对这个缓冲区加 pin 的后端数量 |
共享缓存中的每一个缓冲区都有一行。没有使用的缓冲区的行中只有bufferid为非空。共享的系统目录被显示为属于数据库零。
因为缓冲是所有数据库共享的，通常会有不属于当前数据库的关系的页面。这意味着对于一些行在pg_class中可能不会有匹配的连接行，或者甚至有错误的连接。如果你试图与pg_class连接，将连接限制于reldatabase等于当前数据库 OID 或零的行是一个好主意。
由于缓冲区管理器锁不会用于复制视图将显示的缓冲区状态数据，因此访问pg_buffercache视图对正常缓冲区活动的影响较小，但它不会在所有缓冲区中提供一致的结果集。但是，我们确保每个缓冲区的信息是自洽的。
样例输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | regression=# SELECT n.nspname, c.relname, count(*) AS buffers FROM pg_buffercache 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; nspname | relname | buffers ------------+---------------------------------+--------- public | pgbench_accounts | 8203 public | pgbench_accounts_pkey | 1370 pg_catalog | pg_attribute | 33 pg_catalog | pg_proc | 16 pg_catalog | pg_class | 16 pg_catalog | pg_depend_reference_index | 15 pg_catalog | pg_depend | 11 pg_catalog | pg_proc_oid_index | 10 pg_catalog | pg_statistic | 9 pg_catalog | pg_attribute_relid_attnum_index | 8 (10 rows) |
方法一：需要停机：
1 2 3 4 | pg_ctl stop sync echo 3 > /proc/sys/vm/drop_caches pg_ctl start |
方法二：使用pg_dropcache插件：https://github.com/zilder/pg_dropcache
pg_dropcache is a PostgreSQL extension that invalidates shared_buffers cache
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
