PostgreSQL-shared_buffers（双缓存）
关于shared_buffers
-
这是一篇2018年写的，可以结合 shared read一起看
什么是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需要安装详细的步骤
查询 shared_buffers 占比和缓存情况：
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,
round(
sum(pages_mem) *
4 /
1024,
0)
AS os_cache_MB,
round(
100 *
sum(pages_mem) *
4096 / 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
预热缓存和查看结果：
-- 表缓存预热
SELECT pg_prewarm(
'pgbench_accounts',
'buffer',
'main');
-- 索引预热
SELECT pg_prewarm(
'pgbench_accounts_pkey',
'buffer',
'main');
-- 预热后查看缓存
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,
round(
sum(pages_mem) *
4 /
1024,
0)
AS os_cache_MB,
round(
100 *
sum(pages_mem) *
4096 / 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
如何设定shared_buffers？
使用pg_buffercache可查看缓存使用情况，以及命中次数和脏块
缓存命中数
-- 缓存命中数
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 |
数据在缓存中的占比：
-- 数据在缓存中占比
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
从结果看出，缓存中存储了完整的表和索引，占总缓存的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
测试脚本：
-- 事务延迟（毫秒）
\
set aid random(
1,
100000 * :scale)
\
set bid random(
1,
1 * :scale)
\
set tid random(
1,
10 * :scale)
\
set delta random(
-5000,
5000)
-- 执行事务
BEGIN;
UPDATE pgbench_accounts
SET abalance = abalance + :delta
WHERE aid = :aid;
SELECT abalance
FROM pgbench_accounts
WHERE aid = :aid;
UPDATE pgbench_tellers
SET tbalance = tbalance + :delta
WHERE tid = :tid;
UPDATE pgbench_branches
SET bbalance = bbalance + :delta
WHERE bid = :bid;
INSERT
INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta,
CURRENT_TIMESTAMP);
END;
结果：
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测试结果
SELECT
'shared_buffers=128MB（默认）'
AS config,
249
AS first_time,
126
AS second_time,
145
AS third_time,
173
AS average_tps
UNION
SELECT
'shared_buffers=4GB',
357,
357,
373,
362
UNION
SELECT
'shared_buffers=8GB',
362,
363,
415,
380
UNION
SELECT
'shared_buffers=24GB',
378,
368,
397,
381;
shared_buffers设置为8GB（25%）和设置为24GB（75%）差别不大。
预热缓存测试结果：
-- TPS测试结果
SELECT
'shared_buffers=128MB（默认）'
AS config,
211
AS first_time,
194
AS second_time,
207
AS third_time,
204
AS average_tps
UNION
SELECT
'shared_buffers=4GB',
1225,
1288,
1321,
1278
UNION
SELECT
'shared_buffers=8GB',
1176,
1291,
1144,
1203
UNION
SELECT
'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.在预热数据的过程中可以考虑先做索引的预热，因为要做索引的情况加载索引会比较慢。
#PG数据库工程师的摇篮#PostgreSQL考试#PostgreSQL培训
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69998444/viewspace-2998470/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- PostgreSQL Certified Master 专访 | 第三期 李洋
- 【PGCCC】在 Postgres 中构建复制安全的 LSM 树
- 【PGCCC】PostgreSQL 18 Beta 1发布，有哪些亮点？
- 【PGCCC】Redash：一个开源的数据查询与可视化工具
- 【PGCCC】PostgreSQL Certified Master 个人专访 | 第一期 王丁丁
- 【PGCCC】PostgreSQL 的弊端（第一部分）：临时表
- 【PGCCC】pg_squeeze 1.8：自动表膨胀清理的扩展
- 【PGCCC】commit_delay 对性能的提升：PostgreSQL 基准测试
- 【PGCCC】在 PostgreSQL 上使用 pg_hint_plan替换 Oracle Hints 的最佳实践
- 【PGCCC】表级锁剖析：减少锁定影响
