综合指南：postgresql shared buffers

综合指南：postgresql shared buffers

本文主要针对下面问题详述PG的共享内存：PG中需要给共享内存分配多少内存？为什么？

非常奇怪，为什么我的RDS PG需要使用系统RAM的25％，而Aurora的PG却需要分配75％？

理解PG中的共享内存及操作系统的缓存

首先提出个问题：PG中的bgwriter进程是干什么的？

如果回答是将脏页刷到磁盘的，那这就错了。他仅仅将脏页刷写到操作系统的缓存，然后由操作系统调用sync将操作系统缓存刷写到磁盘。有点迷惑？那么接着我们说道说道。

由于PG轻量的特性，他高度依赖操作系统缓存，通过操作系统感知文件系统、磁盘布局以及读写数据文件。下图帮助了解数据如何在磁盘和共享缓存之间流动。

因此当发起“select ＊from emp”时，数据会加载到操作系统缓存然后才到shared buffer。同样当将脏页向磁盘刷写时，也是先到操作系统缓存，然后由操作系统调用fsync（）将操作系统缓存中数据持久化到磁盘。这样PG实际上由两份数据，看起来有些浪费空间，但是操作系统缓存是一个简单的LRU而不是数据库优化的clock sweep algorithm。一旦在shared＿buffers中命中，那么读就不会下沉到操作系统缓存。如果shared buffer和操作系统缓存有相同页，操作系统缓存中的页很快会被驱逐替换。

我能影响操作系统的fsync将脏页刷回磁盘吗？

当然，通过postgresql．conf中参数bgwriter＿flush＿after，该参数整型，默认512KB。当后台写进程写了这么多数据时，会强制OS发起sync将cache中数据刷到底层存储。这样会限制内核页缓存中的脏数据数量，从而减小checkpoint时间或者后台大批量写回数据的时间。

不仅仅时bgwriter，即使checkpoint进程和用户进程也从shared buffer刷写脏页到OS cache。可以通过checkpoint＿flush＿after影响checkpoint进程的fsync，通过backend＿flush＿after影响后台进程的fsync。

如果给OS cache很小值会怎么样？

正如上文所述，一旦页被标记为脏，他就会刷写到操作系统缓存。操作系统可以更加自由地根据传入的流量进行IO调度。如果OS cache太小，则无法重新对write进行排序从而优化IO。这对于写操作频繁的工作负载尤为重要，所以操作系统缓存大学也很重要。

如果给shared buffer很小值会怎么样？

数据库操作都在shared buffer，所以最好为shared buffer分配足够空间。

建议值多大？

PG推荐系统内存的25％给shared buffer，当然可以根据环境进行调整。

如果查看shared buffer中内容？

PG的buffer cache扩展可以帮助实时查看shared buffer中内容。从shared＿buffers中采集信息保存到pg＿buffercache表中：

create extension 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） ＞ 0  postgres－＃  GROUP BY c．oid，  c．relname  postgres－＃  ORDER BY 3 DESC  postgres－＃  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：

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表示PG buffer cache中有多少数据，pgbuffer＿percent表示pg＿buffered／total＿buffer＿size＊100。os＿cache＿mb表示OS cache中缓存多少。我们的表emp有8301MB数据，92％数据在OS cache，49．3％在shared buffers，大约50％的数据是冗余的。

为什么Aurora PG推荐75％的内存给shared buffer？

Aurora不使用文件系统缓存，因此可以提升shared＿buffers大小以提升性能。最佳实践值为75％。Work＿mem、maintenance＿work＿mem和其他本地内存不是shared buffer的一部分。如果应用请求大量客户端连接，或需要大量work＿mem时，需要将这个值调小。