作者
digoal
日期
2021-04-09
标签
PostgreSQL , recovery , 合并 , data block , wal record , 预读 data block
背景
PostgreSQL 的恢复过程是 “wal record+相关data block合并” 后回写到数据文件的过程.
wal FPW block不需要从已有data file读取, 因为fpw是完整的data block.
因为IO是比较耗时的操作, 为了加速recovery “wal record+相关data block合并”的过程, PostgreSQL 14 可以异步把即将合并的data block先读取到shared buffer中(已经在shared buffer中的block不会去读data file), 加速recovery过程.
相关参数:
wal_decode_buffer_size: 预先解析多少wal record, 从中获取到这些wal record recovery相关的data block id. 预取这些data block
maintenance_io_concurrency: 控制预读时同时发起的IO操作数. SSD可以调大.
recovery_prefetch: 预读方法
recovery_prefetch_fpw: 释放预读FPW块(通常不需要, 因为fpw是整块写入wal的(防止partial write的机制), 所以这个wal record里面就有整个block)
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/config.sgml;h=26628f3e6d3b0d894443a3ed067324310333e580;hp=ea5cf3a2dc0eb399b898a72ac04fbbc81cf56817;hb=1d257577e08d3e598011d6850fd1025858de8c8c;hpb=f003d9f8721b3249e4aec8a1946034579d40d42c
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1d257577e08d3e598011d6850fd1025858de8c8c
```
Optionally prefetch referenced data in recovery.
author Thomas Munro tmunro@postgresql.org
Thu, 8 Apr 2021 11:03:43 +0000 (23:03 +1200)
committer Thomas Munro tmunro@postgresql.org
Thu, 8 Apr 2021 11:20:42 +0000 (23:20 +1200)
commit 1d257577e08d3e598011d6850fd1025858de8c8c
tree 1c2ac92489fe1e54cdc0837dec9b84d17434c4b7 tree
parent f003d9f8721b3249e4aec8a1946034579d40d42c commit | diff
Optionally prefetch referenced data in recovery.
Introduce a new GUC recovery_prefetch, disabled by default. When
enabled, look ahead in the WAL and try to initiate asynchronous reading
of referenced data blocks that are not yet cached in our buffer pool.
For now, this is done with posix_fadvise(), which has several caveats.
Better mechanisms will follow in later work on the I/O subsystem.
The GUC maintenance_io_concurrency is used to limit the number of
concurrent I/Os we allow ourselves to initiate, based on pessimistic
heuristics used to infer that I/Os have begun and completed.
The GUC wal_decode_buffer_size is used to limit the maximum distance we
are prepared to read ahead in the WAL to find uncached blocks.
Reviewed-by: Alvaro Herrera alvherre@2ndquadrant.com (parts)
Reviewed-by: Andres Freund andres@anarazel.de (parts)
Reviewed-by: Tomas Vondra tomas.vondra@2ndquadrant.com (parts)
Tested-by: Tomas Vondra tomas.vondra@2ndquadrant.com
Tested-by: Jakub Wartak Jakub.Wartak@tomtom.com
Tested-by: Dmitry Dolgov 9erthalion6@gmail.com
Tested-by: Sait Talha Nisanci Sait.Nisanci@microsoft.com
Discussion: https://postgr.es/m/CA%2BhUKGJ4VJN8ttxScUFM8dOKX0BrBiboo5uz1cq%3DAovOddfHpA%40mail.gmail.com
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
