RE: [EXTERNAL] Re: WIP: WAL prefetch (another approach) - Mailing list pgsql-hackers

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
asfollows (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.munro@gmail.com> 
Sent: Thursday, August 13, 2020 9:57 AM
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Stephen Frost <sfrost@snowman.net>; Dmitry Dolgov <9erthalion6@gmail.com>; David Steele <david@pgmasters.net>;
AndresFreund <andres@anarazel.de>; Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-hackers
<pgsql-hackers@postgresql.org>
Subject: [EXTERNAL] Re: WIP: WAL prefetch (another approach)

On Thu, Aug 6, 2020 at 10:47 PM Tomas Vondra <tomas.vondra@2ndquadrant.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.