欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

PostgreSQL 中的shared buffer

程序员文章站 2022-11-20 23:34:45
前言通常，在任何数据库系统中，都需要面临海量用户对数数据库进行读写操作。而对数据库中的数据最直接的读写操作是读取数据库中对应的数据目录中的文件。这些文件里面包含了数据库的任何对象，如表、视图、索引、序列、函数等。PostgreSQL 也不例外，在对数据库初始化时，我们能够看到由PostgreSQL 服务端工具 initdb 创建的相关表和目录，但在一个不具有弹性伸缩的数据库系统中，海量用户对数据库的读写操作并不是很理想。因为对数据库的数据对象进行读写操作需要数据库系统查找相关文件，打开文件，然后通过相关的...

前言

通常，在任何数据库系统中，都需要面临海量用户对数数据库进行读写操作。而对数据库中的数据最直接的读写操作是读取数据库中对应的数据目录中的文件。这些文件里面包含了数据库的任何对象，如表、视图、索引、序列、函数等。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级别数据迁移程序。
PostgreSQL 中的shared buffer

本文地址：https://blog.csdn.net/weixin_45694422/article/details/108980012

相关标签： PostgreSQL数据库 数据库 sql

上一篇： net start mysql 遇到 MySql 服务正在启动 MySql 服务无法启动 服务没有报告任何错误 解决方式

下一篇： 驾崩是什么意思？为什么皇帝去世后都要喊

推荐阅读