Date: Wed, 9 Sep 2020 01:16:27 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>,
	Dmitry Dolgov <9erthalion6@gmail.com>,
	David Steele <david@pgmasters.net>,
	Andres Freund <andres@anarazel.de>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: WAL prefetch (another approach)
Message-ID: <20200908231235.lsgat5ni35h6odqt@development>
References: <20200606003632.GS6680@tamriel.snowman.net>
 <CA+hUKG+oXRpmc=yOM-GCQMt+Q4GPt9dD_idhPhFVf6f=_owwvA@mail.gmail.com>
 <20200803154659.nzf5qzuozts5dujp@development>
 <CA+hUKGKfsWj+4qPfOkC4YtYtVO33yrCty1uWxBp3R1OSqWEcyA@mail.gmail.com>
 <20200806104701.j3xnkczd6qelfnhj@development>
 <CA+hUKGKciMMJbE-1+9oPKte977b++UU_X16kkOXTF_7QjP4+jA@mail.gmail.com>
 <20200901131426.5lse2jlepm3uligv@development>
 <CA+hUKG+9NUUbdQqFNCz2SKBdyCCD7SQwJy-Y6_PZmCnc+h79ig@mail.gmail.com>
 <20200901141826.gbhqz664brtt4y2t@development>
 <CA+hUKGLr6nU7yb8CO7_DEppBmPCOkCUvE6HBza8himYV_s5nuQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CA+hUKGLr6nU7yb8CO7_DEppBmPCOkCUvE6HBza8himYV_s5nuQ@mail.gmail.com>
Precedence: bulk

On Sat, Sep 05, 2020 at 12:05:52PM +1200, Thomas Munro wrote:
>On Wed, Sep 2, 2020 at 2:18 AM Tomas Vondra
><tomas.vondra@2ndquadrant.com> wrote:
>> On Wed, Sep 02, 2020 at 02:05:10AM +1200, Thomas Munro wrote:
>> >On Wed, Sep 2, 2020 at 1:14 AM Tomas Vondra
>> ><tomas.vondra@2ndquadrant.com> wrote:
>> >> from the archive
>> >
>> >Ahh, so perhaps that's the key.
>>
>> Maybe. For the record, the commands look like this:
>>
>> archive_command = 'gzip -1 -c %p > /mnt/raid/wal-archive/%f.gz'
>>
>> restore_command = 'gunzip -c /mnt/raid/wal-archive/%f.gz > %p.tmp && mv %p.tmp %p'
>
>Yeah, sorry, I goofed here by not considering archive recovery
>properly.  I have special handling for crash recovery from files in
>pg_wal (XLRO_END, means read until you run out of files) and streaming
>replication (XLRO_WALRCV_WRITTEN, means read only as far as the wal
>receiver has advertised as written in shared memory), as a way to
>control the ultimate limit on how far ahead to read when
>maintenance_io_concurrency and max_recovery_prefetch_distance don't
>limit you first.  But if you recover from a base backup with a WAL
>archive, it uses the XLRO_END policy which can run out of files just
>because a new file hasn't been restored yet, so it gives up
>prefetching too soon, as you're seeing.  That doesn't cause any
>damage, but it stops doing anything useful because the prefetcher
>thinks its job is finished.
>
>It'd be possible to fix this somehow in the two-XLogReader design, but
>since I'm testing a new version that has a unified
>XLogReader-with-read-ahead I'm not going to try to do that.  I've
>added a basebackup-with-archive recovery to my arsenal of test
>workloads to make sure I don't forget about archive recovery mode
>again, but I think it's actually harder to get this wrong in the new
>design.  In the meantime, if you are still interested in studying the
>potential speed-up from WAL prefetching using the most recently shared
>two-XLogReader patch, you'll need to unpack all your archived WAL
>files into pg_wal manually beforehand.

OK, thanks for looking into this. I guess I'll wait for an updated patch
before testing this further. The storage has limited capacity so I'd
have to either reduce the amount of data/WAL or juggle with the WAL
segments somehow. Doesn't seem worth it.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services