public inbox for [email protected]
help / color / mirror / Atom feedFrom: Robert Haas <[email protected]>
To: Andres Freund <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: Pg Hackers <[email protected]>
Cc: Jeff Davis <[email protected]>
Subject: Re: Eager page freeze criteria clarification
Date: Wed, 6 Sep 2023 10:35:17 -0400
Message-ID: <CA+TgmobvHws+ECTE-4pbw0Uw4ORA7-Kc6WH-C6fJ2zoZ8CKwfQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAAKRu_axe6eM2u5nNH_Lk1tsYM=U8m7y-MXyhVuc7CwQ2QSURQ@mail.gmail.com>
<CAH2-Wz=QQsFkH1g2tpkoT61OG+p6=QmRGE-0wPKcvkC93Vz6KA@mail.gmail.com>
<CAAKRu_Z-AAanGqsLX0ecgK2kv1MSvATxV-F4-cCJQDZu9=TPzA@mail.gmail.com>
<CAAKRu_b8BTWKxtTyayRmdjbh+3VQ-fnFYc2kDRKH62wyp8HUGA@mail.gmail.com>
<CA+TgmoZoCWXoZ8uu8QimTwM3e_b7wCz=EwCvPKpLng=14rR-8g@mail.gmail.com>
<[email protected]>
On Wed, Sep 6, 2023 at 1:09 AM Andres Freund <[email protected]> wrote:
> Yea, it'd be useful to have a reasonably approximate wall clock time for the
> last modification of a page. We just don't have infrastructure for determining
> that. We'd need an LSN->time mapping (xid->time wouldn't be particularly
> useful, I think).
>
> A very rough approximate modification time can be computed by assuming an even
> rate of WAL generation, and using the LSN at the time of the last vacuum and
> the time of the last vacuum, to compute the approximate age.
>
> For a while I thought that'd not give us anything that just using LSNs gives
> us, but I think it might allow coming up with a better cutoff logic: Instead
> of using a cutoff like "page LSN is older than 10% of the LSNs since the last
> vacuum of the table", it would allow us to approximate "page has not been
> modified in the last 15 seconds" or such. I think that might help avoid
> unnecessary freezing on tables with very frequent vacuuming.
Yes. I'm uncomfortable with the last-vacuum-LSN approach mostly
because of the impact on very frequently vacuumed tables, and
secondarily because of the impact on very infrequently vacuumed
tables.
Downthread, I proposed using the RedoRecPtr of the latest checkpoint
rather than the LSN of the previou vacuum. I still like that idea.
It's a value that we already have, with no additional bookkeeping. It
varies over a much narrower range than the interval between vacuums on
a table. The vacuum interval could be as short as tens of seconds as
long as years, while the checkpoint interval is almost always going to
be between a few minutes at the low end and some tens of minutes at
the high end, hours at the very most. That's quite appealing. Also, I
think the time between checkpoints actually matters here, because in
some sense we're looking to get dirty, already-FPI'd pages frozen
before they get written out, or before a new FPI becomes necessary,
and checkpoints are one way for the first of those things to happen
and the only way for the second one to happen.
--
Robert Haas
EDB: http://www.enterprisedb.com
view thread (36+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Eager page freeze criteria clarification
In-Reply-To: <CA+TgmobvHws+ECTE-4pbw0Uw4ORA7-Kc6WH-C6fJ2zoZ8CKwfQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox