【PostgreSQL的shared_buffers和系统OS cache的关系】

一、与ORACLE和MySQL的区别

在使用PostgreSQL要设置shared_buffers的大小的时候，我们通常的建议值是操作系统内存的25%，过大或者过小的值都会影响数据库的性能。此时我们有时候会产生一个疑问，为什么类似的参数，ORACLE和MYSQL都分配了操作系统大部分内存，例如ORACLE的sga通常设置为物理内存的80%，或者MySQL的innodb_buffer_pool_size 通常也设置为80%，而PostgreSQL的shared_buffers确设置的比例这么小呢。

其实是因为Postgresql采用数据库实例buffer(shared_buffers)和操作系统buffer双缓存(effective_cache_size)的工作模式，PostgreSQL数据库必须高度依赖操作系统缓存，它依赖于操作系统来了解文件系统、磁盘布局以及如何读写数据文件。缓存作为数据库的一个核心组件，shared_buffers决定了数据库实例层面的可用内存，而系统中预计有多少缓存是effective_cache_size决定的。而且effective_cache_size不仅是缓存经常访问的数据，它同时帮助优化器确定实际存在多少缓存，指导优化器生成最佳执行计划。

而ORACLE和MYSQL把大部分系统内存给到了数据库缓存，倾向于不使用OS cache，支持使用 direct IO——在应用层Buffer和磁盘之间直接建立通道，绕过操作系统缓存。这样在读写数据的时候就能够减少上下文切换次数，同时也能够减少数据拷贝次数，从而提高效率。而原生PostgreSQL是不支持direct IO的，这一点和ORACLE、MySQL还是有着比较本质上的差异的。（事情也不绝对,例如Aurora PostgreSQL消除了双缓存，并且不使用文件系统缓存）

二、PostgreSQL读写数据的过程

当我们日常在数据库里写入数据后，bgwriter进程将脏缓冲区刷新到磁盘时，页面实际上是先刷新到OS缓存，然后再刷新到磁盘。

而执行查询，会先从shared_buffers里查找，一旦在shared_buffers里命中了数据页，就永远不会再到操作系统缓存里进行查找。但如果在shared_buffers里没命中，则会继续从OS cache里找寻，如果在OS cache里命中了，则把数据加载到shared_buffers里去。
如果在shared_buffers和OS cache里都没有命中的话，则会把数据先加载到操作系统缓存（OS cache ），然后再加载到shared buffers。

这种双缓存的工作模式意味着OS cache和shared_buffers可以保存相同的页面。有一定可能可能会导致空间浪费，但OS缓存使用的是LRU算法，而不是shared_buffers的时钟扫描算法（clock sweep algorithm.）。一旦在shared_buffers里命中了数据页，就永远不会到操作系统缓存里进行查找，因此，在shared_buffers里长期使用到的部分，在OS cache里实际上会很容易就被清理掉了。

三、当shared_buffers设置过大或过小

1.shared_buffers过小、OS cache较大

当我们给shared_buffers过小而OS cache较大的时候，虽然数据会集中在OS cache里，但实际的数据库的操作都是在共享缓冲区里执行的，所以做一些复杂查询的时候，性能是很差的。

除此之外，shared_buffers采用的时钟扫描算法（clock sweep algorithm.）算法为每个最近被使用的页面增加了权重，使用越频繁越不容易被替换出去，比OS cache的LRU算法更加符合真实的场景，shared_buffers里其实比OS cache更加容易缓存到常用的数据。

2.shared_buffers过大、OS cache较小

而当我们给OS cache很小，但是shared_buffers很大的时候，shared_buffers里一旦页被标记成了脏页，就会被刷新到OS cache里，如果OS cache过小的话，它就不能重新排序写操作以及优化IO，可能导致大量的离散写，对于有大量繁重写入操作的数据库而言，这一点十分的不友好。

此外PostgreSQL数据目录里pg_clog目录下存储了提交日志信息，是定期读取和写入的，因此OS cache的大小还和clog的读写任务性能息息相关，通过OS cache会更直接。并且，shared_buffers管理内存也需要代价，检查点、脏页判断的代价也会随着shared_buffers的增大而增大。

四、如何查看shared_buffers或OS cache里缓存数据量

可以使用pg_buffercache和pgfincore这两个插件去查看缓存里的数据量。
pgfincore工具github的地址如下https://github.com/klando/pgfincore

git clone git://git.postgresql.org/git/pgfincore.git
make 
make install
 
[xmaster@mogdb-kernel-0005 pgfincore]$ psql
psql (14.1)
Type "help" for help.
 
postgres=# create extension pg_buffercache;
CREATE EXTENSION
postgres=# CREATE EXTENSION pgfincore;
CREATE EXTENSION
 

使用如下语句查看缓存数据量及比例。

postgres=# select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered, 
postgres-#  round(100.0 * count(*) / 
postgres(#            (select setting 
postgres(#             from pg_settings 
postgres(#             where name='shared_buffers')::integer,1)
postgres-#        as pgbuffer_percent,
postgres-#        round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
postgres-#        ( select round( sum(pages_mem) * 4 /1024,0 )
postgres(#          from pgfincore(c.relname::text) ) 
postgres-#          as os_cache_MB , 
postgres-#          round(100 * ( 
postgres(#                select sum(pages_mem)*4096 
postgres(#                from pgfincore(c.relname::text) )/ pg_table_size(c.oid),1) 
postgres-#          as os_cache_percent_of_relation,
postgres-#          pg_size_pretty(pg_table_size(c.oid)) as rel_size 
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(#             and c.relnamespace=(select oid from pg_namespace where nspname='public')) 
postgres-#  group by c.oid,c.relname 
postgres-#  order by 3 desc limit 30;
  relname  | pg_buffered | pgbuffer_percent | percent_of_relation | os_cache_mb | os_cache_percent_of_relation | rel_size 
-----------+-------------+------------------+---------------------+-------------+------------------------------+----------
 demotable | 43 MB       |             33.5 |               100.0 |          43 |                         99.9 | 43 MB
 demoidx   | 64 kB       |              0.0 |                 0.2 |          30 |                        100.0 | 30 MB
(2 rows)

以demotable表为例

pg_buffered表示在PostgreSQL的shared_buffers中缓存了该表多少数据，这里是43MB
pgbuffer_percent 表示是该表占用的shared_buffers的比例，也就是33.5%
percent_of_relation表示表在share_buffers的命中率，这里是100%，
os_cache_mb 表示OS cache中缓存了该表多少数据，这里是43MB
os_cache_percent_of_relation 表示表在OS cache中命中率，这里是99.9%
rel_size 表示这个表的真实大小,这里是43MB