Re: Eager page freeze criteria clarification - Mailing list pgsql-hackers

On Fri, Sep 8, 2023 at 12:07 AM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2023-09-06 10:35:17 -0400, Robert Haas wrote:
> > On Wed, Sep 6, 2023 at 1:09 AM Andres Freund <andres@anarazel.de> 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.
>
> Assuming that "downthread" references
> https://postgr.es/m/CA%2BTgmoYb670VcDFbekjn2YQOKF9a7e-kBFoj2WJF1HtH7YPaWQ%40mail.gmail.com
> could you sketch out the logic you're imagining a bit more?
>
>
> > 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.
>
> The reason I was thinking of using the "lsn at the end of the last vacuum", is
> that it seems to be more adapative to the frequency of vacuuming.
>
> One the one hand, if a table is rarely autovacuumed because it is huge,
> (InsertLSN-RedoRecPtr) might or might not be representative of the workload
> over a longer time. On the other hand, if a page in a frequently vacuumed
> table has an LSN from around the last vacuum (or even before), it should be
> frozen, but will appear to be recent in RedoRecPtr based heuristics?
>
>
> Perhaps we can mix both approaches. We can use the LSN and time of the last
> vacuum to establish an LSN->time mapping that's reasonably accurate for a
> relation. For infrequently vacuumed tables we can use the time between
> checkpoints to establish a *more aggressive* cutoff for freezing then what a
> percent-of-time-since-last-vacuum appach would provide. If e.g. a table gets
> vacuumed every 100 hours and checkpoint timeout is 1 hour, no realistic
> percent-of-time-since-last-vacuum setting will allow freezing, as all dirty
> pages will be too new. To allow freezing a decent proportion of those, we
> could allow freezing pages that lived longer than ~20%
> time-between-recent-checkpoints.

One big sticking point for me (brought up elsewhere in this thread, but,
AFAICT never resolved) is that it seems like the fact that we mark pages
all-visible even when not freezing them means that no matter what
heuristic we use, we won't have the opportunity to freeze the pages we
most want to freeze.

Getting to the specific proposal here -- having two+ heuristics and
using them based on table characteristics:

Take the append-only case. Let's say that we had a way to determine if
an insert-only table should use the vacuum LSN heuristic or older than X
checkpoints heuristic. Given the default
autovacuum_vacuum_insert_threshold, every 1000 tuples inserted,
autovacuum will vacuum the table. If you insert to the table often, then
it is a frequently vacuumed table and, if I'm understanding the proposal
correctly, you would want to use a vacuum LSN-based threshold to
determine which pages are old enough to freeze. Using only the
page-older-than-X%-of-LSNs-since-last-vacuum heuristic and assuming no
concurrent workloads, each autovacuum will leave X% of the pages
unfrozen. However, those pages will likely be marked all visible. That
means that the next non-aggressive autovacuum will not even scan those
pages (assuming that run of pages exceeds the skip pages threshold). So
some chunk of pages will get left behind on every autovacuum.

On the other hand, let's say that the table is not frequently inserted
to, so we consider it an infrequently updated table and want to use the
checkpoint-based heuristic. If the page hasn't been modified for
multiple checkpoints, then the next modification will require an FPI.
Because there aren't dead tuples on the page, we can't expect pruning to
emit the FPI. So, freezing the page will always emit an FPI in this
case.

It seems like the ideal freeze pattern for an insert-only table would be
to freeze as soon as the page is full before any checkpoints which could
force you to emit an FPI.

- Melanie