Re: effective_multixact_freeze_max_age issue - Mailing list pgsql-hackers
From | Peter Geoghegan |
---|---|
Subject | Re: effective_multixact_freeze_max_age issue |
Date | |
Msg-id | CAH2-Wz=fMBK89WAAHZRwwU5-teJN2xvjve24HTY4W_Yn+wX6GQ@mail.gmail.com Whole thread Raw |
In response to | Re: effective_multixact_freeze_max_age issue ("Anton A. Melnikov" <aamelnikov@inbox.ru>) |
Responses |
Re: effective_multixact_freeze_max_age issue
|
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
pgsql-hackers by date: