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

On Wed, Oct 31, 2018 at 1:38 AM Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
wrote:

> Hi,
>
> 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.
>
> Is there a way to speed that up? The disk can easily sustain 400-500
> MB/sec.
>

WAL replay is single-threaded, so the most you would be able to speed it up
is 50%, to where it would be taking 100% CPU.

Is the time spent not on the CPU being spent waiting for WAL files to
arrive from the restore_command, or waiting for the blocks it needs to
replay into to get read from disk, or waiting for dirty pages to get
written to disk so they can be evicted to make way for new ones?

One way I found to speed up restore_command is to have another program run
a few WAL files ahead of it, copying the WAL from the real archive into a
scratch space which is on the same filesystem as pg_xlog/pg_wal. Then have
restore_command simply move (not copy) the requested files from the scratch
space onto %p. The intra-filesystem move completes much faster than a copy.

If it spends time waiting for blocks that need to be recovered into to get
read from disk, and you have enough RAM, you could speed it up by
pre-warming the file system cache. Something like:

tar -cf - $PGDATA | wc -c

Cheers,

Jeff