综合指南:postgresql shared buffers

2021-01-05 14:35:49 浏览数 (1)

综合指南: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;

输出:

代码语言:javascript复制
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:

代码语言:javascript复制
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;

执行下面命令:

代码语言:javascript复制
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时,需要将这个值调小。

原文

https://postgreshelp.com/postgresql_shared_buffers/

0 人点赞