On Wed, Mar 3, 2021 at 7:10 AM Hannu Krosing <han...@google.com> wrote: > On Wed, Mar 3, 2021 at 11:33 AM David Rowley <dgrowle...@gmail.com> wrote: > > On Wed, 3 Mar 2021 at 21:44, Magnus Hagander <mag...@hagander.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