关于sql:PostgreSQL-中的shared-buffer

前言

通常，在任何数据库系统中，都须要面临海量用户对数数据库进行读写操作。而对数据库中的数据最间接的读写操作是读取数据库中对应的数据目录中的文件。这些文件外面蕴含了数据库的任何对象，如表、视图、索引、序列、函数等。PostgreSQL 也不例外，在对数据库初始化时，咱们可能看到由 PostgreSQL 服务端工具 initdb 创立的相干表和目录，但在一个不具备弹性伸缩的数据库系统中，海量用户对数据库的读写操作并不是很现实。因为对数据库的数据对象进行读写操作须要数据库系统查找相干文件，关上文件，而后通过相干的函数对这些对象进行锁定，编辑和开释锁。因而在数据库系统中为了可能进步并发解决并放慢数据处理过程，shared buffer 的概念也利用而生。有了 shared buffer，后盾过程从用户接管到申请后将不会再间接去读取数据库对象文件和写入数据库对象文件，而是通过在 RAM 中的 buffer 中解决数据，从而极大地提高了数据库性能。

一、shared buffer

shared buffer 内存大小通过参数 shared_buffers 参数来进行设置。改参数之在 PostgreSQL 中默认值为 128MB，初始化大小能够通过 initdb 来进行设置，然而不少于 128KB。如果在对该参数不进行设置，那么该值的大小以块字节为单位，即设置为整数的字节大小。同时该参数变动后，须要重新启动数据库服务器。在数据库服务器上，如果 RAM 大小超过 1GB，该值的大小通常为零碎 RAM 的 25%。在进行读写频繁的数据库环境中，该参数能够调整到大于 25% 的值，但不倡议将该值设置太高，如果设置太高，须要同时减少 max_wal_size 参数的大小，否则会造成零碎性能降落。

该原理并不是通过内存 chunk 克隆的形式来进步零碎响应工夫，而是 OS 中的 RAM 将大量数据驻留在其中，以保证数据随时可用来进步零碎响应工夫。当然，除了缓存之外，还有磁盘缓存也能够进步数据处理性能，但原理都是通过缩小不必要的物理 I/O 来进步性能。

在一个惯例的简略查问中，零碎第一步将会查看在 buffer cache 中是否有数据可用，如果数据库 buffer cache 中无可用缓存数据，用户申请将会从 OS 缓存中获取须要的文件或者块。操作系统缓存很可能曾经领有须要查找的数据块或者文件并将其驻留在数据库缓冲中，在这种状况下，数据库系统将会避免出现物理 I / O 申请，这在数据库中也叫做逻辑读，会耗费肯定的 CPU 资源。如果须要查找的数据都不在这两个缓存中或者其它缓存中，那么用户读取数据都须要从磁盘去读取，而第一次数据读取必然要通过读取物理文件来查找。

二、buffer cache 查看

PostgreSQL 提供了一个扩大能够用来查看 buffer cache。以下将介绍 buffer cache 扩大模块。

2.1 创立数据库

postgres=# CREATE DATABASE mydb1;
CREATE DATABASE
postgres=# CREATE DATABASE mydb2;
CREATE DATABASE

2.2 buffer cache 扩大模版

pg_buffercache 模块位于 $PGDATA/../share/postgresql/extension 目录。
提供了一个 buffer cache 函数和一个视图。如下：

CREATE FUNCTION pg_buffercache_pages() RETURNS
SETOF RECORD AS 'MODULE_PATHNAME',
      'pg_buffercache_pages' LANGUAGE C PARALLEL SAFE;


CREATE OR REPLACE 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
);

创立 pg_buffercache 扩大

postgres=# \c mydb1 postgres
You are now connected to database "mydb1" as user "postgres".
mydb1=# CREATE EXTENSION pg_buffercache ;
CREATE EXTENSION

连贯到数据库 mydb1

mydb1=# \c mydb1 postgres
You are now connected to database "mydb1" as user "postgres".
mydb1=# \d+ pg_buffercache 
                             View "public.pg_buffercache"
      Column      |   Type   | Collation | Nullable | Default | Storage | Descri
ption 
------------------+----------+-----------+----------+---------+---------+-------
 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   | 
View definition:
 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 bool
ean, usagecount smallint, pinning_backends integer);

查看视图验证后果

SELECT distinct a.reldatabase,
b.datname 数据库名称
FROM  pg_buffercache b
RIGHT JOIN pg_database a
ON a.oid = b.reldatabase;

mydb1=# SELECT DISTINCT reldatabase
FROM pg_buffercache ;
 reldatabase 
-------------
            
       14187
       24576
       24577
           0

下面 reldatabase 列中呈现为 0 的记录的数据库，示意没有应用 buffer。

mydb1=# \! oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  24576          mydb1  pg_default
  24577          mydb2  pg_default
  14187       postgres  pg_default
  14186      template0  pg_default
      1      template1  pg_default

通过 oid2name 客户端命令能够查看到数据库相干的 oid 信息和名称及应用的表空间。

创立视图

CREATE OR REPLACE VIEW v_buf_info AS
SELECT c.datname 数据库名称,
       d.spcname 表空间名称,
       a.relname 对象名称,
       count(*) AS 缓冲数量
FROM pg_class a
JOIN pg_buffercache b ON a.relfilenode = b.relfilenode
JOIN pg_database c ON (b.reldatabase = c.oid
                       AND c.datname = current_database())
JOIN pg_tablespace d ON d.oid = b.reltablespace
GROUP BY a.relname,c.datname,d.spcname
ORDER BY 2 DESC;

mydb1=# SELECT * FROM v_buf_info ;
 数据库名称 | 表空间名称 |                   对象名称                    | 缓冲数量 
------------+------------+-----------------------------------------------+----------
 mydb1      | pg_default | pg_aggregate                                  |        2
 mydb1      | pg_default | pg_aggregate_fnoid_index                      |        2
 mydb1      | pg_default | pg_am

通过该视图能够查看到数据库 mydb1 中以后的缓存应用信息。
下面视图中看到的都是零碎表在 buffer 中的缓存，如果须要过滤零碎表应用的缓存信息，在视图中退出 where 条件中即可。

CREATE OR REPLACE VIEW v_buf_info AS
SELECT c.datname 数据库名称,
       d.spcname 表空间名称,
       a.relname 对象名称,
       count(*) AS 缓冲数量
FROM pg_class a
JOIN pg_buffercache b ON a.relfilenode = b.relfilenode
JOIN pg_database c ON (b.reldatabase = c.oid
                       AND c.datname = current_database())
JOIN pg_tablespace d ON d.oid = b.reltablespace
WHERE a.relname !~ '^pg'
GROUP BY a.relname,c.datname,d.spcname
ORDER BY 2 DESC;

测试用户数据在 buffer 中的信息

mydb1=# -- 插入数据
mydb1=# INSERT INTO tab_buffer VALUES(1,'PostgreSQL');
INSERT 0 1
mydb1=# INSERT INTO tab_buffer VALUES(2,'MySQL');
INSERT 0 1
mydb1=# -- 查看数据
mydb1=# SELECT * FROM tab_buffer;
 id |    name    
----+------------
  1 | PostgreSQL
  2 | MySQL

在视图中减少更多的列，来查问缓存信息

-- 查问视图
DROP VIEW IF EXISTS v_buf_info;
CREATE OR REPLACE VIEW v_buf_info AS
SELECT c.datname 数据库名称,
       d.spcname 表空间名称,
       a.relname 对象名称,
       case
        b.isdirty WHEN 't' THEN '含有脏块' ELSE '没有脏块' END AS 是否蕴含脏块,
       count(*) AS 缓冲数量
FROM pg_class a
JOIN pg_buffercache b ON a.relfilenode = b.relfilenode
JOIN pg_database c ON (b.reldatabase = c.oid
                       AND c.datname = current_database())
JOIN pg_tablespace d ON d.oid = b.reltablespace
WHERE a.relname !~ '^pg'
GROUP BY a.relname,c.datname,d.spcname,b.isdirty
ORDER BY 2 DESC;


mydb1=# SELECT * FROM v_buf_info ;
 数据库名称 | 表空间名称 |  对象名称  | 是否蕴含脏块 | 缓冲数量 
------------+------------+------------+--------------+----------
 mydb1      | pg_default | tab_buffer | 含有脏块     |        1
(1 row)

mydb1=# checkpoint ;
CHECKPOINT
mydb1=# SELECT * FROM v_buf_info ;
 数据库名称 | 表空间名称 |  对象名称  | 是否蕴含脏块 | 缓冲数量 
------------+------------+------------+--------------+----------
 mydb1      | pg_default | tab_buffer | 没有脏块     |        1
(1 row)

mydb1=# INSERT INTO tab_buffer VALUES(4,'Sungsasong');
INSERT 0 1
mydb1=# SELECT * FROM v_buf_info ;
 数据库名称 | 表空间名称 |  对象名称  | 是否蕴含脏块 | 缓冲数量 
------------+------------+------------+--------------+----------
 mydb1      | pg_default | tab_buffer | 含有脏块     |        1

通过测试发现，如果在数据库中做了 checkpoint，那么 buffer 中的数据将会被刷入磁盘。

作者：宋少华

PostgreSQL 分会培训认证委员会委员、晟数科技首席技术专家、晟数学院金牌讲师、oracle 11g OCM、PostgreSQL 首批 PGCE。

曾服务于国家电网冀北电力有限公司建设大数据平台，为人社局和北京市卫计委构建 IT 根底服务，为多家银行和证券公司构建 web 服务器，零碎及数据库保护；具备对税务局、国家电网、银行等政府行业和民营企业的 IT 培训教训；为相干平安行业设计 DW 数据仓库模型，应用 PostgreSQL,Greenplum,HUAWEIGaussDB,Vertica 和 Clickhouse 做数据根底服务，开发 TB 级数据落地程序及百 TB 级别数据迁徙程序。