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 <[email protected]>
Sent: Thursday, August 13, 2020 9:57 AM
To: Tomas Vondra <[email protected]>
Cc: Stephen Frost <[email protected]>; Dmitry Dolgov <[email protected]>;
David Steele <[email protected]>; Andres Freund <[email protected]>; Alvaro
Herrera <[email protected]>; pgsql-hackers <[email protected]>
Subject: [EXTERNAL] Re: WIP: WAL prefetch (another approach)
On Thu, Aug 6, 2020 at 10:47 PM Tomas Vondra <[email protected]>
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.
