pg进程和内存体系架构

backend 客户发起的会话进程
后台进程
进程                               描述
background writer          在这个过程中，共享缓冲池中的脏页会被逐步地定期写入持久存储(例如，HDD、SSD)。
checkpointer                  负责执行checkpoint。
autovacuum launcher    负责执行autovacuum。
WAL writer                     该进程定期将WAL缓冲区中的WAL数据写入并刷新到持久存储中。
statistics collector          收集诸如pg_stat_activity和pg_stat_database等统计信息。
logging collector            错误消息写入日志文件。
archiver                          将执行归档日志记录。

本地内存区
work_mem                           排序，并使用merge-join和hash-join操作来连接表。
maintenance_work_mem    某些维护操作(如VACUUM, REINDEX)使用这个区域。
temp_buffers                       存储临时表。
共享内存区
shared buffer pool PostgreSQL  将表和索引中的页面从持久存储加载到这里，并直接操作它们。
WAL buffer   为了保证数据不因服务器故障而丢失，PostgreSQL支持WAL机制。WAL data(也称XLOG records)是PostgreSQL中的事务日志;WAL buffer是WAL数据写入持久存储之前的缓冲区域。
commit log 提交日志(CLOG)保存所有事务(如in_progress,committed,aborted)的状态，用于并发控制(CC)机制。

查看及调整内存

SELECT NAME
	,
	setting,
	unit,
	to_char( to_number( setting, '999999' ) * 8 / 1024, '99999999' ) total, to_char( to_number( setting, '999999' ) * 8 / 1024/4096, '99999999.99' ) os
FROM
	pg_settings 
WHERE
	NAME IN (  'temp_buffers', 'shared_buffers', 'wal_buffers', 'effective_cache_size' ) UNION 
SELECT NAME
	,
	setting,
	unit,
	to_char( to_number( setting, '999999' ) * 1 / 1024, '99999999' ) total , to_char( to_number( setting, '999999' ) * 8 / 1024/4096, '99999999.99' ) os
FROM
	pg_settings 
WHERE
	NAME IN ( 'work_mem', 'maintenance_work_mem','max_connections' );

　　

shared_buffers设置的合理范围
1.windows服务器有用范围是64MB到512MB，默认128MB
2.linux服务器建议设置为25%,文档参数的设置范围一般在25%~40%之间

调整shared_buffers

select round((4096*0.25*1024)/8);
alter system set shared_buffers=131072

 

--缓存命中数
SELECT
   c.relname,
   count(*) as buffers, b.isdirty
   FROM pg_class c JOIN sys_buffercache b
   ON b.relfilenode=c.relfilenode
   JOIN pg_database d
   ON (b.reldatabase=d.oid AND d.datname=current_database())
   WHERE c.relname not like 'pg%' group by c.relname,b.isdirty;
--缓存命中百分比 
 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 sys_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;

1.如果大量的usagecount都是4或者5，那表明shared_buffers不够，应该扩大shared_buffers;
2.如果大量的usagecount都是0或者1，那表明shared_buffers过大，应该减小shared_buffers;