Re: effective_multixact_freeze_max_age issue - Mailing list pgsql-hackers

On Tue, Oct 18, 2022 at 3:43 AM Anton A. Melnikov <aamelnikov@inbox.ru> wrote:
> It's interesting that prior to this commit, checks were made for freeze limits, but the error messages were talking
aboutoldestXmin and oldestMxact.
 

The problem really is that oldestXmin and oldestMxact are held back,
though. While that can ultimately result in older FreezeLimit and
MultiXactCutoff cutoffs in vacuumlazy.c, that's just one problem.
Usually not the worst problem.

The term "removable cutoff" is how VACUUM VERBOSE reports OldestXmin.
I think that it's good to use the same terminology here.

> Could you clarify this moment please? Would be very grateful.

While this WARNING is triggered when a threshold controlled by
autovacuum_freeze_max_age is crossed, it's not just a problem with
freezing. It's convenient to use autovacuum_freeze_max_age to
represent "a wildly excessive number of XIDs for OldestXmin to be held
back by, no matter what". In practice it is usually already a big
problem when OldestXmin is held back by far fewer XIDs than that, but
it's hard to reason about when exactly we need to consider that a
problem. However, we can at least be 100% sure of real problems when
OldestXmin age reaches autovacuum_freeze_max_age. There is no longer
any doubt that we need to warn the user, since antiwraparound
autovacuum cannot work as designed at that point. But the WARNING is
nevertheless not primarily (or not exclusively) about not being able
to freeze. It's also about not being able to remove bloat.

Freezing can be thought of as roughly the opposite process to removing
dead tuples deleted by now committed transactions. OldestXmin is the
cutoff both for removing dead tuples (which we want to get rid of
immediately), and freezing live all-visible tuples (which we want to
keep long term). FreezeLimit is usually 50 million XIDs before
OldestXmin (the freeze_min_age default), but that's just how we
implement lazy freezing, which is just an optimization.

> As variant may be split these checks for the freeze cuttoffs and the oldest xmins for clarity?
> The patch attached tries to do this.

I don't think that this is an improvement. For one thing the
FreezeLimit cutoff will have been held back (relative to nextXID-wise
table age) by more than the freeze_min_age setting for a long time
before this WARNING is hit -- so we're not going to show the WARNING
in most cases where freeze_min_age has been completely ignored (it
must be ignored in extreme cases because FreezeLimit must always be <=
OldestXmin). Also, the proposed new WARNING is only seen when we're
bound to also see the existing OldestXmin WARNING already. Why have 2
WARNINGs about exactly the same problem?

-- 
Peter Geoghegan