Re: Is there a way to speed up WAL replay?

Greetings,

* Torsten Förtsch (tfoertsch123(at)gmail(dot)com) wrote:
> I am working on restoring a database from a base backup + WAL. With the
> default settings the database replays about 3-4 WAL files per second. The
> startup process takes about 65% of a CPU and writes data with something
> between 50 and 100 MB/sec.

What are you using for a restore_command..? You can typically get some
improvement by using a restore_command that's faster or pre-stages files
locally to minimize the time required to run, or dumping all the WAL
into the xlog, but that's not something I typically recommend.

> Is there a way to speed that up? The disk can easily sustain 400-500 MB/sec.

Kind of depends what you're ultimately going for.. If you've made
everything else faster and you're still only seeing 65% CPU utilization
on the one CPU, then maybe there's just enough latency to the disks to
be an issue, in which case you could possibly restore on to a ramdisk if
you've got enough memory/space and don't mind that.

There's other things that can be done too, like adjusting the amount of
shared buffers; depending on what you're doing that can also make a
difference in replay speed (we've been working to improve that though).

I tend to find that it's better to just reduce the amount of WAL that
needs to be replayed by taking incremental backups more frequently and
using things like pgbackrest's delta restore ability for doing repeated
restores to the same location. Of course, that's not likely to help you
out much here.

Thanks!

Stephen