pg进程和内存体系架构

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((40960.251024)/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;