PostgreSQL pg_buffercache

#PostgreSQL #pg_buffercache

1 背景知识

由于postgresql 轻量级的特性，PostgreSQL必须高度依赖操作系统缓存，它依赖于操作系统来了解文件系统、磁盘布局以及如何读写数据文件。

本章主要使用 pg_buffercache 插件查看共享缓冲区，也可以使用 pgfincore 插件查看操作系统缓存区（OS CACHE ）。

1.1 pg_buffercache 扩展

1、 pg_buffercache 扩展模块可以查看共享内存区中的内容。
2、 pg_buffercache 扩展模块提供以下 pg_buffercache_pages() 函数
3、此函数描述了共享内存的所有缓冲区的状态，现在已经由 pg_buffercache 视图封装。直接查询视图即可。

Warning

超级管理员和pg_monitor 角色拥有查看共享缓冲区信息的权限。

1.2 `pg_buffercache` 视图

名称	描述
bufferid	ID范围是1到shared_buffers。
relfilenode	关系的文件节点号。
reltablespace	关系的表空间OID。
reldatabase	关系的数据库OID。
relforknumber	关系内的分叉树。
relblocknumber	关系内的页面数。
isdirty	页面是否为脏。
usagecount	clock-sweep访问计数。
pinning_backends	对这个缓冲区加pin的后端数量。

1、共享缓存中每一行代表每个缓冲区页面，一般页面为 8kb。
2、除了bufferid以外，未使用的缓冲区页面的所有列为null。
3、数据字典在缓冲区中的数据库OID 为0。
4、如果想要查询数据字典的缓冲区，或者关联数据字典，请添加 where 条件中 where reldatabase=0。
5、使用 pg_buffercache 访问共享缓存区时，不会在内存中加锁，所以无法保障读取一致性。

1.3 pgfincore 扩展

pgfincore 扩展模块，可以检查在操作系统级别缓存的数据包。

2 测试环境准备

2.1 pg_buffercache 扩展安装

su - postgres
psql -U postgres -d testdb
CREATE EXTENSION pg_buffercache;

2.2 pgfincore 扩展安装

1、编译和安装。

su - postgres
cd /soft
git clone https://github.com/klando/pgfincore.git
cd pgfincore/
make -j 8 && make install

2、加载插件。

psql -U postgres -d testdb
CREATE EXTENSION pgfincore;

3 查询共享缓存区

3.1 共享缓冲区的使用数据块数量

1、官方文档的查询例子

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;

1、查询当前连接的数据库，每个对象在数据库中缓存的数据块数量。

SELECT d.datname,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
             LEFT JOIN pg_database d     ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname
             ORDER BY 4 DESC;

3.2 共享缓冲区的使用数据块（TOP10）

1、查询当前连接的数据库，每个对象在数据库中缓存的数据块数量。
2、只显示TOP10 。

SELECT d.datname,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
             LEFT JOIN pg_database d     ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname
             ORDER BY 4 DESC
             LIMIT 10;

3.3 所有数据库的共享缓冲区的使用情况

1、查询所有数据库，每个对象在数据库中缓存的数据块数量。

SELECT d.datname,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=0 OR b.reldatabase IN (SELECT oid FROM pg_database))
             JOIN pg_namespace n ON n.oid = c.relnamespace
             LEFT JOIN pg_database d     ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname
             ORDER BY 4 DESC;

3.4 查看共享缓冲区的脏数据块数量

1、isdirty 为 t 时，表示是脏数据块。
2、isdirty 为 f 时，表示是干净数据块。

SELECT d.datname,n.nspname, c.relname, count(*) AS buffers,isdirty
             FROM pg_buffercache b JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND (b.reldatabase=0 OR b.reldatabase IN (SELECT oid FROM pg_database))
             JOIN pg_namespace n ON n.oid = c.relnamespace
             LEFT JOIN pg_database d ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname,isdirty
             HAVING isdirty ='t'
             ORDER BY 4 DESC;

3.5 计算共享存储区的缓冲百分比

1、家孙当前已缓存的大小，以 KB 显示。
2、计算每个对象占用共享缓冲区的百分比。
3、表对象已经缓冲的在共享缓冲区中的百分比。

SELECT d.datname,n.nspname, c.relname, count(*) AS buffers
  , 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_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
             LEFT JOIN pg_database d ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname,c.oid
             ORDER BY 3 DESC
             LIMIT 10;

4 查看脏数据块信息

4.1 环境准备

1、修改检查点参数。

ALTER SYSTEM SET checkpoint_timeout='30min';
SELECT pg_reload_conf();

2、准备测试表。

DROP TABLE t01;
CREATE TABLE t01 (id int, name varchar(50));

3、插入一行数据。

INSERT INTO t01 VALUEStext);

4.2 查看共享缓冲的脏页面

    
SELECT d.datname,n.nspname, c.relname, count(*) AS buffers,isdirty
             FROM pg_buffercache b JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND (b.reldatabase=0 OR b.reldatabase IN (SELECT oid FROM pg_database))
             JOIN pg_namespace n ON n.oid = c.relnamespace
             LEFT JOIN pg_database d ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname,isdirty
             HAVING isdirty ='t'
             ORDER BY 4 DESC;
 datname  |  nspname   |              relname              | buffers | isdirty 
----------+------------+-----------------------------------+---------+---------
...........
 postgres | public     | emp                               |       1 | t
 postgres | pg_catalog | pg_type                           |       1 | t
...........
 postgres | public     | emp_id_seq                        |       1 | t

1、emp 表和创建的序列 sequence emp_id_seq，被加载到共享内存中。其buffers均为1个页面。
2、insert 时，数据缓存在共享内存中。
3、isdirty 显示为 true 时，表示的都是脏数据页面还未写入磁盘。
4、可以等待系统自动执行检查点，进行数据刷盘。
5、可以手动执行 checkpoint 命令进行数据刷盘。

5 查看共享缓冲区的读取方式

5.1 环境准备

1、再插入一行数据。

INSERT INTO t01 VALUEStext);

5.2 查看共享缓冲的脏页面

    
SELECT d.datname,n.nspname, c.relname, count(*) AS buffers,isdirty
             FROM pg_buffercache b JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND (b.reldatabase=0 OR b.reldatabase IN (SELECT oid FROM pg_database))
             JOIN pg_namespace n ON n.oid = c.relnamespace
             LEFT JOIN pg_database d ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname,isdirty
             HAVING isdirty ='t'
             ORDER BY 4 DESC;
 datname  |  nspname   |              relname              | buffers | isdirty 
----------+------------+-----------------------------------+---------+---------
...........
 postgres | public     | emp                               |       1 | t
 postgres | pg_catalog | pg_type                           |       1 | t
...........
 postgres | public     | emp_id_seq                        |       1 | t

看到的依然是脏数据块。因为，此时这2条记录都在同一个数据块儿上，被加载到共享内存中时，依然在同一个共享内存块儿上，所以依然是脏数据块儿。

6 查看操作系统缓存（OS CACHE）

6.1 环境准备

create table t01 (id int, name varchar(50));
insert into t01 valuestext);

6.2 SELECT 语句详解

1、d.datname : 数据库名称。
2、n.nspname：数据库模式。
3、c.relname：对象名称。
4、count(*) AS buffers： shared_buffer 的数据块数。
5、pg_size_pretty(count(*) * 8192) AS buffered：计算shared_buffers 的已使用大小。
6、round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation：计算 shared_buffers 的已使用的百分比。
7、(SELECT round( sum(pages_mem) * 4 /1024,0 ) FROM pgfincoretext) AS os_cache_MB : 已使用的OS CACHE
8、round(100 * ( select sum(pages_mem)*4096 FROM pgfincoretext) )/ pg_table_size(c.oid),1 AS os_cache_percent_of_relation：对象占用 OS_CACHE 的百分比。
9、pg_size_pretty(pg_table_size(c.oid)) as rel_size ：对象的大小。

SELECT d.datname,n.nspname, c.relname, count(*) AS buffers
  , 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
  , (SELECT round( sum(pages_mem) * 4 /1024,0 ) || 'MB'   FROM pgfincoretext)    AS os_cache_MB 
  , round(100 * (select sum(pages_mem)*4096    FROM pgfincoretext) )/ pg_table_size(c.oid),1   AS os_cache_percent_of_relation
  , pg_size_pretty(pg_table_size(c.oid)) as rel_size 
             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 AND c.relnamespace=(select oid from pg_namespace where nspname='public')
             LEFT JOIN pg_database d ON d.oid = b.reldatabase
             GROUP BY d.datname,n.nspname, c.relname,c.oid
             ORDER BY 3 DESC
             LIMIT 10;
//屏幕输出：
-[ RECORD 1 ]----------------+---------
datname                      | postgres
nspname                      | public
relname                      | t01
buffers                      | 15703
buffered                     | 123 MB
buffers_percent              | 95.8
percent_of_relation          | 81.9
os_cache_mb                  | 150
os_cache_percent_of_relation | 100.0
rel_size                     | 150 MB

6.3 输出说明

字段	说明
datname	数据库名称
nspname	模式名
relname	对象名
buffers	shared_buffer 中缓存的块数。
buffered	shared_buffer 中缓存的大小。
buffers_percent	shared_buffer 中缓存的百分比。
percent_of_relation	对象的数据shared_buffer中所占的百分比。
os_cache_mb	已使用的os_cache 大小。
os_cache_percent_of_relation	对象的数据 os_cache 中所占的百分比。

7 参考文档

1、 PostgreSQL Shared Buffers 全面指南（译） - MSSQL123 - 博客园 (cnblogs.com)
2、关于 PostgreSQL pgfincore 扩展，的更多用法，请见相关文档。