Horton

Thread

Commits

  1. Change XID and mxact limits to warn at 40M and stop at 3M.

  1. Raising stop and warn limits

    Noah Misch <noah@leadboat.com> — 2020-06-21T08:35:13Z

    In brief, I'm proposing to raise xidWrapLimit-xidStopLimit to 3M and
xidWrapLimit-xidWarnLimit to 40M.  Likewise for mxact counterparts.


PostgreSQL has three "stop limit" values beyond which only single-user mode
will assign new values of a certain counter:

- xidStopLimit protects pg_xact, pg_commit_ts, pg_subtrans, and pg_serial.
  SetTransactionIdLimit() withholds a million XIDs, and warnings start ten
  million before that.
- multiStopLimit protects pg_multixact/offsets.  SetMultiXactIdLimit()
  withholds 100 mxacts, and warnings start at ten million.
- offsetStopLimit protects pg_multixact/members.  SetOffsetVacuumLimit()
  withholds [1,2) SLRU segments thereof (50k-100k member XIDs).  No warning
  phase for this one.

Reasons to like a larger stop limit:

1. VACUUM, to advance a limit, may assign IDs subject to one of the limits.
   VACUUM formerly consumed XIDs, not mxacts.  It now consumes mxacts, not
   XIDs.  I think a DBA can suppress VACUUM's mxact consumption by stopping
   all transactions older than vacuum_freeze_min_age, including prepared
   transactions.

2. We currently have edge-case bugs when assigning values in the last few
   dozen pages before the wrap limit
   (https://postgr.es/m/20190214072623.GA1139206@rfd.leadboat.com and
   https://postgr.es/m/20200525070033.GA1591335@rfd.leadboat.com).  A higher
   stop limit could make this class of bug unreachable outside of single-user
   mode.  That's valuable against undiscovered bugs of this class.

3. Any bug in stop limit enforcement is less likely to have user impact.  For
   a live example, see the XXX comment that
   https://postgr.es/m/attachment/111084/slru-truncate-modulo-v3.patch adds to
   CheckPointPredicate().

Raising a stop limit prompts an examination of warn limits, which represent
the time separating the initial torrent of warnings from the service outage.
The current limits appeared in 2005; transaction rates have grown, while human
reaction times have not.  I like warnings starting when an SLRU is 98%
consumed (40M XIDs or mxacts remaining).  That doesn't change things enough to
make folks reconfigure VACUUM, and it buys back some of the grace period DBAs
had in 2005.  I considered 95-97%, but the max_val of
autovacuum_freeze_max_age would then start the warnings before the autovacuum.
While that wouldn't rule out a value lower than 98%, 98% felt fine anyhow.

For the new stop limits, I propose allowing 99.85% SLRU fullness (stop with 3M
XIDs or mxacts remaining).  If stopping this early will bother users, an
alternative is 3M for XIDs and 0.2M for others.  Either way leaves at least
two completely-idle segments for each SLRU, which I expect to mitigate present
and future edge-case bugs.

Changing this does threaten clusters that experience pg_upgrade when close to
a limit.  pg_upgrade can fail or, worse, yield a cluster that spews warnings
shortly after the upgrade.  I could implement countermeasures, but they would
take effect only when one upgrades a cluster having a 98%-full SLRU.  I
propose not to change pg_upgrade; some sites may find cause to do a
whole-cluster VACUUM before pg_upgrade.  Do you agree or disagree with that
choice?  I am attaching a patch (not for commit) that demonstrates the
pg_upgrade behavior that nearly-full-SLRU clusters would see.

Thanks,
nm

  2. Re: Raising stop and warn limits

    Noah Misch <noah@leadboat.com> — 2020-06-28T06:20:04Z

    On Sun, Jun 21, 2020 at 01:35:13AM -0700, Noah Misch wrote:
> In brief, I'm proposing to raise xidWrapLimit-xidStopLimit to 3M and
> xidWrapLimit-xidWarnLimit to 40M.  Likewise for mxact counterparts.

Here's the patch for it.

> 1. VACUUM, to advance a limit, may assign IDs subject to one of the limits.
>    VACUUM formerly consumed XIDs, not mxacts.  It now consumes mxacts, not
>    XIDs.

Correction: a lazy_truncate_heap() at wal_level!=minimal does assign an XID,
so XID consumption is impossible with "VACUUM (TRUNCATE false)" but possible
otherwise.  "VACUUM (ANALYZE)", which a DBA might do by reflex, also assigns
XIDs.  (These corrections do not affect $SUBJECT.)