Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit cd5e822

Browse filesBrowse files
nmischnmisch
committed
Change XID and mxact limits to warn at 40M and stop at 3M.
We have edge-case bugs when assigning values in the last few dozen pages before the wrap limit. We may introduce similar bugs in the future. At default BLCKSZ, this makes such bugs unreachable outside of single-user mode. Also, when VACUUM began to consume mxacts, multiStopLimit did not change to compensate. pg_upgrade may fail on a cluster that was already printing "must be vacuumed" warnings. Follow the warning's instructions to clear the warning, then run pg_upgrade again. One can still, peacefully consume 98% of XIDs or mxacts, so DBAs need not change routine VACUUM settings. Discussion: https://postgr.es/m/20200621083513.GA3074645@rfd.leadboat.com
1 parent 9f96827 commit cd5e822
Copy full SHA for cd5e822

File tree

Expand file treeCollapse file tree

3 files changed

+29
-30
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+29
-30
lines changed

‎doc/src/sgml/maintenance.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/maintenance.sgml
+4-4Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -608,10 +608,10 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
608608
<para>
609609
If for some reason autovacuum fails to clear old XIDs from a table, the
610610
system will begin to emit warning messages like this when the database's
611-
oldest XIDs reach eleven million transactions from the wraparound point:
611+
oldest XIDs reach forty million transactions from the wraparound point:
612612

613613
<programlisting>
614-
WARNING: database "mydb" must be vacuumed within 10985967 transactions
614+
WARNING: database "mydb" must be vacuumed within 39985967 transactions
615615
HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
616616
</programlisting>
617617

@@ -621,15 +621,15 @@ HINT: To avoid a database shutdown, execute a database-wide VACUUM in that data
621621
be able to advance the database's <structfield>datfrozenxid</structfield>.)
622622
If these warnings are
623623
ignored, the system will shut down and refuse to start any new
624-
transactions once there are fewer than 1 million transactions left
624+
transactions once there are fewer than three million transactions left
625625
until wraparound:
626626

627627
<programlisting>
628628
ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"
629629
HINT: Stop the postmaster and vacuum that database in single-user mode.
630630
</programlisting>
631631

632-
The 1-million-transaction safety margin exists to let the
632+
The three-million-transaction safety margin exists to let the
633633
administrator recover without data loss, by manually executing the
634634
required <command>VACUUM</command> commands. However, since the system will not
635635
execute commands once it has gone into the safety shutdown mode,

‎src/backend/access/transam/multixact.c

Copy file name to clipboardExpand all lines: src/backend/access/transam/multixact.c
+10-14Lines changed: 10 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -2217,28 +2217,24 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
22172217
multiWrapLimit += FirstMultiXactId;
22182218

22192219
/*
2220-
* We'll refuse to continue assigning MultiXactIds once we get within 100
2221-
* multi of data loss.
2222-
*
2223-
* Note: This differs from the magic number used in
2224-
* SetTransactionIdLimit() since vacuum itself will never generate new
2225-
* multis. XXX actually it does, if it needs to freeze old multis.
2220+
* We'll refuse to continue assigning MultiXactIds once we get within 3M
2221+
* multi of data loss. See SetTransactionIdLimit.
22262222
*/
2227-
multiStopLimit = multiWrapLimit - 100;
2223+
multiStopLimit = multiWrapLimit - 3000000;
22282224
if (multiStopLimit < FirstMultiXactId)
22292225
multiStopLimit -= FirstMultiXactId;
22302226

22312227
/*
2232-
* We'll start complaining loudly when we get within 10M multis of the
2233-
* stop point. This is kind of arbitrary, but if you let your gas gauge
2234-
* get down to 1% of full, would you be looking for the next gas station?
2235-
* We need to be fairly liberal about this number because there are lots
2236-
* of scenarios where most transactions are done by automatic clients that
2237-
* won't pay attention to warnings. (No, we're not gonna make this
2228+
* We'll start complaining loudly when we get within 40M multis of data
2229+
* loss. This is kind of arbitrary, but if you let your gas gauge get
2230+
* down to 2% of full, would you be looking for the next gas station? We
2231+
* need to be fairly liberal about this number because there are lots of
2232+
* scenarios where most transactions are done by automatic clients that
2233+
* won't pay attention to warnings. (No, we're not gonna make this
22382234
* configurable. If you know enough to configure it, you know enough to
22392235
* not get in this kind of trouble in the first place.)
22402236
*/
2241-
multiWarnLimit = multiStopLimit - 10000000;
2237+
multiWarnLimit = multiWrapLimit - 40000000;
22422238
if (multiWarnLimit < FirstMultiXactId)
22432239
multiWarnLimit -= FirstMultiXactId;
22442240

‎src/backend/access/transam/varsup.c

Copy file name to clipboardExpand all lines: src/backend/access/transam/varsup.c
+15-12Lines changed: 15 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -350,27 +350,30 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
350350

351351
/*
352352
* We'll refuse to continue assigning XIDs in interactive mode once we get
353-
* within 1M transactions of data loss. This leaves lots of room for the
353+
* within 3M transactions of data loss. This leaves lots of room for the
354354
* DBA to fool around fixing things in a standalone backend, while not
355355
* being significant compared to total XID space. (Note that since
356356
* vacuuming requires one transaction per table cleaned, we had better be
357-
* sure there's lots of XIDs left...)
357+
* sure there's lots of XIDs left...) Also, at default BLCKSZ, this
358+
* leaves two completely-idle segments. In the event of edge-case bugs
359+
* involving page or segment arithmetic, idle segments render the bugs
360+
* unreachable outside of single-user mode.
358361
*/
359-
xidStopLimit = xidWrapLimit - 1000000;
362+
xidStopLimit = xidWrapLimit - 3000000;
360363
if (xidStopLimit < FirstNormalTransactionId)
361364
xidStopLimit -= FirstNormalTransactionId;
362365

363366
/*
364-
* We'll start complaining loudly when we get within 10M transactions of
365-
* the stop point. This is kind of arbitrary, but if you let your gas
366-
* gauge get down to 1% of full, would you be looking for the next gas
367-
* station? We need to be fairly liberal about this number because there
368-
* are lots of scenarios where most transactions are done by automatic
369-
* clients that won't pay attention to warnings. (No, we're not gonna make
370-
* this configurable. If you know enough to configure it, you know enough
371-
* to not get in this kind of trouble in the first place.)
367+
* We'll start complaining loudly when we get within 40M transactions of
368+
* data loss. This is kind of arbitrary, but if you let your gas gauge
369+
* get down to 2% of full, would you be looking for the next gas station?
370+
* We need to be fairly liberal about this number because there are lots
371+
* of scenarios where most transactions are done by automatic clients that
372+
* won't pay attention to warnings. (No, we're not gonna make this
373+
* configurable. If you know enough to configure it, you know enough to
374+
* not get in this kind of trouble in the first place.)
372375
*/
373-
xidWarnLimit = xidStopLimit - 10000000;
376+
xidWarnLimit = xidWrapLimit - 40000000;
374377
if (xidWarnLimit < FirstNormalTransactionId)
375378
xidWarnLimit -= FirstNormalTransactionId;
376379

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.