综合指南：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时，需要将这个值调小。
