PostgreSQL数据库如何创建和使用pg_buffercache

一 pg_buffercache概述

PostgreSQL数据库提供了一个extension：pg_buffercache，可以用于查看数据库共享内存shared buffer的相关信息。一般不建议在生产环境启用该插件。但是，我们可以用它来研究和学习PostgreSQL数据库的共享内存shared buffer的相关知识。

二安装pg_buffercache报错和解决

0 环境介绍

以下操作在一套CentOS 7.5 X64位系统，数据库版本为PostgreSQL 11.11。

操作系统环境：

[postgres@localhost ~]$ cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 
[postgres@localhost ~]$ uname -rm
3.10.0-862.el7.x86_64 x86_64
[postgres@localhost ~]$ 

数据库环境：

[postgres@localhost ~]$ id
uid=1000(postgres) gid=1000(postgres) 组=1000(postgres) 环境=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[postgres@localhost ~]$ env |grep PG
PGDATA=/postgres/pg11/data
[postgres@localhost ~]$ psql
psql (11.11)
Type "help" for help.
​
postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 11.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
​
postgres=# 

1安装报错

postgres用户连接到template1数据库执行：第一执行创建extension的操作，一定是数据库软件的owner，即postgres用户；第二，这里选择在模板数据库template1库上创建，将来再在这个cluster上创建新的数据库的时候，就会自动创建了该extension。

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)
​
template1=# create extension pg_buffercache;
ERROR:  could not open extension control file "/postgres/pg11/share/extension/pg_buffercache.control": 没有那个文件或目录
template1=# \q

可以看到，错误提示说，在当前环境下数据库的安装目录的share/extension下找不到对应的.control文件，意味着该插件可能没有编译进去。

2 解决错误

以postgres用户，进入到源代码路径下的contrib路径下，找到名为pg_buffercache的路径。至于，为什么要到源代码路径下的contriib路径，可以参考之前的一篇文章：PostgreSQL编译源码安装步骤解释及源码和安装目标路径说明,然后执行make install命令，对该extension执行安装操作，把之前编译好的关于该extension的动态链接库文件copy到PostgreSQL数据库软件安装路径下的lib路径下，即放到/postgres/pg11/lib，把插件相关的文件copy到PostgreSQL数据库软件安装路径下的share/extension路径下，即/postgres/pg11/share/extension/。

[postgres@localhost pg_buffercache]$ pwd
/home/postgres/postgresql-11.11/contrib/pg_buffercache
[postgres@localhost pg_buffercache]$ ll
总用量 60
-rw-r--r--. 1 postgres postgres   619 2月   9 2021 Makefile
-rw-r--r--. 1 postgres postgres   508 2月   9 2021 pg_buffercache--1.0--1.1.sql
-rw-r--r--. 1 postgres postgres   271 2月   9 2021 pg_buffercache--1.1--1.2.sql
-rw-r--r--. 1 postgres postgres   328 2月   9 2021 pg_buffercache--1.2--1.3.sql
-rw-r--r--. 1 postgres postgres   794 2月   9 2021 pg_buffercache--1.2.sql
-rw-r--r--. 1 postgres postgres   157 2月   9 2021 pg_buffercache.control
-rw-r--r--. 1 postgres postgres  7347 2月   9 2021 pg_buffercache_pages.c
-rw-rw-r--. 1 postgres postgres  5632 8月  12 15:27 pg_buffercache_pages.o
-rwxrwxr-x. 1 postgres postgres 13184 8月  12 15:27 pg_buffercache.so
-rw-r--r--. 1 postgres postgres   351 2月   9 2021 pg_buffercache--unpackaged--1.0.sql
[postgres@localhost pg_buffercache]$ make install
make -C ../../src/backend generated-headers
make[1]: 进入目录“/home/postgres/postgresql-11.11/src/backend”
make -C catalog distprep generated-header-symlinks
make[2]: 进入目录“/home/postgres/postgresql-11.11/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/home/postgres/postgresql-11.11/src/backend/catalog”
make -C utils distprep generated-header-symlinks
make[2]: 进入目录“/home/postgres/postgresql-11.11/src/backend/utils”
make[2]: 对“distprep”无需做任何事。
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/home/postgres/postgresql-11.11/src/backend/utils”
make[1]: 离开目录“/home/postgres/postgresql-11.11/src/backend”
/bin/mkdir -p '/postgres/pg11/lib'
/bin/mkdir -p '/postgres/pg11/share/extension'
/bin/mkdir -p '/postgres/pg11/share/extension'
/bin/install -c -m 755  pg_buffercache.so '/postgres/pg11/lib/pg_buffercache.so'
/bin/install -c -m 644 ./pg_buffercache.control '/postgres/pg11/share/extension/'
/bin/install -c -m 644 ./pg_buffercache--1.2.sql ./pg_buffercache--1.2--1.3.sql ./pg_buffercache--1.1--1.2.sql ./pg_buffercache--1.0--1.1.sql ./pg_buffercache--unpackaged--1.0.sql  '/postgres/pg11/share/extension/'
[postgres@localhost pg_buffercache]$ ll /postgres/pg11/share/extension/
总用量 36
-rw-r--r--. 1 postgres postgres 508 10月 21 14:50 pg_buffercache--1.0--1.1.sql
-rw-r--r--. 1 postgres postgres 271 10月 21 14:50 pg_buffercache--1.1--1.2.sql
-rw-r--r--. 1 postgres postgres 328 10月 21 14:50 pg_buffercache--1.2--1.3.sql
-rw-r--r--. 1 postgres postgres 794 10月 21 14:50 pg_buffercache--1.2.sql
-rw-r--r--. 1 postgres postgres 157 10月 21 14:50 pg_buffercache.control
-rw-r--r--. 1 postgres postgres 351 10月 21 14:50 pg_buffercache--unpackaged--1.0.sql
-rw-r--r--. 1 postgres postgres 332 8月  12 15:27 plpgsql--1.0.sql
-rw-r--r--. 1 postgres postgres 179 8月  12 15:27 plpgsql.control
-rw-r--r--. 1 postgres postgres 381 8月  12 15:27 plpgsql--unpackaged--1.0.sql
[postgres@localhost pg_buffercache]$ 

执行安装之后，就可以看到/postgres/pg11/share/extension/路径下，有了关于pg_buffercache相关的文件了。

可以看到相关的信息：

[postgres@localhost pg_buffercache]$ cat /postgres/pg11/share/extension/pg_buffercache--1.2.sql 
/* contrib/pg_buffercache/pg_buffercache--1.2.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 PARALLEL SAFE;
​
-- 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,
         pinning_backends int4);
​
-- 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;
[postgres@localhost pg_buffercache]$ 

创建了1个基于C的函数，pg_buffercache_pages。然后创建了一个名为pg_buffercache的视图，该视图的数据其实就是从函数pg_buffercache_pages中取的数据。最后，从public schema上回收了对于pg_buffercache的使用权限。这一点儿可以从官方文档上对于该extension的说明看到：https://www.postgresql.org/docs/current/pgbuffercache.html

3 创建extension

postgres用户连接到模板数据库template1上执行安装extension的操作：

[postgres@localhost pg_buffercache]$ psql
psql (11.11)
Type "help" for help.
​
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# create extension pg_buffercache;
CREATE EXTENSION
template1=# \dx
                      List of installed extensions
      Name      | Version |   Schema   |           Description           
----------------+---------+------------+---------------------------------
 pg_buffercache | 1.3     | public     | examine the shared buffer cache
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
​
template1=#

4 创建新的目标库，目标库上自动包含该pg_buffercache的extension

[postgres@localhost ~]$ psql
psql (11.11)
Type "help" for help.
​
postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# \dx
                      List of installed extensions
      Name      | Version |   Schema   |           Description           
----------------+---------+------------+---------------------------------
 pg_buffercache | 1.3     | public     | examine the shared buffer cache
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
​
testdb=# 

这里，创建新的testdb之后，就可以看到该库上自动带了该pg_buffercache的extension。因为该testdb是根据template1模板库创建出来的。

三 使用pg_buffercache

1 查看shared_buffers的信息

从PostgreSQL官方文档，看到关于视图pg_buffercache的字段的说明：

Table F.15. pg_buffercache Columns

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
relforknumber	smallint		Fork number within the relation; see include/common/relpath.h
relblocknumber	bigint		Page number within the relation
isdirty	boolean		Is the page dirty?
usagecount	smallint		Clock-sweep access count
pinning_backends	integer		Number of backends pinning this buffer

There is one row for each buffer in the shared cache. Unused buffers are shown with all fields null except bufferid. Shared system catalogs are shown as belonging to database zero.

也就是说当前PostgreSQL cluster的共享内存shared buffers有多少个内存块，该视图就有多少条记录。下来，我们验证一下：

testdb=#  \c
You are now connected to database "testdb" as user "postgres".
testdb=# \d pg_buffercache 
                 View "public.pg_buffercache"
      Column      |   Type   | Collation | Nullable | Default 
------------------+----------+-----------+----------+---------
 bufferid         | integer  |           |          | 
 relfilenode      | oid      |           |          | 
 reltablespace    | oid      |           |          | 
 reldatabase      | oid      |           |          | 
 relforknumber    | smallint |           |          | 
 relblocknumber   | bigint   |           |          | 
 isdirty          | boolean  |           |          | 
 usagecount       | smallint |           |          | 
 pinning_backends | integer  |           |          | 
​
testdb=# show block_size ;
 block_size 
------------
 8192
(1 row)
​
testdb=# show shared_buffers ;
 shared_buffers 
----------------
 4GB
(1 row)
​
testdb=# select count(*) from pg_buffercache ;
 count  
--------
 524288
(1 row)
​
testdb=# select 524288*8192.0/1024/1024/1024 G ;
         g          
--------------------
 4.0000000000000000
(1 row)
​
testdb=# 

当前数据库testdb的数据块大小为8192字节，共享内存shared_buffers=4GB，pg_buffercache视图里一共有524288条记录，即说明共享内存由这么多个内存块儿组成的。最后，可以反推出共享内存的确是4GB。

2 查看脏内存块的信息

通过关联pg_buffercache,pg_class,pg_database，可以看到当前数据库中哪些数据库对象被加载到共享内存中了，SQL如下：

SELECT
   c.relname,
   count(*) as buffers, b.isdirty
   FROM pg_class c JOIN pg_buffercache b
   ON b.relfilenode=c.relfilenode
   JOIN pg_database d
   ON (b.reldatabase=d.oid AND d.datname=current_database())
   WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;

当前testdb中，没有任何操作，执行结果均为空：

testdb=# SELECT
   c.relname,
   count(*) as buffers, b.isdirty
   FROM pg_class c JOIN pg_buffercache b
   ON b.relfilenode=c.relfilenode
   JOIN pg_database d
   ON (b.reldatabase=d.oid AND d.datname=current_database())
   WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;
 relname | buffers | isdirty 
---------+---------+---------
(0 rows)
​
testdb=# 

然后，执行创建表，并且插入测试数据：

testdb=# CREATE TABLE emp(id serial, name varchar(20));
CREATE TABLE
testdb=# insert into emp(name) values('Huang Wei');
INSERT 0 1
testdb=# select * from emp;
 id |   name    
----+-----------
  1 | Huang Wei
(1 row)
​
testdb=# SELECT
testdb-#    c.relname,
testdb-#    count(*) as buffers, b.isdirty
testdb-#    FROM pg_class c JOIN pg_buffercache b
testdb-#    ON b.relfilenode=c.relfilenode
testdb-#    JOIN pg_database d
testdb-#    ON (b.reldatabase=d.oid AND d.datname=current_database())
testdb-#    WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;
  relname   | buffers | isdirty 
------------+---------+---------
 emp        |       1 | t
 emp_id_seq |       1 | t
(2 rows)
​
testdb=# 

共享内存中，关于表emp和默认创建的sequence emp_id_seq，都被加载到共享内存中，其buffers均为1。同时，isdirty显示为true，表示的都是脏数据块儿，因为是insert的结果，暂时还在内存中，并没有同步到数据文件中。我们可以等待系统自动执行检查点，把内存中的脏数据刷出到数据库文件中，也可以手动强制通过执行checkpoint命令来把脏数据刷出去。

testdb=# checkpoint ;
CHECKPOINT
testdb=# SELECT      
   c.relname,
   count(*) as buffers, b.isdirty
   FROM pg_class c JOIN pg_buffercache b
   ON b.relfilenode=c.relfilenode
   JOIN pg_database d
   ON (b.reldatabase=d.oid AND d.datname=current_database())
   WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;
  relname   | buffers | isdirty 
------------+---------+---------
 emp        |       1 | f
 emp_id_seq |       1 | f
(2 rows)
​
testdb=# 

再插入1条记录：

testdb=# insert into emp(name) values('PostgreSQL');
INSERT 0 1
testdb=# SELECT                                     
   c.relname,
   count(*) as buffers, b.isdirty
   FROM pg_class c JOIN pg_buffercache b
   ON b.relfilenode=c.relfilenode
   JOIN pg_database d
   ON (b.reldatabase=d.oid AND d.datname=current_database())
   WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;
  relname   | buffers | isdirty 
------------+---------+---------
 emp        |       1 | t
 emp_id_seq |       1 | t
(2 rows)
​
testdb=# select * from emp;
 id |    name    
----+------------
  1 | Huang Wei
  2 | PostgreSQL
(2 rows)
​
testdb=# SELECT            
   c.relname,
   count(*) as buffers, b.isdirty
   FROM pg_class c JOIN pg_buffercache b
   ON b.relfilenode=c.relfilenode
   JOIN pg_database d
   ON (b.reldatabase=d.oid AND d.datname=current_database())
   WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;
  relname   | buffers | isdirty 
------------+---------+---------
 emp        |       1 | t
 emp_id_seq |       1 | t
(2 rows)
​
testdb=# select * from emp where id=1;
 id |   name    
----+-----------
  1 | Huang Wei
(1 row)
​
testdb=# SELECT                       
   c.relname,
   count(*) as buffers, b.isdirty
   FROM pg_class c JOIN pg_buffercache b
   ON b.relfilenode=c.relfilenode
   JOIN pg_database d
   ON (b.reldatabase=d.oid AND d.datname=current_database())
   WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;
  relname   | buffers | isdirty 
------------+---------+---------
 emp        |       1 | t
 emp_id_seq |       1 | t
(2 rows)
​
testdb=# 

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

这里，我们要有一个明确的概念，数据库执行IO操作时，不是以表中的记录为单位进行读写的，而是以数据块儿为单位进行IO操作的。

四参考：

https://paquier.xyz/postgresql-2/postgres-feature-highlight-pg_buffercache/

https://www.postgresql.org/docs/14/pgbuffercache.html