RE: Use fadvise in wal replay - Mailing list pgsql-hackers

From Jakub Wartak
Subject RE: Use fadvise in wal replay
Date
Msg-id AM8PR07MB82480E22146937BAD6A4AE02F6B39@AM8PR07MB8248.eurprd07.prod.outlook.com
Whole thread Raw
In response to Re: Use fadvise in wal replay  (Thomas Munro <thomas.munro@gmail.com>)
Responses Re: Use fadvise in wal replay
List pgsql-hackers
> On Tue, Jun 21, 2022 at 10:33 PM Jakub Wartak <Jakub.Wartak@tomtom.com>
> wrote:
> > > > Maybe the important question is why would be readahead mechanism
> > > > be
> > > disabled in the first place via /sys | blockdev ?
> > >
> > > Because database should know better than OS which data needs to be
> > > prefetched and which should not. Big OS readahead affects index scan
> > > performance.
> >
> > OK fair point, however the patch here is adding 1 syscall per XLOG_BLCKSZ
> which is not cheap either. The code is already hot and there is example from the
> past where syscalls were limiting the performance [1]. Maybe it could be
> prefetching in larger batches (128kB? 1MB? 16MB?)  ?
> 
> I've always thought we'd want to tell it about the *next* segment file, to
> smooth the transition from one file to the next, something like the attached (not
> tested).

Hey Thomas! 

Apparently it's false theory. Redo-bench [1] results (1st is total recovery time in seconds, 3.1GB pgdata (out of which
2.6pg_wals/166 files). Redo-bench was slightly hacked to drop fs caches always after copying so that there is nothing
infscache (both no pgdata and no pg_wals; shared fs). M_io_c is at default (10), recovery_prefetch same (try; on by
default)

master, default Linux readahead (128kb):
33.979, 0.478
35.137, 0.504
34.649, 0.518

master, blockdev --setra 0 /dev/nvme0n1: 
53.151, 0.603
58.329, 0.525
52.435, 0.536

master, with yours patch (readaheads disabled) -- double checked, calls to fadvise64(offset=0 len=0) were there
58.063, 0.593
51.369, 0.574
51.716, 0.59

master, with Kirill's original patch (readaheads disabled)
38.25, 1.134
36.563, 0.582
37.711, 0.584

I've noted also that in both cases POSIX_FADV_SEQUENTIAL is being used instead of WILLNEED (?). 
I haven't quantified the tradeoff of master vs Kirill's with readahead, but I think that 1 additional syscall is not
goingto be cheap just for non-standard OS configurations (?)
 

-J.

[1] - https://github.com/macdice/redo-bench

pgsql-hackers by date:

Previous
From: Aleksander Alekseev
Date:
Subject: Re: doc: Bring mention of unique index forced transaction wait behavior outside of the internal section
Next
From: Przemysław Sztoch
Date:
Subject: Re: generate_series for timestamptz and time zone problem