A Comprehensive Guide: PostgreSQL Shared Buffers（译）

译者注

与MySQL设置innodb_buffer_pool_size = 80%左右的系统内存相比，也就是将操作系统大部分内存分配给Innodb的buffer pool的缓存管理机制不同，Postgresql采用数据库采用实例buffer和操作系统buffer双缓存(effective_cache_size)的工作模式，这一点两者还是有着比较本质上的差异的。

缓存作为数据库的一个核心组件，shared_buffers决定了数据库实例层面的可用内存，而文件系统缓存的大小是effective_cache_size决定的，effective_cache_size不仅是缓存经常访问的数据，它同时帮助优化器确定实际存在多少缓存，指导优化器生成最佳执行计划。 以下几篇文章都比较好地解释了Shared Buffers和操作系统层面文件缓存（os cache）之间的关系，可作为参考

https://www.cybertec-postgresql.com/en/effective_cache_size-what-it-means-in-postgresql/

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

https://severalnines.com/database-blog/architecture-and-tuning-memory-postgresql-databases

https://distributedsystemsauthority.com/optimizing-postgresql-shared-buffers/

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/10/EDB_Postgres_Advanced_Server_Guide.1.24.html

https://stackoverflow.com/questions/42478488/resize-shared-buffer-size-in-postgresql-hosted-in-aws-rds

https://devcenter.heroku.com/articles/understanding-postgres-data-caching

查阅到的资料上只是说了一些理论基础，比如shared buffers或者和effective_cache_size设置的过大或者过小理论上存在的问题，但是最终还是没整明白，Postgresql为什么shared buffers（建议值是25%系统内存）和effective_cache_size（建议值是50%系统内存）分配机制的背后原理，也希望有高人指点。

以下为译文。

原文地址：

https://postgreshelp.com/postgresql_shared_buffers/

这篇文章详细回答了以下问题：在PostgreSQL中，你需要给共享缓冲区多少内存?为什么?

额外的福利! !为什么我的RDS postgreSQL共享缓冲区使用系统内存的25%，而Aurora Postgresql的共享缓冲区是75%?答案就在这里。

理解PostgreSQL中的OS Cache vs Shared Buffers

在我们开始之前，首先回答一个问题：BGWriter在PostgreSQL中的作用是什么? 如果你的答案是“它把脏缓冲区写到磁盘”，那就错了。

它实际上将脏缓冲区写入OS缓存，然后进行单独的系统调用，将页面从OS缓存刷新到磁盘。

是不是不太明白吗?可以这么去理解它： 由于它轻量级的特性，PostgreSQL必须高度依赖操作系统缓存，它依赖于操作系统来了解文件系统、磁盘布局以及如何读写数据文件。

下图让您大致了解了数据如何在磁盘和share buffers之间传递。

在这里，每当您发出一个“select * from emp”，您的数据实际上是加载到操作系统缓存（OS cache ），然后到shared buffers。

同样，当您尝试将脏缓冲区刷新到磁盘时，页面实际上是刷新到OS缓存，然后通过一个名为fsync()的单独系统调用刷新到磁盘。

这里，PostgreSQL实际上复制了OS的功能，这意味着OS cache和shared_buffers可以保存相同的页面。

这可能会导致空间浪费，但请记住，OS缓存使用的是简单的LRU，而不是数据库优化的时钟扫描算法（clock sweep algorithm.）。一旦页面在shared_buffers上命中，读取就永远不会到达操作系统缓存，如果有任何副本，则很容易删除它们。

是否可以设置操作系统的fsync()方法刷新脏页到磁盘？

是的，参数在postgresql.conf文件中，bgwriter_flush_after(整数)—默认512 kB 当backend writer写入的数据超过这个数量时，尝试强制操作系统向底层存储发出这些写入操作。

这样做将限制内核页面缓存中的脏数据量，减少在检查点结束时发出fsync或操作系统在后台大量回写数据时暂停的可能性。 它被用作块，即BLCKSZ字节，通常为8kB。

不仅是bgwriter，在PostgreSQL甚至checkpoint进程和用户后端进程也可以将脏缓冲区从共享缓冲区到操作系统缓存。

即使在这里，我们也可以分别使用checkpoint_flush_after和backend_flush_after命令来影响操作系统的fsync()(尽管关于检查指针和后端进程的讨论超出了本文的范围)。

另外请参考:

checkpoint过程综合指南 https://postgreshelp.com/postgresql-checkpoint/

如果给操作系统缓存（OS Cache）分配的值太少了呢?

如上所述，一旦页面被标记为dirty，它就会被刷新到OS缓存中，然后写入磁盘。

在这里，OS可以更自由地根据传入的流量进行I/O。如果操作系统缓存的大小更小，那么它就不能重新排序写操作和优化I/O。这对于编写繁重的工作负载尤其重要。所以操作系统缓存大小也很重要。

译者注：

这里提到的，如果给操作系统分片的缓存值太小，意味着将大部分内存分配给shared buffers，除了上面提到的原因，笔者查阅了很多资料，关于Postgresql实例的OS cache和shared buffers分配比例以及原因都没有一个明确的解释。

比如建议的给shared buffers分配25%的总内存，给effective_cache_size设置50%总内存的具体原理，为什么是这个比例，而不是5:5开或者是7:3开的比例？ 在这里有类似问题的解释：

https://www.postgresql-archive.org/Increased-shared-buffer-setting-lower-hit-ratio-td5826899.html

Regarding the unexpected decrease of performance after increasing shared_buffers - that's actually quite common behavior. First, themanagement of shared buffers is not free, and the more pieces you need to manage the more expensive it is. Also, by using larger shared buffers you make that memory unusable for page cache etc. There are also other negative consequences - double buffering, accumulating more changes for a checkpoint etc.

如果给共享缓冲区缓存（shared buffers）的值太少怎么办?

很简单，虽然使用OS缓存进行缓存，但实际的数据库操作是在共享缓冲区中执行的。因此，在共享缓冲区中提供足够的空间是个好主意。

译者注：以下翻译自

https://distributedsystemsauthority.com/optimizing-postgresql-shared-buffers/

PostgreSQL shared_buffers versus operating system cache 如果你习惯了这样的数据库，大部分的系统内存都是给数据库的，而操作系统缓存是通过同步和直接写绕过的，你就不会想用同样的方法来设置PostgreSQL。

但是在Postgresql中这样做适得其反，例如，PostgreSQL的存储在pg_clog目录下提交日志信息。该数据是定期读取和写入的，因此操作系统将负责优化clog的任务。

那么为什么不把所有的内存都给操作系统呢? PostgreSQL共享缓冲区缓存能比操作系统缓存做得更好的主要原因是它保持缓冲区使用计数的方式。

这允许缓冲区获得从0到5的“流行度”分数，分数越高，这些缓冲区离开缓存的可能性就越小。

每当数据库查找要删除的内容以便为其需要的数据腾出更多空间时，就会减少使用计数。使用量的每一次增加都会使该块更难摆脱。这个实现称为时钟扫描算法（clock-sweep algorithm）。典型的操作系统缓存在数据被驱逐之前只会给任何缓冲区一到两次机会。

通常，操作系统会使用某种形式的LRU算法。如果数据库中有经常使用的数据，那么将数据保存在数据库的共享RAM中可能比保存在操作系统的共享RAM中更好。

那么什么是最优值呢?

PostgreSQL建议你将25%的系统内存分配给共享缓冲区，并且你可以根据你的环境随时修改共享缓冲区的值。

如何查看共享缓冲区的内容?

PG缓冲缓存扩展帮助我们实时查看共享缓冲区中的数据。从shared_buffers收集信息并将其放在pg_buffercache中以供查看。

create extents pg_buffercache;

安装扩展之后，执行下面的查询来检查共享缓冲区的内容。

SELECT c.relname
 , 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_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) WHERE pg_relation_size(c.oid) > 0
GROUP BY c.oid, c.relname ORDER BY 3 DESC
LIMIT 10;

输出的结果类似如下：

postgres=# SELECT c.relname
postgres-#   , pg_size_pretty(count(*) * 8192) as buffered
postgres-#   , round(100.0 * count(*) / ( SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent
postgres-#   , round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation
postgres-#  FROM pg_class c
postgres-#  INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
postgres-#  INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
postgres-#  WHERE pg_relation_size(c.oid) > 0postgres-#  GROUP BY c.oid, c.relname
postgres-#  ORDER BY 3 DESCpostgres-#  LIMIT 10;
         relname          |  buffered  | buffers_percent | percent_of_relation---------------------------+------------+-----------------+---------------------
pg_operator               | 80 kB      |             0.1 |                71.4
pg_depend_reference_index | 96 kB      |             0.1 |                27.9
pg_am                     | 8192 bytes |             0.0 |               100.0
pg_amproc                 | 24 kB      |             0.0 |               100.0
pg_cast                   | 8192 bytes |             0.0 |                50.0
pg_depend                 | 64 kB      |             0.0 |                14.0
pg_index                  | 32 kB      |             0.0 |               100.0
pg_description            | 40 kB      |             0.0 |                14.3
pg_language               | 8192 bytes |             0.0 |               100.0
pg_amop                   | 40 kB      |             0.0 |                83.3(10 rows)

如何能看到数据实际上是在操作系统级别缓存的?

要检查在操作系统级别缓存的数据，我们需要安装pgfincore包。 这是一个外部模块，提供关于操作系统如何缓存页面的信息。它的级别很低，但却非常强大。

下载pgfincore并执行以下步骤。

As root user:

export PATH=/usr/local/pgsql/bin:$PATH //Set the path to point pg_config.tar -xvf pgfincore-v1.1.1.tar.gz 
cd pgfincore-1.1.1 make clean 
make make install 
Now connect to PG and run below command
postgres=# CREATE EXTENSION pgfincore;

现在执行下面的命令来检查操作系统级别的缓冲区。

select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered, 
round(100.0 * count(*) / 
          (select setting 
           from pg_settings 
           where name='shared_buffers')::integer,1)       as pgbuffer_percent,       round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
      ( select round( sum(pages_mem) * 4 /1024,0 )         from pgfincore(c.relname::text) ) 
        as os_cache_MB , 
        round(100 * ( 
              select sum(pages_mem)*4096 
              from pgfincore(c.relname::text) )/ 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_class c 
inner join pg_buffercache b on b.relfilenode=c.relfilenode 
inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()            and c.relnamespace=(select oid from pg_namespace where nspname='public')) 
group by c.oid,c.relname 
order by 3 desc limit 30;

输出的结果类似如下：

relname |pg_buffered|pgbuffer_per|per_of_relation|os_cache_mb|os_cache_per_of_relation|rel_size 
---------+-----------+------------+---------------+-----------+------------------------+-------- 
emp     | 4091 MB   | 99.9       | 49.3          | 7643      | 92.1                   | 8301 MB

pg_buffered表示PostgreSQL缓冲缓存中缓冲了多少数据 pgbuffer_percent表示pg_buffered/total_buffer_size *100 percent_of_relation表示pg_buffered/total_relation_size * 100 os_cache_mb表示在OS中缓存了多少关系 在这里，我们的emp表有8301 MB大小，92%的数据在操作系统缓存中，同时49.3%的数据在共享缓冲区中，也就是大约50%的数据是冗余的。

额外的收获! !

为什么Aurora的PostgreSQL将shared buffers设置为可用内存的75%?

对于RDS DB实例，DB参数组的默认值设置为总内存的25%。但是对于Aurora DB实例，DB参数组的默认值设置为总内存的75%。

这是因为Aurora PostgreSQL消除了双缓冲，并且不使用文件系统缓存。 因此，Aurora PostgreSQL可以通过增加shared_buffers来提高性能。在使用Aurora PostgreSQL时，shared_buffers DB参数的默认值为75%是一个最佳实践。

众所周知，work_mem、maintenance_work_mem和其他本地内存组件都不是shared buffers的一部分，在aurora中，如果你的应用程序需要大量的work_mem或者你的应用程序需要更多的客户端连接，你会遇到set shared_buffers值小于75%的情况。