本文深入探讨PostgreSQL中shared_buffers参数的设置与优化，解析其在数据库性能提升中的关键作用，包括缓存机制、预热策略及对不同内存配置下TPS的影响。
postgresql shared_buffers 讲解
什么是shred_buffer，我们为什么需要shared_buffers？
1.在数据库系统中，我们主要关注磁盘io，大多数oltp工作负载都是随机io，因此从磁盘获取非常慢。
2.为了解决这个问题，postgres将数据缓存在RAM中，以此来提高性能，即使ssd的情况下RAM也要快很多。
3.shared_buffers是一个8KB的数组，postgres在从磁盘中查询数据前，会先查找shared_buffers的页，如果命中，就直接返回，避免从磁盘查询。
shared_buffers存储什么？
1.表数据
2.索引，索引也存储在8K块中。
3.执行计划，存储基于会话的执行计划，会话结束，缓存的计划也就被丢弃。
什么时候加载shared_buffers？
1.在访问数据时，数据会先加载到os缓存，然后再加载到shared_buffers，这个加载过程可能是一些查询，也可以使用pg_prewarm预热缓存。
2.当然也可能同时存在os和shared_buffers两份一样的缓存（双缓存）。
3.查找到的时候会先在shared_buffers查找是否有缓存，如果没有再到os缓存查找，最后再从磁盘获取。
4.os缓存使用简单的LRU（移除最近最久未使用的缓存），而数据库采用的优化的时钟扫描，即缓存使用频率高的会被保存，低的被移除。
shared_buffers设置的合理范围
1.windows服务器有用范围是64MB到512MB，默认128MB
2.linux服务器建议设置为25%，亚马逊服务器设置为75%（避免双缓存，数据会存储在os和shared_buffers两份）
os缓存的重要性
数据写入时，从内存到磁盘，这个页面就会被标记为脏页，一旦被标记为脏页，它就会被刷新到os缓存，然后写入磁盘。所以如果os高速缓存大小较小，则它不能重新排序写入并优化io，这对于繁重的写入来说非常致命，因此os的缓存大小也非常重要。给予shared_buffers太大或太小都会损害性能。
查看shared_buffers，os缓存
这里需要使用到两个插件，pg_bufferscache系统已经自带可以直接创建扩展，pgfincore需要安装详细的步骤
pg_buffered表占用缓存大小
pg_buffer_percent:表占用整个缓存的占比
percent_of_relation：缓存数据和该表数据占比
os_cache_mb:表占用os系统缓存大小
os_cache_percent_of_relation：os缓存和表占比
rel_size:整个表大小
pgbench=# 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_percent | percent_of_relation | os_cache_mb | os_cache_percent_of_relation | rel_size
-----------------------+-------------+------------------+---------------------+-------------+------------------------------+----------
pgbench_accounts | 471 MB | 1.9 | 7.3 | 495 | 7.7 | 6416 MB
pgbench_accounts_pkey | 139 MB | 0.6 | 13.0 | 274 | 25.6 | 1071 MB
pgbench_history | 2704 kB | 0.0 | 86.9 | 3 | 99.2 | 3112 kB
pgbench_branches_pkey | 56 kB | 0.0 | 100.0 | 0 | 100.0 | 56 kB
pgbench_tellers_pkey | 240 kB | 0.0 | 100.0 | 0 | 100.0 | 240 kB
pgbench_branches | 2968 kB | 0.0 | 70.7 | 4 | 99.2 | 4200 kB
pgbench_tellers | 608 kB | 0.0 | 100.0 | 1 | 94.7 | 608 kB
(7 rows)
--表缓存预热
pgbench=# select pg_prewarm('pgbench_accounts', 'buffer', 'main');
pg_prewarm
------------
820956
(1 row)
--索引预热：
pgbench=# select pg_prewarm('pgbench_accounts_pkey', 'buffer', 'main');
pg_prewarm
------------
137099
(1 row)
--预热后查看缓存
pgbench=# 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_percent | percent_of_relation | os_cache_mb | os_cache_percent_of_relation | rel_size
-----------------------+-------------+------------------+---------------------+-------------+------------------------------+----------
pgbench_accounts | 6414 MB | 26.1 | 100.0 | 6414 | 100.0 | 6416 MB
pgbench_accounts_pkey | 139 MB | 0.6 | 13.0 | 274 | 25.6 | 1071 MB
pgbench_history | 2704 kB | 0.0 | 86.9 | 3 | 99.2 | 3112 kB
pgbench_branches_pkey | 56 kB | 0.0 | 100.0 | 0 | 100.0 | 56 kB
pgbench_tellers_pkey | 240 kB | 0.0 | 100.0 | 0 | 100.0 | 240 kB
pgbench_branches | 2968 kB | 0.0 | 70.7 | 4 | 99.2 | 4200 kB
pgbench_tellers | 608 kB | 0.0 | 100.0 | 1 | 94.7 | 608 kB
(7 rows)
可以看到将数据加载至shared_buffers，并且os也缓存了一份。正常情况os不应该缓存这么多的数据。
如何设定shared_buffers？
使用pg_buffercache可查看缓存使用情况，以及命中次数和脏块
--1.缓存命中数
pgbench=# select usagecount,count(*),isdirty from pg_buffercache group by isdirty, usagecount order by isdirty, usagecount ;
usagecount | count | isdirty
------------+---------+---------
1 | 6651 | f
2 | 762250 | f
3 | 54684 | f
4 | 12630 | f
5 | 3940 | f
| 2305573 |
(6 rows)
--2.数据在缓存中占比
pgbench=# 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())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC LIMIT 10;
relname | buffered | buffers_percent | percent_of_relation
-----------------------+------------+-----------------+---------------------
pgbench_accounts | 6414 MB | 26.1 | 100.0
pgbench_accounts_pkey | 1071 MB | 4.4 | 100.0
pg_amop | 56 kB | 0.0 | 87.5
pg_cast | 16 kB | 0.0 | 100.0
pg_constraint | 8192 bytes | 0.0 | 100.0
pg_index | 32 kB | 0.0 | 100.0
pg_opclass | 16 kB | 0.0 | 66.7
pg_namespace | 8192 bytes | 0.0 | 100.0
pg_operator | 120 kB | 0.0 | 100.0
pg_amproc | 40 kB | 0.0 | 100.0
(10 rows)
缓存中存储了完整的表，和索引，占总缓存的30%，占比很低缓存剩余很多。
1.如果大量的usagecount都是4或者5，那表明shared_buffers不够，应该扩大shared_buffers;
2.如果大量的usagecount都是0或者1，那表明shared_buffers过大，应该减小shared_buffers;
每当共享内存中使用一个块时，他就会增加一次时钟扫描算法，范围从1-5。4和5标识极高的使用数据块，高使用可能会保留在shared_buffers中(有空间)，如果需要更高使用率的空间，则低使用率的块将被移除，一般简单的插入或者更新会将使用次数设置为1。
缓存占比低。可以确定的是如果我们的数据集非常小，那么设置较大的shared_buffers，没什么区别。
pgbench性能测试（shared_buffers 128MB,4GB,8GB,24GB）
PostgreSQL默认测试脚本，含UPDATE、INSERT还有SELECT等操作。通过修改shared_buffers大小来测试tps。
数据库版本：PostgreSQL 10.4 (ArteryBase 5.0.0, Thunisoft)
操作系统配置：CentOS Linux release 7 ，32GB内存，8 cpu
测试参数：初始化5000w数据：pgbench -i -s 500 -h localhost -U sa -d pgbench
测试方法：pgbench -c 500 -t 20 -n -r pgbench 模拟500客户端，每个客户端20个事务，每种配置参数执行三次，记录tps值。
数据库物理大小：数据库总大小7503 MB，其中表总大小pgbench_accounts：7487 MB，索引pgbench_accounts_pkey ：1071 MB
测试脚本：
- statement latencies in milliseconds:
0.002 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
9.478 BEGIN;
14.575 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
6.758 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
130.573 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
786.933 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
5.355 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1242.835 END;
未预热缓存测试结果：
序号 | 第一次 | 第二次 | 第三次 | 平均值(tps)
-----------------------------+---------+---------+---------+---------
shared_buffers=128MB（默认）| 249 | 126 | 145 | 173
shared_buffers=4GB | 357 | 357 | 373 | 362
shared_buffers=8GB | 362 | 363 | 415 | 380
shared_buffers=24GB | 378 | 368 | 397 | 381
shared_buffers设置为8GB（25%）和设置为24GB（75%）差别不大。
预热缓存测试结果：
序号 | 第一次 | 第二次 | 第三次 | 平均值(tps)
-----------------------------+---------+---------+---------+---------
shared_buffers=128MB（默认）| 211 | 194 | 207 | 204
shared_buffers=4GB | 1225 | 1288 | 1321 | 1278
shared_buffers=8GB | 1176 | 1291 | 1144 | 1203
shared_buffers=24GB | 1285 | 1250 | 1309 | 1281
当shared_buffers=4GB时，数据6GB不能完全装下，所以优先预热索引，将索引加载到缓存，然后在加载数据。可以看到最终shared_buffers=4GB的tps和8GB，24GB表现差别不大。
内存结构
1.本地内存：work_mem,maintenance_work_mem,temp_buffer，进程分配
2.共享内存:shared_buffers,wal buffers，commitLog buffer
本地内存*max_connections+共享内存+服务器使用内存<=总内存
小结
1.大多数情况设置shared_buffers为内存的25%，当然为了最优可以根据命中，以及缓存占比调整。
2.设置shared_buffers为75%和25%相差不大，也和数据量一共只有7G+有关系。但是os系统缓存同样重要，而设置为75%，可能会超过总内存。
3.设置所有的缓存需要注意不要超过总内存大小。
4.在预热数据的过程中可以考虑先做索引的预热，因为要做索引的情况加载索引会比较慢。
扫一扫
648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
