RE: [EXTERNAL] Re: WIP: WAL prefetch (another approach)

I have run some benchmarks for this patch. Overall it seems that there is a good improvement with the patch on recovery times:

The VMs I used have 32GB RAM, pgbench is initialized with a scale factor 3000(so it doesn’t fit to memory, ~45GB).

In order to avoid checkpoints during benchmark, max_wal_size(200GB) and checkpoint_timeout(200 mins) are set to a high value.

The run is cancelled when there is a reasonable amount of WAL ( > 25GB). The recovery times are measured from the REDO logs.

I have tried combination of SSD, HDD, full_page_writes = on/off and max_io_concurrency = 10/50, the recovery times are as follows (in seconds):

No prefetch | Default prefetch values | Default + max_io_concurrency = 50
SSD, full_page_writes = on 852 301 197
SSD, full_page_writes = off 1642 1359 1391
HDD, full_page_writes = on 6027 6345 6390
HDD, full_page_writes = off 738 275 192

Default prefetch values:
- Max_recovery_prefetch_distance = 256KB
- Max_io_concurrency = 10

It probably makes sense to compare each row separately as the size of WAL can be different.

Talha.

-----Original Message-----
From: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
Sent: Thursday, August 13, 2020 9:57 AM
To: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>; Dmitry Dolgov <9erthalion6(at)gmail(dot)com>; David Steele <david(at)pgmasters(dot)net>; Andres Freund <andres(at)anarazel(dot)de>; Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>; pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [EXTERNAL] Re: WIP: WAL prefetch (another approach)

On Thu, Aug 6, 2020 at 10:47 PM Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> On Thu, Aug 06, 2020 at 02:58:44PM +1200, Thomas Munro wrote:
> >On Tue, Aug 4, 2020 at 3:47 AM Tomas Vondra
> >> Any luck trying to reproduce thigs? Should I try again and collect
> >> some additional debug info?
> >
> >No luck. I'm working on it now, and also trying to reduce the
> >overheads so that we're not doing extra work when it doesn't help.
>
> OK, I'll see if I can still reproduce it.

Since someone else ask me off-list, here's a rebase, with no functional changes. Soon I'll post a new improved version, but this version just fixes the bitrot and hopefully turns cfbot green.