Re: WAL prefetch - Mailing list pgsql-hackers

From Andres Freund
Subject Re: WAL prefetch
Date
Msg-id 20180617000126.3bq5hixbncnv5sfi@alap3.anarazel.de
Whole thread Raw
In response to Re: WAL prefetch  (Konstantin Knizhnik <k.knizhnik@postgrespro.ru>)
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: WAL prefetch
Next
From: Alvaro Herrera
Date:
Subject: Re: Removing "Included attributes in B-tree indexes" section fromdocs