Re: We should stop telling users to "vacuum that database in single-user mode"

On Wed, Mar 3, 2021 at 7:10 AM Hannu Krosing <hannuk(at)google(dot)com> wrote:
> On Wed, Mar 3, 2021 at 11:33 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > On Wed, 3 Mar 2021 at 21:44, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> >
> > I meant to ignore the cost limits if we're within a hundred million or
> > so of the stopLimit. Per what Hannu mentioned, there does not seem to
> > be a great need with current versions of PostgreSQL to restart in the
> > instance in single-user mode. VACUUM still works once we're beyond the
> > stopLimit. It's just commands that need to generate a new XID that'll
> > fail with the error message mentioned by Hannu.
>
> I am investigating a possibility of introducing a special "Restricted
> Maintenance
> Mode" to let admin mitigate after xidStopLimit, maybe for another 0.5M txids,
> by doing things like
>
> * dropping an index - to make vacuum faster
> * dropping a table - sometimes it is better to drop a table in order to get the
> production database functional again instead of waiting hours for the vacuum
> to finish.
> And then later restore it from backup or maybe access it from a read-only
> clone of the database via FDW.
> * drop a stale replication slot which is holding back vacuum
>

I've talked with a few people about modifying wraparound and xid
emergency vacuums to be more efficient, ie. run them without indexes,
and possibly some other options. That seems like low-hanging fruit if
not already a thing.

> To make sure that this will not accidentally just move xidStopLimit to 0.5M for
> users who run main workloads as a superuser (they do exists!) this mode should
> be restricted to
> * only superuser
> * only a subset of commands / functions
> * be heavily throttled to avoid running out of TXIDs, maybe 1-10 xids per second
> * maybe require also setting a GUC to be very explicit
>
> > > I agree with your other idea, that of kicking in a more aggressive
> > > autovacuum if it's not dealing with things fast enough. Maybe even on
> > > an incremental way - that is run with the default, then at another
> > > threshold drop them to half, and at yet another threshold drop them to
> > > 0. I agree that pretty much anything is better than forcing the user
> > > into single user mode.
> >
> > OK cool. I wondered if it should be reduced incrementally or just
> > switch off the cost limit completely once we're beyond
> > ShmemVariableCache->xidStopLimit.
>
> Abrupt change is something that is more likely to make the user/DBA notice
> that something is going on. I have even been thinking about deliberate
> throttling to make the user notice / pay attention.
>

I worry that we're walking down the path of trying to find "clever"
solutions in a situation where the variety of production environments
(and therefore the right way to handle this issue) is nearly endless.
That said... I think at the point we're talking about, subtly is not
an absolute requirement... if people were paying attention they'd have
noticed autovacuum for wrap-around running or warnings in the logs; at
some point you do need to be a bit in your face that there is a real
possibility of disaster around the corner.

> > If we did want it to be incremental
> > then if we had say ShmemVariableCache->xidFastVacLimit, which was
> > about 100 million xids before xidStopLimit, then the code could adjust
> > the sleep delay down by the percentage through we are from
> > xidFastVacLimit to xidStopLimit.
> >
> > However, if we want to keep adjusting the sleep delay then we need to
> > make that work for vacuums that are running already. We don't want to
> > call ReadNextTransactionId() too often, but maybe if we did it once
> > per 10 seconds worth of vacuum_delay_point()s. That way we'd never do
> > it for vacuums already going at full speed.
>
> There are already samples of this in code, for example the decision to
> force-start disabled autovacuum is considered after every 64k transactions.
>
> There is a related item in https://commitfest.postgresql.org/32/2983/ .
> When that gets done, we could drive the adjustments from autovacuum.c by
> adding the remaining XID range adjustment to existing worker delay adjust
> mechanisms in autovac_balance_cost() and signalling the autovacuum
> backend to run the adjustment every few seconds once we are in the danger
> zone.
>

That patch certainly looks interesting; many many times I've had to
have people kick off manual vacuums to use more i/o and kill the
wrap-around vacuum. Reading the discussion there, I wonder if we
should think about weighting the most urgent vacuum at the expense of
other potential autovacuums, although I feel like they often come in
bunches in these scenarios.

Robert Treat
https://xzilla.net