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.