Re: effective_multixact_freeze_max_age issue

Hello!

On 18.10.2022 20:56, Peter Geoghegan wrote:

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

Thanks for the explanation! Firstly exactly this term confused me.
Sure, the same terminology makes all easier to understand.

>
>> 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.
>

That's clear. Thanks a lot!

>> 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?>

I didn't understand this moment.

If the FreezeLimit is always older than OldestXmin or equal to it according to:

> FreezeLimit is usually 50 million XIDs before
> OldestXmin (the freeze_min_age default),

can't there be a situation like this?

______________________________
| autovacuum_freeze_max_age |
past <_______|____________|_____________|________________|> future
FreezeLimit safeOldestXmin oldestXmin nextXID
|___________________________________________|
freeze_min_age

In that case the existing OldestXmin WARNING will not fire while the new one will.
As the FreezeLimit is only optimization it's likely not a warning but a notice message
before OldestXmin WARNING and possible real problems in the future.
Maybe it can be useful in a such kind?

With best wishes,

--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company