PostgreSQL WAL replay 加速(datapage preload) - 恢複加速, 備庫延遲優化

标簽

PostgreSQL , replace , standby , recovery , preload , 預加載

https://github.com/digoal/blog/blob/master/201807/20180711_03.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL 資料庫恢複時，讀取wal，如果目前wal page不是full page，則從這筆wal record對應的data file中拿到datapage，與wal record合并，覆寫對應data page。持續讀取wal 實作恢複的目的。

需要注意wal是順序讀寫，而data file可能是離散讀寫（大部分oltp業務都是如此），WAL的目的就是要将離散的DATA FILE寫變成順序的IO。

那麼問題來了，恢複時，data file就變成了離散的讀操作。

在主庫WAL産生量非常巨大時，standby recovery(replay)将會導緻與主庫的延遲，通常wal write不會有大的延遲（因為WAL是順序寫），replay的延遲主要是recovery時data file的離散讀導緻。

如何降低離散讀呢？

DBAs struggling with replication lag is nothing new. A large volume of data or write IO comes into the system and the followers struggle to keep up. pg_prefaulter was written to eliminate replication lag on followers and also improves database startup times.

If your database is under 24/7 write workload, has periodic replication lag that is unacceptable, or want to reduce the startup time of PostgreSQL, pg_prefaulter will help all three of these scenarios.

At Joyent we use PostgreSQL as the metadata tier for our object storage system, Manta. This talk chronicles how we identified our source of replication lag and why we found it necessary to write pgprefaulter. pgprefaulter is a sidecar process for PostgreSQL written in Go that pre-fetches pages from disk and loads them into the operating system's filesystem cache before PostgreSQL requests them during the startup and application of WAL records.

Additionally, this talk also discusses:

the design considerations that went into writing pg_prefaulter

the various forms of "replication lag" in PostgreSQL (WAL receive lag, WAL apply lag, and checkpoint lag)

pathologies that came from deploying pg_prefaulter

why we now consider pg_prefaulter mission critical software for our production databases

tips for deploying pg_prefaulter

https://github.com/digoal/blog/blob/master/201807/20180711_03.md#%E4%BC%98%E5%8C%96%E6%96%B9%E6%B3%95 優化方法

在備庫接收到WAL後，解析WAL，并提前将需要用到的DATA FILE PAGE加載到OS PAGE CACHE中，在postgresql startup process replay wal時，讀取需要的data page時，從os cache讀取，進而降低replay時因為讀取data page帶來的IO等待。

patch:

http://www.postgresql-archive.org/WAL-prefetch-td6024900.html

https://github.com/digoal/blog/blob/master/201807/20180711_03.md#%E5%8F%82%E8%80%83 參考

pg_prefaulter: Scaling WAL Performance (application/pdf - 2.6 MB) http://www.pgcon.org/2018/schedule/events/1204.en.html https://github.com/joyent/pg_prefaulter