Decoupling antiwraparound autovacuum from special rules around auto cancellation
I think that we should decouple the PROC_VACUUM_FOR_WRAPAROUND
autocancellation behavior in ProcSleep() from antiwraparound
autovacuum itself. In other words I think that it should be possible
to cancel an autovacuum that happens to be an antiwraparound
autovacuum, just as if were any other autovacuum -- because it usually
is no different in any real practical sense. Or at least it shouldn't
be seen as fundamentally different to other autovacuums at first,
before relfrozenxid attains an appreciably greater age (definition of
"appreciably greater" is TBD).
Why should the PROC_VACUUM_FOR_WRAPAROUND behavior happen on *exactly*
the same timeline as the one used to launch an antiwraparound
autovacuum, though? There is no inherent reason why we have to do both
things at exactly the same XID-age-wise time. But there is reason to
think that doing so could make matters worse rather than better [1]https://www.tritondatacenter.com/blog/manta-postmortem-7-27-2015.
More generally I think that it'll be useful to perform "aggressive
behaviors" on their own timeline, with no two distinct aggressive
behaviors applied at exactly the same time. In general we ought to
give a less aggressive approach some room to succeed before escalating
to a more aggressive approach -- we should see if a less aggressive
approach will work on its own. The failsafe is the most aggressive
intervention of all. The PROC_VACUUM_FOR_WRAPAROUND behavior is almost
as aggressive, and should happen sooner. Antiwraparound autovacuum
itself (which is really a separate thing to
PROC_VACUUM_FOR_WRAPAROUND) is less aggressive still. Then you have
things like the cutoffs in vacuumlazy.c that control things like
freezing.
In short, having an "escalatory" approach that applies each behavior
at different times. The exact timelines we'd want are of course
debatable, but the value of having multiple distinct timelines (one
per aggressive behavior) is far less debatable. We should give
problems a chance to "resolve themselves", at least up to a point.
The latest version of my in progress VACUUM patch series [2]/messages/by-id/CAH2-WzkU42GzrsHhL2BiC1QMhaVGmVdb5HR0_qczz0Gu2aSn=A@mail.gmail.com -- Peter Geoghegan
completely removes the concept of aggressive VACUUM as a discrete mode
of operation inside vacuumlazy.c. Every existing "aggressive-ish
behavior" will be retained in some form or other, but they'll be
applied on separate timelines, in proportion to the problem at hand.
For example, we'll have a separate XID cutoff for waiting for a
cleanup lock the hard way -- we will no longer use FreezeLimit for
that, since that doesn't give freezing a chance to happen in the next
VACUUM. The same VACUUM operation that is the first one that is
capable of freezing should ideally not *also* be the first one that
has to wait for a cleanup lock. We should be willing to put off
waiting for a cleanup lock for much longer than we're willing to put
off freezing. Reusing the same cutoff just makes life harder.
Clearly the idea of decoupling the PROC_VACUUM_FOR_WRAPAROUND behavior
from antiwraparound autovacuum is conceptually related to my patch
series, but it can be treated as separate work. That's why I'm
starting another thread now.
There is another idea in that patch series that also seems worth
mentioning as relevant (but not essential) to this discussion on this
thread: it would be better if antiwraparound autovacuum was simply
another way to launch an autovacuum, which isn't fundamentally
different to any other. I believe that users will find this conceptual
model a lot easier, especially in a world where antiwraparound
autovacuums naturally became rare (which is the world that the big
patch series seeks to bring about). It'll make antiwraparound
autovacuum "the threshold of last resort", only needed when
conventional tuple-based thresholds don't trigger at all for an
extended period of time (e.g., for static tables).
Perhaps it won't be trivial to fix autovacuum.c in the way I have in
mind (which is to split PROC_VACUUM_FOR_WRAPAROUND into two flags that
serve two separate purposes). I haven't considered if we're
accidentally relying on the coupling to avoid confusion within
autovacuum.c. That doesn't seem important right now, though.
[1]: https://www.tritondatacenter.com/blog/manta-postmortem-7-27-2015
[2]: /messages/by-id/CAH2-WzkU42GzrsHhL2BiC1QMhaVGmVdb5HR0_qczz0Gu2aSn=A@mail.gmail.com -- Peter Geoghegan
--
Peter Geoghegan
On Wed, 2022-10-19 at 14:58 -0700, Peter Geoghegan wrote:
Why should the PROC_VACUUM_FOR_WRAPAROUND behavior happen on
*exactly*
the same timeline as the one used to launch an antiwraparound
autovacuum, though?
The terminology is getting slightly confusing here: by
"antiwraparound", you mean that it's not skipping unfrozen pages, and
therefore is able to advance relfrozenxid. Whereas the
PROC_VACUUM_FOR_WRAPAROUND is the same thing, except done with greater
urgency because wraparound is imminent. Right?
There is no inherent reason why we have to do both
things at exactly the same XID-age-wise time. But there is reason to
think that doing so could make matters worse rather than better [1].
Can you explain?
Regards,
Jeff Davis
On Thu, Oct 20, 2022 at 11:09 AM Jeff Davis <pgsql@j-davis.com> wrote:
The terminology is getting slightly confusing here: by
"antiwraparound", you mean that it's not skipping unfrozen pages, and
therefore is able to advance relfrozenxid. Whereas the
PROC_VACUUM_FOR_WRAPAROUND is the same thing, except done with greater
urgency because wraparound is imminent. Right?
Not really.
I started this thread to discuss a behavior in autovacuum.c and proc.c
(the autocancellation behavior), which is, strictly speaking, not
related to the current vacuumlazy.c behavior we call aggressive mode
VACUUM. Various hackers have in the past described antiwraparound
autovacuum as "implying aggressive", which makes sense; what's the
point in doing an antiwraparound autovacuum that can almost never
advance relfrozenxid?
It is nevertheless true that antiwraparound autovacuum is an
independent behavior to aggressive VACUUM. The former is an autovacuum
thing, and the latter is a VACUUM thing. That's just how it works,
mechanically.
If this division seems artificial or pedantic to you, then consider
the fact that you can quite easily get a non-aggressive antiwraparound
autovacuum by using the storage option called
autovacuum_freeze_max_age (instead of the GUC):
/messages/by-id/CAH2-Wz=DJAokY_GhKJchgpa8k9t_H_OVOvfPEn97jGNr9W=deg@mail.gmail.com
This is even a case where we'll output a distinct description in the
server log when autovacuum logging is enabled and gets triggered. So
while there may be no point in an antiwraparound autovacuum that is
non-aggressive, that doesn't stop them from happening. Regardless of
whether or not that's an intended behavior, that's just how the
mechanism has been constructed.
There is no inherent reason why we have to do both
things at exactly the same XID-age-wise time. But there is reason to
think that doing so could make matters worse rather than better [1].Can you explain?
Why should the special autocancellation behavior for antiwraparound
autovacuums kick in at exactly the same point that we first launch an
antiwraparound autovacuum? Maybe that aggressive intervention will be
needed, in the end, but why start there?
With my patch series, antiwraparound autovacuums still occur, but
they're confined to things like static tables -- things that are
pretty much edge cases. They still need to behave sensibly (i.e.
reliably advance relfrozenxid based on some principled approach), but
now they're more like "an autovacuum that happens because no other
condition triggered an autovacuum". To some degree this is already the
case, but I'd like to be more deliberate about it.
Leaving my patch series aside, I still don't think that it makes sense
to make it impossible to auto-cancel antiwraparound autovacuums,
across the board, regardless of the underlying table age. We still
need something like that, but why not give a still-cancellable
autovacuum worker a chance to resolve the problem? Why take a risk of
causing much bigger problems (e.g., blocking automated DDL that blocks
simple SELECT queries) before the point that that starts to look like
the lesser risk (compared to hitting xidStopLimit)?
--
Peter Geoghegan
On Thu, Oct 20, 2022 at 11:52 AM Peter Geoghegan <pg@bowt.ie> wrote:
Leaving my patch series aside, I still don't think that it makes sense
to make it impossible to auto-cancel antiwraparound autovacuums,
across the board, regardless of the underlying table age.
One small thought on the presentation/docs side of this: maybe it
would be better to invent a new kind of autovacuum that has the same
purpose as antiwraparound autovacuum, but goes by a different name,
and doesn't have the special behavior around cancellations. We
wouldn't have to change anything about the behavior of antiwraparound
autovacuum once we reached the point of needing one.
Maybe we wouldn't even need to invent a new user-visible name for this
other kind of autovacuum. While even this so-called "new kind of
autovacuum" will be rare once my main patch series gets in, it'll
still be a totally normal occurrence. Whereas antiwraparound
autovacuums are sometimes described as an emergency mechanism.
That way we wouldn't be fighting against the widely held perception
that antiwraparound autovacuums are scary. In fact that reputation
would be fully deserved, for the first time. There are lots of
problems with the idea that antiwraparound autovacuum is kind of an
emergency thing right now, but this would make things fit the
perception, "fixing" the perception. Antiwraparound autovacuums would
become far far rarer under this scheme, but when they did happen
they'd be clear cause for concern. A useful signal for users, who
should ideally aim to never see *any* antiwraparound autovacuums.
--
Peter Geoghegan
On Fri, 2022-10-21 at 17:39 -0700, Peter Geoghegan wrote:
One small thought on the presentation/docs side of this: maybe it
would be better to invent a new kind of autovacuum
It's possible this would be easier for users to understand: one process
that does cleanup work over time in a way that minimizes interference;
and another process that activates in more urgent situations (perhaps
due to misconfiguration of the first process).
But we should be careful that we don't end up with more confusion. For
something like that to work, we'd probably want the second process to
not be configurable at all, and we'd want it to be issuing WARNINGs
pointing to what might be misconfigured, and otherwise just be
invisible.
That way we wouldn't be fighting against the widely held perception
that antiwraparound autovacuums are scary.
There's certainly a terminology problem there. Just to brainstorm on
some new names, we might want to call it something like "xid
reclamation" or "xid horizon advancement".
When it starts to run out, we can use words like "wraparound" or
"exhaustion".
--
Jeff Davis
PostgreSQL Contributor Team - AWS
On Sun, Oct 23, 2022 at 9:32 PM Jeff Davis <pgsql@j-davis.com> wrote:
It's possible this would be easier for users to understand: one process
that does cleanup work over time in a way that minimizes interference;
and another process that activates in more urgent situations (perhaps
due to misconfiguration of the first process).
I think that the new "early" version of antiwraparound autovacuum
(that can still be autocancelled) would simply be called autovacuum.
It wouldn't appear as "autovacuum to prevent wraparound" in places
like pg_stat_activity. For the most part users wouldn't have to care
about the difference between these autovacuums and traditional
non-antiwraparound autovacuums. They really would be exactly the same
thing, so it would make sense if users typically noticed no difference
whatsoever (at least in contexts like pg_stat_activity).
But we should be careful that we don't end up with more confusion. For
something like that to work, we'd probably want the second process to
not be configurable at all, and we'd want it to be issuing WARNINGs
pointing to what might be misconfigured, and otherwise just be
invisible.
There should be some simple scheme for determining when an
antiwraparound autovacuum (non-cancellable autovacuum to advance
relfrozenxid/relminmxid) should run (applied by the autovacuum.c
scheduling logic). Something like "table has attained an age that's
now 2x autovacuum_freeze_max_age, or 1/2 of vacuum_failsafe_age,
whichever is less".
The really important thing is giving a regular/early autocancellable
autovacuum triggered by age(relfrozenxid) *some* opportunity to run. I
strongly suspect that the exact details won't matter too much,
provided we manage to launch at least one such autovacuum before
escalating to traditional antiwraparound autovacuum (which cannot be
autocancelled). Even if regular/early autovacuum had just one
opportunity to run to completion, we'd already be much better off. The
hazards from blocking automated DDL in a way that leads to a very
disruptive traffic jam (like in the Joyent Manta postmortem) would go
way down.
That way we wouldn't be fighting against the widely held perception
that antiwraparound autovacuums are scary.There's certainly a terminology problem there. Just to brainstorm on
some new names, we might want to call it something like "xid
reclamation" or "xid horizon advancement".
I think that we should simply call it autovacuum. Under this scheme,
antiwraparound autovacuum would be a qualitatively different kind of
operation to users (though not to vacuumlazy.c), because it would not
be autocancellable in the standard way. And because users should take
it as a signal that things aren't really working well (otherwise we
wouldn't have reached the point of requiring a scary antiwraparound
autovacuum in the first place). Right now antiwraparound autovacuums
are both an emergency thing (or at least described as such in one or
two areas of the source code), and a completely routine occurrence.
This is deeply confusing.
Separately, I plan on breaking out insert-triggered autovacuums from
traditional dead tuple triggered autovacuums [1]/messages/by-id/CAH2-WznEqmkmry8feuDK8XdpH37-4anyGF7a04bWXOc1GKd0Yg@mail.gmail.com -- Peter Geoghegan, which creates a need
to invent some kind of name to differentiate the new table age
triggering criteria from both insert-driven and dead tuple driven
autovacuums. These are all fundamentally the same operations with the
same urgency to users, though. We'd only need to describe the
*criteria* that *triggered* the autovacuum in our autovacuum log
report (actually we'd still report autovacuums aš antiwraparound
autovacuum in cases where that still happened, which won't be
presented as just another triggering criteria in the report).
[1]: /messages/by-id/CAH2-WznEqmkmry8feuDK8XdpH37-4anyGF7a04bWXOc1GKd0Yg@mail.gmail.com -- Peter Geoghegan
--
Peter Geoghegan
On Mon, 2022-10-24 at 07:25 -0700, Peter Geoghegan wrote:
The really important thing is giving a regular/early autocancellable
autovacuum triggered by age(relfrozenxid) *some* opportunity to run.
+1. That principle seems both reasonable from a system standpoint and
understandable to a user.
Even if regular/early autovacuum had just one
opportunity to run to completion, we'd already be much better off.
By "opportunity", you mean that, regardless of configuration, the
cancellable autovacuum would at least start; though it still might be
cancelled by DDL. Right?
These are all fundamentally the same operations with the
same urgency to users, though. We'd only need to describe the
*criteria* that *triggered* the autovacuum in our autovacuum log
report
Hmm... I'm worried that could be a bit confusing depending on how it's
done. Let's be clear that it was merely the triggering criteria and
doesn't necessarily represent the work that is being done.
There are enough cases that it would be good to start a document and
outline the end behavior that your patch series is designed to
accomplish. In other words, a before/after of the interesting cases.
--
Jeff Davis
PostgreSQL Contributor Team - AWS
On Mon, Oct 24, 2022 at 8:43 AM Jeff Davis <pgsql@j-davis.com> wrote:
Even if regular/early autovacuum had just one
opportunity to run to completion, we'd already be much better off.By "opportunity", you mean that, regardless of configuration, the
cancellable autovacuum would at least start; though it still might be
cancelled by DDL. Right?
Yes, exactly.
It might be difficult as a practical matter to make sure that we
*reliably* give autovacuum.c the opportunity to launch a "standard"
autovacuum tasked with advancing relfrozenxid (just after
autovacuum_freeze_max_age is first crossed) before the point that a
scary antiwraparound autovacuum is launched. So we might end up giving
it more XID slack than it's likely to ever need (say by only launching
a traditional antiwraparound autovacuum against tables that attain an
age that is twice the value of autovacuum_freeze_max_age). These are
all just details, though -- the important principle is that we try our
utmost to give the less disruptive strategy a chance to succeed before
concluding that it has failed, and then "escalating" to a traditional
antiwraparound autovacuum.
These are all fundamentally the same operations with the
same urgency to users, though. We'd only need to describe the
*criteria* that *triggered* the autovacuum in our autovacuum log
reportHmm... I'm worried that could be a bit confusing depending on how it's
done. Let's be clear that it was merely the triggering criteria and
doesn't necessarily represent the work that is being done.
Maybe it could be broken out into a separate "autovacuum triggered by:
" line, seen only in the autovacuum log instrumentation (and not in
the similar report output by a manual VACUUM VERBOSE). When we still
end up "escalating" to an antiwraparound autovacuum, the "triggered
by:" line would match whatever we'd show in the benign the
non-cancellable-but-must-advance-relfrozenxid autovacuum case. The
difference would be that we'd now be reporting on a different
operation entirely (not just a regular autovacuum, a scary
antiwraparound autovacuum).
(Again, even these distinctions wouldn't be meaningful to vacuumlazy.c
itself -- it would just need to handle the details around logging in a
way that gave users the right idea. There wouldn't be any special
discrete aggressive mode of operation anymore, assuming my big patch
set gets into Postgres 16 too.)
There are enough cases that it would be good to start a document and
outline the end behavior that your patch series is designed to
accomplish. In other words, a before/after of the interesting cases.
That's on my TODO list. Mostly it's an independent thing to this
(antiwraparound) autovacuum stuff, despite the fact that both projects
share the same underlying philosophy.
--
Peter Geoghegan
On Mon, Oct 24, 2022 at 9:00 AM Peter Geoghegan <pg@bowt.ie> wrote:
Maybe it could be broken out into a separate "autovacuum triggered by:
" line, seen only in the autovacuum log instrumentation (and not in
the similar report output by a manual VACUUM VERBOSE). When we still
end up "escalating" to an antiwraparound autovacuum, the "triggered
by:" line would match whatever we'd show in the benign the
non-cancellable-but-must-advance-relfrozenxid autovacuum case. The
difference would be that we'd now be reporting on a different
operation entirely (not just a regular autovacuum, a scary
antiwraparound autovacuum).
Attached WIP patch invents the idea of a regular autovacuum that is
tasked with advancing relfrozenxid -- which is really just another
trigger criteria, reported on in the server log in its autovacuum
reports. Of course we retain the idea of antiwraparound autovacuums.
The only difference is that they are triggered when table age has
advanced by twice the usual amount, which is presumably only possible
because a regular autovacuum couldn't start or couldn't complete in
time (most likely due to continually being auto-cancelled).
As I said before, I think that the most important thing is to give
regular autovacuuming a chance to succeed. The exact approach taken
has a relatively large amount of slack, but that probably isn't
needed. So the new antiwraparound cutoffs were chosen because they're
easy to understand and remember, which is fairly arbitrary.
Adding this to the upcoming CF.
--
Peter Geoghegan
Attachments:
v1-0001-Add-table-age-trigger-concept-to-autovacuum.patchapplication/octet-stream; name=v1-0001-Add-table-age-trigger-concept-to-autovacuum.patchDownload+126-40
On Fri, 2022-11-25 at 14:47 -0800, Peter Geoghegan wrote:
Attached WIP patch invents the idea of a regular autovacuum that is
tasked with advancing relfrozenxid -- which is really just another
trigger criteria, reported on in the server log in its autovacuum
reports. Of course we retain the idea of antiwraparound autovacuums.
The only difference is that they are triggered when table age has
advanced by twice the usual amount, which is presumably only possible
because a regular autovacuum couldn't start or couldn't complete in
time (most likely due to continually being auto-cancelled).As I said before, I think that the most important thing is to give
regular autovacuuming a chance to succeed. The exact approach taken
has a relatively large amount of slack, but that probably isn't
needed. So the new antiwraparound cutoffs were chosen because they're
easy to understand and remember, which is fairly arbitrary.
The target is a table that receives no DML at all, right?
I think that is a good idea.
Wouldn't it make sense to trigger that at *half* "autovacuum_freeze_max_age"?
Yours,
Laurenz Albe
On Sat, Nov 26, 2022 at 9:58 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
The target is a table that receives no DML at all, right?
Sort of, but not really. The target is a table that doesn't get
vacuumed for any other reason -- I don't make any claims beyond that
one.
It seems a little too optimistic to suppose that such a table really
didn't need any vacuuming to deal with bloat just because autovacuum.c
didn't seem to think that it did.
I think that is a good idea.
Wouldn't it make sense to trigger that at *half* "autovacuum_freeze_max_age"?
That would be equivalent to what I've done here, provided you also
double the autovacuum_freeze_max_age setting. I did it this way
because I believe that it has fewer problems. The approach I took
makes the general perception that antiwraparound autovacuum are a
scary thing (really just needed for emergencies) become true, for the
first time.
We should expect to see very few antiwraparound autovacuums with the
patch, but when we do see even one it'll be after a less aggressive
approach was given the opportunity to succeed, but (for whatever
reason) failed. Just seeing any antiwraparound autovacuums will become
a useful signal of something being amiss in a way that it just isn't
at the moment. The docs can be updated to talk about antiwraparound
autovacuum as a thing that you should encounter approximately never.
This is possible even though the patch isn't invasive at all.
--
Peter Geoghegan
On Sat, 2022-11-26 at 11:00 -0800, Peter Geoghegan wrote:
I think that is a good idea.
Wouldn't it make sense to trigger that at *half* "autovacuum_freeze_max_age"?That would be equivalent to what I've done here, provided you also
double the autovacuum_freeze_max_age setting.
That's exactly what I was trying to debate. Wouldn't it make sense to
trigger VACUUM earlier so that it has a chance of being less heavy?
On the other hand, if there are not sufficiently many modifications
on the table to trigger autovacuum, perhaps it doesn't matter in many
cases.
I did it this way
because I believe that it has fewer problems. The approach I took
makes the general perception that antiwraparound autovacuum are a
scary thing (really just needed for emergencies) become true, for the
first time.We should expect to see very few antiwraparound autovacuums with the
patch, but when we do see even one it'll be after a less aggressive
approach was given the opportunity to succeed, but (for whatever
reason) failed.
Is that really so much less aggressive? Will that autovacuum run want
to process all pages that are not all-frozen? If not, it probably won't
do much good. If yes, it will be just as heavy as an anti-wraparound
autovacuum (except that it won't block other sessions).
Just seeing any antiwraparound autovacuums will become
a useful signal of something being amiss in a way that it just isn't
at the moment. The docs can be updated to talk about antiwraparound
autovacuum as a thing that you should encounter approximately never.
This is possible even though the patch isn't invasive at all.
True. On the other hand, it might happen that after this, people start
worrying about normal autovacuum runs because they occasionally experience
a table age autovacuum that is much heavier than the other ones. And
they can no longer tell the reason, because it doesn't show up anywhere.
Yours,
Laurenz Albe
On Sun, Nov 27, 2022 at 8:54 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
That's exactly what I was trying to debate. Wouldn't it make sense to
trigger VACUUM earlier so that it has a chance of being less heavy?
On the other hand, if there are not sufficiently many modifications
on the table to trigger autovacuum, perhaps it doesn't matter in many
cases.
Maybe. There is a deeper problem here, though: table age is a really
terrible proxy for whether or not it's appropriate for VACUUM to
freeze preexisting all-visible pages. It's not obvious that half
autovacuum_freeze_max_age is much better than
autovacuum_freeze_max_age if your concern is avoiding getting too far
into debt on freezing. Afterall, this is debt that must be paid back
by freezing some number of physical heap pages, which in general has
approximately zero relationship with table age (we need physical units
for this, not logical units).
This is a long standing problem that I hope and expect will be fixed
in 16, by my ongoing work to completely remove the concept of
aggressive mode VACUUM:
https://commitfest.postgresql.org/40/3843/
This makes VACUUM care about both table age and the number of unfrozen
heap pages (mostly the latter). It weighs everything at the start of
each VACUUM, and decides on how it must advance relfrozenxid based on
the conditions in the table and the picture over time. Note that
performance stability is the main goal; we will not just keep
accumulating unfrozen pages for no good reason. All of the behaviors
previously associated with aggressive mode are retained, but are
individually applied on a timeline that is attuned to the needs of the
table (we can still wait for a cleanup lock, but that happens much
later than the point that the same page first becomes eligible for
freezing, not at exactly the same time).
In short, "aggressiveness" becomes a continuous thing, rather than a
discrete mode of operation, improving performance stability. We go
back to having only one kind of lazy vacuum, which is how things
worked prior to the introduction of the visibility map. (We did have
antiwraparound autovacuums in 8.3, but we did not have
aggressive/scan_all VACUUMs at the time.)
Is that really so much less aggressive? Will that autovacuum run want
to process all pages that are not all-frozen? If not, it probably won't
do much good. If yes, it will be just as heavy as an anti-wraparound
autovacuum (except that it won't block other sessions).
Even if we assume that my much bigger patch set won't make it into 16,
it'll probably still be a good idea to do this in 16. I admit that I
haven't really given that question enough thought to be sure of that,
though. Naturally my goal is to get everything in. Hopefully I'll
never have to make that call.
It is definitely true that this patch is "the autovacuum side" of the
work from the other much larger patchset (which handles "the VACUUM
side" of things). This antiwraparound patch should probably be
considered in that context, even though it's theoretically independent
work. It just worked out that way.
True. On the other hand, it might happen that after this, people start
worrying about normal autovacuum runs because they occasionally experience
a table age autovacuum that is much heavier than the other ones. And
they can no longer tell the reason, because it doesn't show up anywhere.
But you can tell the reason, just by looking at the autovacuum log
reports. The only thing you can't do is see "(to prevent wraparound)"
in pg_stat_activity. That (and the autocancellation behavioral change)
are the only differences.
The big picture is that users really will have no good reason to care
very much about autovacuums that were triggered to advance
relfrozenxid (at least in the common case where we haven't needed to
make them antiwraparound autovacuums). They could almost (though not
quite) now be explained as "an autovacuum that takes place because
it's been a while since we did an autovacuum to deal with bloat and/or
tuple inserts". That will at least be reasonable if you assume all of
the patches get in.
--
Peter Geoghegan
On Fri, Nov 25, 2022 at 2:47 PM Peter Geoghegan <pg@bowt.ie> wrote:
Attached WIP patch invents the idea of a regular autovacuum that is
tasked with advancing relfrozenxid -- which is really just another
trigger criteria, reported on in the server log in its autovacuum
reports.
Attached is v2, which is just to fix bitrot. Well, mostly. I did make
one functional change in v2: the autovacuum server log reports now
separately report on table XID age and table MultiXactId age, each as
its own distinct triggering condition.
I've heard informal reports that the difference between antiwraparound
autovacuums triggered by table XID age versus table MXID age can
matter a great deal. It isn't difficult to break out that detail
anyway, so even if the distinction isn't interesting all that often we
might as well surface it to users.
I still haven't made a start on the docs for this. I'm still not sure
how much work I should do on the docs in the scope of this project
versus my project that deals with related issues in VACUUM itself. The
existing material from the "Routine Vacuuming" docs has lots of
problems, and figuring out how to approach fixing those problems seems
kind of daunting right now.
--
Peter Geoghegan
Attachments:
v2-0001-Add-table-age-trigger-concept-to-autovacuum.patchapplication/x-patch; name=v2-0001-Add-table-age-trigger-concept-to-autovacuum.patchDownload+145-48
On Thu, Dec 29, 2022 at 7:01 PM Peter Geoghegan <pg@bowt.ie> wrote:
Attached is v2, which is just to fix bitrot.
Attached is v3. We no longer apply vacuum_failsafe_age when
determining the cutoff for antiwraparound autovacuuming -- the new
approach is a bit simpler.
This is a fairly small change overall. Now any "table age driven"
autovacuum will also be antiwraparound when its
relfrozenxid/relminmxid attains an age that's either double the
relevant setting (either autovacuum_freeze_max_age or
effective_multixact_freeze_max_age), or 1 billion XIDs/MXIDs --
whichever is less.
That makes it completely impossible to disable antiwraparound
protections (the special antiwrap autocancellation behavior) for
table-age-driven autovacuums once table age exceeds 1 billion
XIDs/MXIDs. It's still possible to increase autovacuum_freeze_max_age
to well over a billion, of course. It just won't be possible to do
that while also avoiding the no-auto-cancellation behavior for those
autovacuums that are triggered due to table age crossing the
autovacuum_freeze_max_age/effective_multixact_freeze_max_age
threshold.
--
Peter Geoghegan
Attachments:
v3-0001-Add-table-age-trigger-concept-to-autovacuum.patchapplication/x-patch; name=v3-0001-Add-table-age-trigger-concept-to-autovacuum.patchDownload+197-76
Hi,
On 2023-01-08 17:49:20 -0800, Peter Geoghegan wrote:
Teach autovacuum.c to launch "table age" autovacuums at the same point
that it previously triggered antiwraparound autovacuums. Antiwraparound
autovacuums are retained, but are only used as a true option of last
resort, when regular autovacuum has presumably tried and failed to
advance relfrozenxid (likely because the auto-cancel behavior kept
cancelling regular autovacuums triggered based on table age).
I've also seen the inverse, with recent versions of postgres: Autovacuum can
only ever make progress if it's an anti-wraparound vacuum, because it'll
always get cancelled otherwise. I'm worried that substantially increasing the
time until an anti-wraparound autovacuum happens will lead to more users
running into out-of-xid shutdowns.
I don't think it's safe to just increase the time at which anti-wrap vacuums
happen to a hardcoded 1 billion.
I'm also doubtful that it's ok to just make all autovacuums on relations with
an age > 1 billion anti-wraparound ones. For people that use a large
autovacuum_freeze_max_age that will be a rude awakening.
I am all in favor for adding logic to trigger autovacuum based on the table
age, without needing to reach autovacuum_freeze_max_age. It never made sense
to me that we get to the "emergency mode" in entirely normal operation. But
I'm not in favor of just entirely reinterpreting existing GUCs and adding
important thresholds as hardcoded numbers.
Greetings,
Andres Freund
On Mon, Jan 9, 2023 at 5:22 PM Andres Freund <andres@anarazel.de> wrote:
I've also seen the inverse, with recent versions of postgres: Autovacuum can
only ever make progress if it's an anti-wraparound vacuum, because it'll
always get cancelled otherwise. I'm worried that substantially increasing the
time until an anti-wraparound autovacuum happens will lead to more users
running into out-of-xid shutdowns.I don't think it's safe to just increase the time at which anti-wrap vacuums
happen to a hardcoded 1 billion.
That's not what the patch does. It doubles the time that the anti-wrap
no-autocancellation behaviors kick in, up to a maximum of 1 billion
XIDs/MXIDs. So it goes from autovacuum_freeze_max_age to
autovacuum_freeze_max_age x 2, without changing the basic fact that we
initially launch autovacuums that advance relfrozenxid/relminmxid when
the autovacuum_freeze_max_age threshold is first crossed.
These heuristics are totally negotiable -- and likely should be
thought out in more detail. It's likely that most of the benefit of
the patch comes from simply trying to advance relfrozenxid without the
special auto-cancellation behavior one single time. The main problem
right now is that the threshold that launches most antiwraparound
autovacuums is exactly the same as the threshold that activates the
auto-cancellation protections. Even doing the latter very slightly
later than the former could easily make things much better, while
adding essentially no risk of the kind you're concerned about.
I'm also doubtful that it's ok to just make all autovacuums on relations with
an age > 1 billion anti-wraparound ones. For people that use a large
autovacuum_freeze_max_age that will be a rude awakening.
Actually, users that have autovacuum_freeze_max_age set to over 1
billion will get exactly the same behavior as before (except that the
instrumentation of autovacuum will be better). It'll be identical.
If you set autovacuum_freeze_max_age to 2 billion, and a "standard"
autovacuum is launched on a table whose relfrozenxid age is 1.5
billion, it'll just be a regular dead tuples/inserted tuples
autovacuum, with the same old familiar locking characteristics as
today.
--
Peter Geoghegan
On Mon, Jan 9, 2023 at 8:40 PM Peter Geoghegan <pg@bowt.ie> wrote:
That's not what the patch does. It doubles the time that the anti-wrap
no-autocancellation behaviors kick in, up to a maximum of 1 billion
XIDs/MXIDs. So it goes from autovacuum_freeze_max_age to
autovacuum_freeze_max_age x 2, without changing the basic fact that we
initially launch autovacuums that advance relfrozenxid/relminmxid when
the autovacuum_freeze_max_age threshold is first crossed.
I'm skeptical about this kind of approach.
I do agree that it's good to slowly increase the aggressiveness of
VACUUM as we get further behind, rather than having big behavior
changes all at once, but I think that should happen by smoothly
varying various parameters rather than by making discrete behavior
changes at a whole bunch of different times. For instance, when VACUUM
goes into emergency mode, it stops respecting the vacuum delay. I
think that's great, but it happens all at once, and maybe it would be
better if it didn't. We could consider gradually ramping the vacuum
delay from 100% down to 0% instead of having it happen all at once.
Maybe that's not the right idea, I don't know, and a naive
implementation might be worse than nothing, but I think it has some
chance of being worth consideration.
But what the kind of change you're proposing here does is create
another threshold where the behavior changes suddenly, and I think
that's challenging from the point of view of understanding the
behavior of the system. The behavior already changes when you hit
vacuum_freeze_min_age and then again when you hit
vacuum_freeze_table_age and then there's also
autoovacuum_freeze_max_age and xidWarnLimit and xidStopLimit and a few
others, and these setting all interact in pretty complex ways. The
more conditional logic we add to that, the harder it becomes to
understand what's actually happening. You see a system where
age(relfrozenxid) = 673m and you need a calculator and a spreadsheet
to figure out what the vacuum behavior is at that point. Honestly, I
think we already have a problem with the behaviors here being too
complex for normal human beings to understand them, and I think that
the kinds of changes you are proposing here could make that quite a
bit worse.
Now, you might reply to the above by saying, well, some behaviors
can't vary continuously. vacuum_cost_limit can perhaps be phased out
gradually, but autocancellation seems like something that you must
either do, or not do. I would agree with that. But what I'm saying is
that we ought to favor having those kinds of behaviors all engage at
the same point rather than at different times. I'm not saying that
there can't ever be good reasons to separate out different behaviors
and have the engage at different times, but I think we will end up
better off if we minimize that sort of thing as much as we reasonably
can. In your opening email you write "Why should the
PROC_VACUUM_FOR_WRAPAROUND behavior happen on *exactly* the same
timeline as the one used to launch an antiwraparound autovacuum,
though?" and my answer is "because that's easier to understand and I
don't see that it has much of a downside."
I did take a look at the post-mortem to which you linked, but I am not
quite sure how that bears on the behavior change under discussion.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Thu, Jan 12, 2023 at 9:12 AM Robert Haas <robertmhaas@gmail.com> wrote:
I do agree that it's good to slowly increase the aggressiveness of
VACUUM as we get further behind, rather than having big behavior
changes all at once, but I think that should happen by smoothly
varying various parameters rather than by making discrete behavior
changes at a whole bunch of different times.
In general I tend to agree, but, as you go on to acknowledge yourself,
this particular behavior is inherently discrete. Either the
PROC_VACUUM_FOR_WRAPAROUND behavior is in effect, or it isn't.
In many important cases the only kind of autovacuum that ever runs
against a certain big table is antiwraparound autovacuum. And
therefore every autovacuum that runs against the table must
necessarily not be auto cancellable. These are the cases where we see
disastrous interactions with automated DDL, such as a TRUNCATE run by
a cron job (to stop those annoying antiwraparound autovacuums) -- a
heavyweight lock traffic jam that causes the application to lock up.
All that I really want to do here is give an autovacuum that *can* be
auto cancelled *some* non-zero chance to succeed with these kinds of
tables. TRUNCATE completes immediately, so the AEL is no big deal.
Except when it's blocked behind an antiwraparound autovacuum. That
kind of interaction is occasionally just disastrous. Even just the
tiniest bit of wiggle room could avoid it in most cases, possibly even
almost all cases.
Maybe that's not the right idea, I don't know, and a naive
implementation might be worse than nothing, but I think it has some
chance of being worth consideration.
It's a question of priorities. The failsafe isn't supposed to be used
(when it is it is a kind of a failure), and so presumably only kicks
in on very rare occasions, where nobody was paying attention anyway.
So far I've heard no complaints about this, but I've heard lots of
complaints about the antiwrap autocancellation behavior.
The behavior already changes when you hit
vacuum_freeze_min_age and then again when you hit
vacuum_freeze_table_age and then there's also
autoovacuum_freeze_max_age and xidWarnLimit and xidStopLimit and a few
others, and these setting all interact in pretty complex ways. The
more conditional logic we add to that, the harder it becomes to
understand what's actually happening.
In general I strongly agree. In fact that's a big part of what
motivates my ongoing work on VACUUM. The user experience is central.
As Andres pointed out, presenting antiwraparound autovacuums as kind
of an emergency thing but also somehow a routine thing is just
horribly confusing. I want to make them into an emergency thing in
every sense -- something that you as a user can reasonably expect to
never see (like the failsafe). But if you do see one, then that's a
useful signal of an underlying problem with contention, say from
automated DDL that pathologically cancels autovacuums again and again.
Now, you might reply to the above by saying, well, some behaviors
can't vary continuously. vacuum_cost_limit can perhaps be phased out
gradually, but autocancellation seems like something that you must
either do, or not do. I would agree with that. But what I'm saying is
that we ought to favor having those kinds of behaviors all engage at
the same point rather than at different times.
Right now aggressive VACUUMs do just about all freezing at the same
time, to the extent that many users seem to think that it's a totally
different thing with totally different responsibilities to regular
VACUUM. Doing everything at the same time like that causes huge
practical problems, and is very confusing.
I think that users will really appreciate having only one kind of
VACUUM/autovacuum (since the other patch gets rid of discrete
aggressive mode VACUUMs). I want "table age autovacuuming" (as I
propose to call it) come to be seen as not any different to any other
autovacuum, such as an "insert tuples" autovacuum or a "dead tuples"
autovacuum. The difference is only in how autovacuum.c triggers the
VACUUM, not in any runtime behavior. That's an important goal here.
I did take a look at the post-mortem to which you linked, but I am not
quite sure how that bears on the behavior change under discussion.
The post-mortem involved a single "DROP TRIGGER" that caused chaos
when it interacted with the auto cancellation behavior. It would
usually completely instantly, so the AEL wasn't actually disruptive,
but one day antiwraparound autovacuum made the cron job effectively
block all reads and writes for hours.
The similar outages I was called in to help with personally had either
an automated TRUNCATE or an automated CREATE INDEX. Had autovacuum
only been willing to yield once or twice, then it probably would have
been fine -- the situation probably would have worked itself out
naturally. That's the best outcome you can hope for.
--
Peter Geoghegan
On Thu, Jan 12, 2023 at 2:22 PM Peter Geoghegan <pg@bowt.ie> wrote:
All that I really want to do here is give an autovacuum that *can* be
auto cancelled *some* non-zero chance to succeed with these kinds of
tables. TRUNCATE completes immediately, so the AEL is no big deal.
Except when it's blocked behind an antiwraparound autovacuum. That
kind of interaction is occasionally just disastrous. Even just the
tiniest bit of wiggle room could avoid it in most cases, possibly even
almost all cases.
I doubt it. Wiggle room that's based on the XID threshold being
different for one behavior vs. another can easily fail to produce any
benefit, because there's no guarantee that the autovacuum launcher
will ever try to launch a worker against that table while the XID is
in the range where you'd get one behavior and not the other. I've
long thought that the fact that vacuum_freeze_table_age is documented
as capped at 0.95 * autovacuum_freeze_max_age is silly for just this
reason. The interval that you're proposing is much wider so the
chances of getting a benefit are greater, but supposing that it's
going to solve it in most cases seems like an exercise in unwarranted
optimism.
In fact, I would guess that in fact it will very rarely solve the
problem. Normally, the XID age of a table never reaches
autovacuum_freeze_max_age in the first place. If it does, there's some
reason. Maybe there's a really old open transaction or an abandon
replication slot or an unresolved 2PC transaction. Maybe the
autovacuum system is overloaded and no table is getting visited
regularly because the system just can't keep up. Or maybe there are
regular AELs being taken on the table at issue. If there's only an AEL
taken against a table once in blue moon, some autovacuum attempt ought
to succeed before we reach autovacuum_freeze_max_age. Flipping that
around, if we reach autovacuum_freeze_max_age without advancing
relfrozenxid, and an AEL shows up behind us in the lock queue, it's
really likely that the reason *why* we've reached
autovacuum_freeze_max_age is that this same thing has happened to
every previous autovacuum attempt and they all cancelled themselves.
If we cancel ourselves too, we're just postponing resolution of the
problem to some future point when we decide to stop cancelling
ourselves. That's not a win.
I think that users will really appreciate having only one kind of
VACUUM/autovacuum (since the other patch gets rid of discrete
aggressive mode VACUUMs). I want "table age autovacuuming" (as I
propose to call it) come to be seen as not any different to any other
autovacuum, such as an "insert tuples" autovacuum or a "dead tuples"
autovacuum. The difference is only in how autovacuum.c triggers the
VACUUM, not in any runtime behavior. That's an important goal here.
I don't agree with that goal. I think that having different kinds of
autovacuums with different, identifiable names and corresponding,
easily-identifiable behaviors is really important for troubleshooting.
Trying to remove those distinctions and make everything look the same
will not keep autovacuum from getting itself into trouble. It will
just make it harder to understand what's happening when it does.
--
Robert Haas
EDB: http://www.enterprisedb.com