PostgreSQL 10.0 preview 功能增强 - 缓存自动预热, 直达性能巅峰

标签

PostgreSQL , 10.0 , 自动预热缓存

---

背景

数据库的shared buffer可以用来存储经常使用的数据块，以提升效率。通过LRU算法老化不常用的数据块。

因此在生产数据库中，SHARED BUFFER中通常是热数据。 

数据库如果重启，或者发生主备切换，缓存中的热数据需要从磁盘（或者OS PAGE CACHE）重新载入数据库的shared buffer。

当这个事情发生在业务高峰期时，由于热数据未在数据库缓存中，访问将会变慢，用户的感觉可能是请求变慢了。

为了提升用户体验，减少请求的响应时间的抖动，PostgreSQL 10.0推出了自动预热缓存的技术。

也就是说shared buffer的block list chain会记录下来，下次启动时，自动载入shared buffer.

# pg_autoprewarm.  

This a PostgreSQL contrib module which automatically dump all of the  
blocknums  
present in buffer pool at the time of server shutdown(smart and fast mode  
only,  
to be enhanced to dump at regular interval.) and load these blocks when  
server restarts.  

Design:  
------  
We have created a BG Worker Auto Pre-warmer which during shutdown dumps all  
the  
blocknum in buffer pool in sorted order.  
Format of each entry is  
<DatabaseId,TableSpaceId,RelationId,Forknum,BlockNum>.  
Auto Pre-warmer is started as soon as the postmaster is started we do not  
wait  
for recovery to finish and database to reach a consistent state. If there  
is a  
"dump_file" to load we start loading each block entry to buffer pool until  
there is a free buffer. This way we do not replace any new blocks which was  
loaded either by recovery process or querying clients. Then it waits until  
it receives  
SIGTERM to dump the block information in buffer pool.  

HOW TO USE:  
-----------  
Build and add the pg_autoprewarm to shared_preload_libraries. Auto  
Pre-warmer  
process automatically do dumping of buffer pool's block info and load them  
when  
restarted.  

TO DO:  
------  
Add functionality to dump based on timer at regular interval.  
And some cleanups.  
--   
Thanks and Regards  
Mithun C Y  
EnterpriseDB: http://www.enterprisedb.com  

这个patch的讨论，详见邮件组，本文末尾URL。

PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。

参考

https://commitfest.postgresql.org/13/848/

https://www.postgresql.org/message-id/flat/CAD__Ougw7Kc+1-cGA8GM0t+FuUHqPYR9Aow6OZX48sbH=V8BWw@mail.gmail.com#CAD__Ougw7Kc+1-cGA8GM0t+FuUHqPYR9Aow6OZX48sbH=V8BWw@mail.gmail.com