Re: WAL prefetch

On 2018-06-16 23:31:49 +0300, Konstantin Knizhnik wrote:
>
>
> On 16.06.2018 22:23, Andres Freund wrote:
> > Hi,
> >
> > On 2018-06-13 16:09:45 +0300, Konstantin Knizhnik wrote:
> > > Usage:
> > > 1. At master: create extension wal_prefetch
> > > 2. At replica: Call pg_wal_prefetch() function: it will not return until you
> > > interrupt it.
> > FWIW, I think the proper design would rather be a background worker that
> > does this work. Forcing the user to somehow coordinate starting a
> > permanently running script whenever the database restarts isn't
> > great. There's also some issues around snapshots preventing vacuum
> > (which could be solved, but not nicely).
>
> As I already wrote, the current my approach with extension and
> pg_wal_prefetch function called by user can be treated only as prototype
> implementation which can be used to estimate efficiency of prefetch. But in
> case of prefetching in shared buffers, one background worker will not be
> enough. Prefetch can can speedup recovery process if it performs reads in
> parallel or background. So more than once background worker will be needed
> for prefetch if we read data to Postgres shared buffers rather then using
> posix_prefetch to load page in OS cache.

Sure, we'd need more than one to get the full benefit, but that's not
really hard. You'd see benefit even with a single process, because WAL
replay often has a lot of other bottlenecks too. But no reason to not
have multiple ones.

Greetings,

Andres Freund