From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: David Steele <david@pgmasters.net>
Cc: Andres Freund <andres@anarazel.de>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: WIP: WAL prefetch (another approach)
Date: Tue, 1 Sep 2020 15:14:26 +0200
Message-ID: <20200901131426.5lse2jlepm3uligv@development> (raw)
In-Reply-To: <CA+hUKGKciMMJbE-1+9oPKte977b++UU_X16kkOXTF_7QjP4+jA@mail.gmail.com>
References: <20200425191935.4p6qxzzu5bklodwk@localhost>
<20200502151423.yf52i63u232fdfrg@localhost>
<CA+hUKG+obB-e_VFa2uPVe52DvtpBQtC30vyo_PDURJQTr3SdJQ@mail.gmail.com>
<20200605223422.r76a5rpl2zg2xo34@development>
<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>
On Thu, Aug 13, 2020 at 06:57:20PM +1200, Thomas Munro wrote:
>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.
I've decided to do some tests with this patch version, but I immediately
ran into issues. What I did was initializing a 32GB pgbench database,
backed it up (shutdown + tar) and then ran 2h pgbench with archiving.
And then I restored the backed-up data directory and instructed it to
replay WAL from the archive. There's about 16k WAL segments, so about
256GB of WAL.
Unfortunately, the very first thing that happens after starting the
recovery is this:
LOG: starting archive recovery
LOG: restored log file "000000010000001600000080" from archive
LOG: consistent recovery state reached at 16/800000A0
LOG: redo starts at 16/800000A0
LOG: database system is ready to accept read only connections
LOG: recovery started prefetching on timeline 1 at 0/800000A0
LOG: recovery no longer prefetching: unexpected pageaddr 8/84000000 in log segment 000000010000001600000081, offset 0
LOG: restored log file "000000010000001600000081" from archive
LOG: restored log file "000000010000001600000082" from archive
So we start applying 000000010000001600000081 and it fails almost
immediately on the first segment. This is confirmed by prefetch stats,
which look like this:
-[ RECORD 1 ]---+-----------------------------
stats_reset | 2020-09-01 15:02:31.18766+02
prefetch | 1044
skip_hit | 1995
skip_new | 87
skip_fpw | 2108
skip_seq | 27
distance | 0
queue_depth | 0
avg_distance | 135838.95
avg_queue_depth | 8.852459
So we do a little bit of prefetching and then it gets disabled :-(
The segment looks perfectly fine when inspected using pg_waldump, see
the attached file.
I've tested this applied on 6ca547cf75ef6e922476c51a3fb5e253eef5f1b6,
and the failure seems fairly similar to what I reported before, except
that now it happened right at the very beginning.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
000000010000001600000081.log.gz (application/gzip, 99.4K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, thomas.munro@gmail.com, sfrost@snowman.net, 9erthalion6@gmail.com, david@pgmasters.net, andres@anarazel.de, alvherre@2ndquadrant.com
Subject: Re: WIP: WAL prefetch (another approach)
In-Reply-To: <20200901131426.5lse2jlepm3uligv@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox