统计PostgreSQL缓存情况

可以通过以下步骤查询PostgreSQL数据的缓存情况。

1. 创建pg_buffercache扩展。

sde=# create extension pg_buffercache;
CREATE EXTENSION

2. 创建该extension后，后台会创建pg_buffercache视图用来记录缓存情况，其定义如下，实际上是简历在表函数上的一个视图

sde=# \d+ pg_buffercache
                          视图 "sde.pg_buffercache"
       栏位       |   类型   | Collation | Nullable | Default | 存储  | 描述
------------------+----------+-----------+----------+---------+-------+------
 bufferid         | integer  |           |          |         | plain |
 relfilenode      | oid      |           |          |         | plain |
 reltablespace    | oid      |           |          |         | plain |
 reldatabase      | oid      |           |          |         | plain |
 relforknumber    | smallint |           |          |         | plain |
 relblocknumber   | bigint   |           |          |         | plain |
 isdirty          | boolean  |           |          |         | plain |
 usagecount       | smallint |           |          |         | plain |
 pinning_backends | integer  |           |          |         | plain |
视图定义:
 SELECT p.bufferid,
    p.relfilenode,
    p.reltablespace,
    p.reldatabase,
    p.relforknumber,
    p.relblocknumber,
    p.isdirty,
    p.usagecount,
    p.pinning_backends
   FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, isdirty boolean, usagecount smallint, pinning_backends integer);

relfilenode 表示文件的oid，与pg_class的relfilenode对应

reltablespace代表表空间的oid，与pg_tablespace的oid对应

reldatabase代表database的oid，与pg_database的oid对应

relforknumber代表被缓存是表的哪个部分，堆，空闲空间映射还是可见性映射表

relblocknumber代表被缓存的块号

isdirty代表该快是否为脏块

3. 查询每个数据库中缓存的概要情况

sde=# select datname,count(*),count(*) filter(where isdirty=true) as dirty from pg_buffercache as b,pg_database as d where d.oid=b.reldatabase group by rollup(1) order by 1 asc nulls last;
  datname  | count | dirty
-----------+-------+-------
 postgres  |    59 |     0
 sde       |  1390 |     0
 sde1      |    62 |     0
 template1 |    59 |     0
 test      |    59 |     0
           |  1629 |     0

4. 查询某个数据中的表的缓存情况

sde=# select relname,relkind,count(*) as count,count(*) filter(where isdirty=true) as dirtycount from pg_buffercache as b,pg_database as d,pg_class as c where d.oid=b.reldatabase and c.relfilenode=b.relfilenode and datname='sde' group by 1,2  order by 3 desc nulls first limit 10;
          relname          | relkind | count | dirtycount
---------------------------+---------+-------+------------
 testddd                   | r       |  3420 |       2501
 pg_statistic              | r       |    35 |          1
 pg_depend                 | r       |    16 |          0
 pg_operator               | r       |    15 |          0
 pg_depend_reference_index | i       |    15 |          0
 pg_index                  | r       |    13 |          0
 pg_depend_depender_index  | i       |     8 |          0
 pg_toast_2619             | t       |     8 |          2
 pg_amop                   | r       |     7 |          0
 pg_rewrite                | r       |     7 |          0

5. checkpoint下，发现testddd表中的dirty为0了

sde=# checkpoint;
CHECKPOINT
sde=# select relname,relkind,count(*) as count,count(*) filter(where isdirty=true) as dirtycount from pg_buffercache as b,pg_database as d,pg_class as c where d.oid=b.reldatabase and c.relfilenode=b.relfilenode and datname='sde' group by 1,2  order by 3 desc nulls first limit 10;
          relname          | relkind | count | dirtycount
---------------------------+---------+-------+------------
 testddd                   | r       |  3420 |          0
 pg_statistic              | r       |    38 |          0
 pg_operator               | r       |    16 |          0
 pg_depend                 | r       |    16 |          0
 pg_depend_reference_index | i       |    15 |          0
 pg_index                  | r       |    13 |          0
 pg_depend_depender_index  | i       |     8 |          0
 pg_toast_2619             | t       |     8 |          0
 pg_amop                   | r       |     7 |          0
 pg_rewrite                | r       |     7 |          0