From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Sait Talha Nisanci <Sait.Nisanci@microsoft.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: David Steele <david@pgmasters.net>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [EXTERNAL] Re: WIP: WAL prefetch (another approach)
Date: Sun, 30 Aug 2020 00:14:50 +0200
Message-ID: <20200829221450.t7omssadp2i6bbcx@development> (raw)
In-Reply-To: <20200827202853.GT29590@tamriel.snowman.net>
References: <20200827182642.GO29590@tamriel.snowman.net>
<6B918AFB-88D6-4687-8B4A-34C472E48BF4@anarazel.de>
<20200827185116.GQ29590@tamriel.snowman.net>
<CA+TgmoYSnACj2ouDKVO+vJ=3r_=ChhmE2mvy2DGOKbpDeTYG+Q@mail.gmail.com>
<20200827202853.GT29590@tamriel.snowman.net>
On Thu, Aug 27, 2020 at 04:28:54PM -0400, Stephen Frost wrote:
>Greetings,
>
>* Robert Haas (robertmhaas@gmail.com) wrote:
>> On Thu, Aug 27, 2020 at 2:51 PM Stephen Frost <sfrost@snowman.net> wrote:
>> > > Hm? At least earlier versions didn't do prefetching for records with an fpw, and only for subsequent records affecting the same or if not in s_b anymore.
>> >
>> > We don't actually read the page when we're replaying an FPW though..?
>> > If we don't read it, and we entirely write the page from the FPW, how is
>> > pre-fetching helping..?
>>
>> Suppose there is a checkpoint. Then we replay a record with an FPW,
>> pre-fetching nothing. Then the buffer gets evicted from
>> shared_buffers, and maybe the OS cache too. Then, before the next
>> checkpoint, we again replay a record for the same page. At this point,
>> pre-fetching should be helpful.
>
>Sure- but if we're talking about 25GB of WAL, on a server that's got
>32GB, then why would those pages end up getting evicted from memory
>entirely? Particularly, enough of them to end up with such a huge
>difference in replay time..
>
>I do agree that if we've got more outstanding WAL between checkpoints
>than the system's got memory then that certainly changes things, but
>that wasn't what I understood the case to be here.
>
I don't think it's very clear how much WAL there actually was in each
case - the message only said there was more than 25GB, but who knows how
many checkpoints that covers? In the cases with FPW=on this may easily
be much less than one checkpoint (because with scale 45GB an update to
every page will log 45GB of full-page images). It'd be interesting to
see some stats from pg_waldump etc.
>> Admittedly, I don't quite understand whether that is what is happening
>> in this test case, or why SDD vs. HDD should make any difference. But
>> there doesn't seem to be any reason why it doesn't make sense in
>> theory.
>
>I agree that this could be a reason, but it doesn't seem to quite fit in
>this particular case given the amount of memory and WAL. I'm suspecting
>that it's something else and I'd very much like to know if it's a
>general "this applies to all (most? a lot of?) SSDs because the
>hardware has a larger than 8KB page size and therefore the kernel has to
>read it", or if it's something odd about this particular system and
>doesn't apply generally.
>
Not sure. I doubt it has anything to do with the hardware page size,
that's mostly transparent to the kernel anyway. But it might be that the
prefetching on a particular SSD has more overhead than what it saves.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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, sfrost@snowman.net, robertmhaas@gmail.com, andres@anarazel.de, Sait.Nisanci@microsoft.com, thomas.munro@gmail.com, 9erthalion6@gmail.com, david@pgmasters.net, alvherre@2ndquadrant.com
Subject: Re: [EXTERNAL] Re: WIP: WAL prefetch (another approach)
In-Reply-To: <20200829221450.t7omssadp2i6bbcx@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