Skip to content

Commit 801c2dc

Browse files
alvherrealvherre
committed
Separate multixact freezing parameters from xid's
Previously we were piggybacking on transaction ID parameters to freeze multixacts; but since there isn't necessarily any relationship between rates of Xid and multixact consumption, this turns out not to be a good idea. Therefore, we now have multixact-specific freezing parameters: vacuum_multixact_freeze_min_age: when to remove multis as we come across them in vacuum (default to 5 million, i.e. early in comparison to Xid's default of 50 million) vacuum_multixact_freeze_table_age: when to force whole-table scans instead of scanning only the pages marked as not all visible in visibility map (default to 150 million, same as for Xids). Whichever of both which reaches the 150 million mark earlier will cause a whole-table scan. autovacuum_multixact_freeze_max_age: when for cause emergency, uninterruptible whole-table scans (default to 400 million, double as that for Xids). This means there shouldn't be more frequent emergency vacuuming than previously, unless multixacts are being used very rapidly. Backpatch to 9.3 where multixacts were made to persist enough to require freezing. To avoid an ABI break in 9.3, VacuumStmt has a couple of fields in an unnatural place, and StdRdOptions is split in two so that the newly added fields can go at the end. Patch by me, reviewed by Robert Haas, with additional input from Andres Freund and Tom Lane.
1 parent de4b655 commit 801c2dc

File tree

19 files changed

+379
-29
lines changed

19 files changed

+379
-29
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 69 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4892,6 +4892,33 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
48924892
</listitem>
48934893
</varlistentry>
48944894

4895+
<varlistentry id="guc-autovacuum-multixact-freeze-max-age" xreflabel="autovacuum_multixact_freeze_max_age">
4896+
<term><varname>autovacuum_multixact_freeze_max_age</varname> (<type>integer</type>)</term>
4897+
<indexterm>
4898+
<primary><varname>autovacuum_multixact_freeze_max_age</varname> configuration parameter</primary>
4899+
</indexterm>
4900+
<listitem>
4901+
<para>
4902+
Specifies the maximum age (in multixacts) that a table's
4903+
<structname>pg_class</>.<structfield>relminmxid</> field can
4904+
attain before a <command>VACUUM</> operation is forced to
4905+
prevent multixact ID wraparound within the table.
4906+
Note that the system will launch autovacuum processes to
4907+
prevent wraparound even when autovacuum is otherwise disabled.
4908+
</para>
4909+
4910+
<para>
4911+
Vacuuming multixacts also allows removal of old files from the
4912+
<filename>pg_multixact/members</> and <filename>pg_multixact/offsets</>
4913+
subdirectories, which is why the default is a relatively low
4914+
400 million multixacts.
4915+
This parameter can only be set at server start, but the setting
4916+
can be reduced for individual tables by changing storage parameters.
4917+
For more information see <xref linkend="vacuum-for-multixact-wraparound">.
4918+
</para>
4919+
</listitem>
4920+
</varlistentry>
4921+
48954922
<varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
48964923
<term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
48974924
<indexterm>
@@ -5300,7 +5327,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
53005327
<structname>pg_class</>.<structfield>relfrozenxid</> field has reached
53015328
the age specified by this setting. The default is 150 million
53025329
transactions. Although users can set this value anywhere from zero to
5303-
one billion, <command>VACUUM</> will silently limit the effective value
5330+
two billions, <command>VACUUM</> will silently limit the effective value
53045331
to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
53055332
periodical manual <command>VACUUM</> has a chance to run before an
53065333
anti-wraparound autovacuum is launched for the table. For more
@@ -5331,6 +5358,47 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
53315358
</listitem>
53325359
</varlistentry>
53335360

5361+
<varlistentry id="guc-vacuum-multixact-freeze-table-age" xreflabel="vacuum_multixact_freeze_table_age">
5362+
<term><varname>vacuum_multixact_freeze_table_age</varname> (<type>integer</type>)</term>
5363+
<indexterm>
5364+
<primary><varname>vacuum_multixact_freeze_table_age</> configuration parameter</primary>
5365+
</indexterm>
5366+
<listitem>
5367+
<para>
5368+
<command>VACUUM</> performs a whole-table scan if the table's
5369+
<structname>pg_class</>.<structfield>relminmxid</> field has reached
5370+
the age specified by this setting. The default is 150 million multixacts.
5371+
Although users can set this value anywhere from zero to two billions,
5372+
<command>VACUUM</> will silently limit the effective value to 95% of
5373+
<xref linkend="guc-autovacuum-multixact-freeze-max-age">, so that a
5374+
periodical manual <command>VACUUM</> has a chance to run before an
5375+
anti-wraparound is launched for the table.
5376+
For more information see <xref linkend="vacuum-for-multixact-wraparound">.
5377+
</para>
5378+
</listitem>
5379+
</varlistentry>
5380+
5381+
<varlistentry id="guc-vacuum-multixact-freeze-min-age" xreflabel="vacuum_multixact_freeze_min_age">
5382+
<term><varname>vacuum_multixact_freeze_min_age</varname> (<type>integer</type>)</term>
5383+
<indexterm>
5384+
<primary><varname>vacuum_multixact_freeze_min_age</> configuration parameter</primary>
5385+
</indexterm>
5386+
<listitem>
5387+
<para>
5388+
Specifies the cutoff age (in multixacts) that <command>VACUUM</>
5389+
should use to decide whether to replace multixact IDs with a newer
5390+
transaction ID or multixact ID while scanning a table. The default
5391+
is 5 million multixacts.
5392+
Although users can set this value anywhere from zero to one billion,
5393+
<command>VACUUM</> will silently limit the effective value to half
5394+
the value of <xref linkend="guc-autovacuum-multixact-freeze-max-age">,
5395+
so that there is not an unreasonably short time between forced
5396+
autovacuums.
5397+
For more information see <xref linkend="vacuum-for-multixact-wraparound">.
5398+
</para>
5399+
</listitem>
5400+
</varlistentry>
5401+
53345402
<varlistentry id="guc-bytea-output" xreflabel="bytea_output">
53355403
<term><varname>bytea_output</varname> (<type>enum</type>)</term>
53365404
<indexterm>

‎doc/src/sgml/maintenance.sgml

Lines changed: 55 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -108,7 +108,8 @@
108108

109109
<listitem>
110110
<simpara>To protect against loss of very old data due to
111-
<firstterm>transaction ID wraparound</>.</simpara>
111+
<firstterm>transaction ID wraparound</> or
112+
<firstterm>multixact ID wraparound</>.</simpara>
112113
</listitem>
113114
</orderedlist>
114115

@@ -379,6 +380,11 @@
379380
<secondary>wraparound</secondary>
380381
</indexterm>
381382

383+
<indexterm>
384+
<primary>wraparound</primary>
385+
<secondary>of transaction IDs</secondary>
386+
</indexterm>
387+
382388
<para>
383389
<productname>PostgreSQL</productname>'s MVCC transaction semantics
384390
depend on being able to compare transaction ID (<acronym>XID</>)
@@ -597,6 +603,54 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
597603
page for details about using single-user mode.
598604
</para>
599605

606+
<sect3 id="vacuum-for-multixact-wraparound">
607+
<title>Multixacts and Wraparound</title>
608+
609+
<indexterm>
610+
<primary>MultiXactId</primary>
611+
</indexterm>
612+
613+
<indexterm>
614+
<primary>wraparound</primary>
615+
<secondary>of multixact IDs</secondary>
616+
</indexterm>
617+
618+
<para>
619+
<firstterm>Multixacts</> are used to implement row locking by
620+
multiple transactions: since there is limited space in the tuple
621+
header to store lock information, that information is stored as a
622+
multixact separately in the <filename>pg_multixact</> subdirectory,
623+
and only its ID is in the <structfield>xmax</> field
624+
in the tuple header.
625+
Similar to transaction IDs, multixact IDs are implemented as a
626+
32-bit counter and corresponding storage, all of which requires
627+
careful aging management, storage cleanup, and wraparound handling.
628+
</para>
629+
630+
<para>
631+
During a <command>VACUUM</> table scan, either partial or of the whole
632+
table, any multixact ID older than
633+
<xref linkend="guc-vacuum-multixact-freeze-min-age">
634+
is replaced by a different value, which can be the zero value, a single
635+
transaction ID, or a newer multixact ID. For each table,
636+
<structname>pg_class</>.<structfield>relminmxid</> stores the oldest
637+
possible value still stored in any tuple of that table. Every time this
638+
value is older than
639+
<xref linkend="guc-vacuum-multixact-freeze-table-age">, a whole-table
640+
scan is forced. Whole-table <command>VACUUM</> scans, regardless of
641+
what causes them, enable advancing the value for that table.
642+
Eventually, as all tables in all databases are scanned and their
643+
oldest multixact values are advanced, on-disk storage for older
644+
multixacts can be removed.
645+
</para>
646+
647+
<para>
648+
As a safety device, a whole-table vacuum scan will occur for any table
649+
whose multixact-age is greater than
650+
<xref linkend="guc-autovacuum-multixact-freeze-max-age">.
651+
This will occur even if autovacuum is nominally disabled.
652+
</para>
653+
</sect3>
600654
</sect2>
601655

602656
<sect2 id="autovacuum">

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 38 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -985,7 +985,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
985985
<para>
986986
Custom <xref linkend="guc-vacuum-freeze-min-age"> parameter. Note that
987987
autovacuum will ignore attempts to set a per-table
988-
<literal>autovacuum_freeze_min_age</> larger than the half system-wide
988+
<literal>autovacuum_freeze_min_age</> larger than half the system-wide
989989
<xref linkend="guc-autovacuum-freeze-max-age"> setting.
990990
</para>
991991
</listitem>
@@ -1014,6 +1014,43 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
10141014
</listitem>
10151015
</varlistentry>
10161016

1017+
<varlistentry>
1018+
<term><literal>autovacuum_multixact_freeze_min_age</literal>, <literal>toast.autovacuum_multixact_freeze_min_age</literal> (<type>integer</type>)</term>
1019+
<listitem>
1020+
<para>
1021+
Custom <xref linkend="guc-vacuum-multixact-freeze-min-age"> parameter.
1022+
Note that autovacuum will ignore attempts to set a per-table
1023+
<literal>autovacuum_multixact_freeze_min_age</> larger than half the
1024+
system-wide <xref linkend="guc-autovacuum-multixact-freeze-max-age">
1025+
setting.
1026+
</para>
1027+
</listitem>
1028+
</varlistentry>
1029+
1030+
<varlistentry>
1031+
<term><literal>autovacuum_multixact_freeze_max_age</literal>, <literal>toast.autovacuum_multixact_freeze_max_age</literal> (<type>integer</type>)</term>
1032+
<listitem>
1033+
<para>
1034+
Custom <xref linkend="guc-autovacuum-multixact-freeze-max-age"> parameter. Note
1035+
that autovacuum will ignore attempts to set a per-table
1036+
<literal>autovacuum_multixact_freeze_max_age</> larger than the
1037+
system-wide setting (it can only be set smaller). Note that while you
1038+
can set <literal>autovacuum_multixact_freeze_max_age</> very small,
1039+
or even zero, this is usually unwise since it will force frequent
1040+
vacuuming.
1041+
</para>
1042+
</listitem>
1043+
</varlistentry>
1044+
1045+
<varlistentry>
1046+
<term><literal>autovacuum_multixact_freeze_table_age</literal>, <literal>toast.autovacuum_multixact_freeze_table_age</literal> (<type>integer</type>)</term>
1047+
<listitem>
1048+
<para>
1049+
Custom <xref linkend="guc-vacuum-multixact-freeze-table-age"> parameter.
1050+
</para>
1051+
</listitem>
1052+
</varlistentry>
1053+
10171054
</variablelist>
10181055

10191056
</refsect2>

‎src/backend/access/common/reloptions.c

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -171,6 +171,14 @@ static relopt_int intRelOpts[] =
171171
},
172172
-1, 0, 1000000000
173173
},
174+
{
175+
{
176+
"autovacuum_multixact_freeze_min_age",
177+
"Minimum multixact age at which VACUUM should freeze a row multixact's, for autovacuum",
178+
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
179+
},
180+
-1, 0, 1000000000
181+
},
174182
{
175183
{
176184
"autovacuum_freeze_max_age",
@@ -179,13 +187,29 @@ static relopt_int intRelOpts[] =
179187
},
180188
-1, 100000000, 2000000000
181189
},
190+
{
191+
{
192+
"autovacuum_multixact_freeze_max_age",
193+
"Multixact age at which to autovacuum a table to prevent multixact wraparound",
194+
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
195+
},
196+
-1, 100000000, 2000000000
197+
},
182198
{
183199
{
184200
"autovacuum_freeze_table_age",
185201
"Age at which VACUUM should perform a full table sweep to freeze row versions",
186202
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
187203
}, -1, 0, 2000000000
188204
},
205+
{
206+
{
207+
"autovacuum_multixact_freeze_table_age",
208+
"Age of multixact at which VACUUM should perform a full table sweep to freeze row versions",
209+
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
210+
}, -1, 0, 2000000000
211+
},
212+
189213
/* list terminator */
190214
{{NULL}}
191215
};
@@ -1166,6 +1190,12 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
11661190
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, freeze_max_age)},
11671191
{"autovacuum_freeze_table_age", RELOPT_TYPE_INT,
11681192
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, freeze_table_age)},
1193+
{"autovacuum_multixact_freeze_min_age", RELOPT_TYPE_INT,
1194+
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, multixact_freeze_min_age)},
1195+
{"autovacuum_multixact_freeze_max_age", RELOPT_TYPE_INT,
1196+
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, multixact_freeze_max_age)},
1197+
{"autovacuum_multixact_freeze_table_age", RELOPT_TYPE_INT,
1198+
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, multixact_freeze_table_age)},
11691199
{"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL,
11701200
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, vacuum_scale_factor)},
11711201
{"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL,

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

Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2055,11 +2055,13 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
20552055
Assert(MultiXactIdIsValid(oldest_datminmxid));
20562056

20572057
/*
2058-
* The place where we actually get into deep trouble is halfway around
2059-
* from the oldest potentially-existing XID/multi. (This calculation is
2060-
* probably off by one or two counts for Xids, because the special XIDs
2061-
* reduce the size of the loop a little bit. But we throw in plenty of
2062-
* slop below, so it doesn't matter.)
2058+
* Since multixacts wrap differently from transaction IDs, this logic is
2059+
* not entirely correct: in some scenarios we could go for longer than 2
2060+
* billion multixacts without seeing any data loss, and in some others we
2061+
* could get in trouble before that if the new pg_multixact/members data
2062+
* stomps on the previous cycle's data. For lack of a better mechanism we
2063+
* use the same logic as for transaction IDs, that is, start taking action
2064+
* halfway around the oldest potentially-existing multixact.
20632065
*/
20642066
multiWrapLimit = oldest_datminmxid + (MaxMultiXactId >> 1);
20652067
if (multiWrapLimit < FirstMultiXactId)
@@ -2093,12 +2095,13 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
20932095

20942096
/*
20952097
* We'll start trying to force autovacuums when oldest_datminmxid gets to
2096-
* be more than autovacuum_freeze_max_age mxids old.
2098+
* be more than autovacuum_multixact_freeze_max_age mxids old.
20972099
*
2098-
* It's a bit ugly to just reuse limits for xids that way, but it doesn't
2099-
* seem worth adding separate GUCs for that purpose.
2100+
* Note: autovacuum_multixact_freeze_max_age is a PGC_POSTMASTER parameter
2101+
* so that we don't have to worry about dealing with on-the-fly changes in
2102+
* its value. See SetTransactionIdLimit.
21002103
*/
2101-
multiVacLimit = oldest_datminmxid + autovacuum_freeze_max_age;
2104+
multiVacLimit = oldest_datminmxid + autovacuum_multixact_freeze_max_age;
21022105
if (multiVacLimit < FirstMultiXactId)
21032106
multiVacLimit += FirstMultiXactId;
21042107

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -313,7 +313,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
313313
* value. It doesn't look practical to update shared state from a GUC
314314
* assign hook (too many processes would try to execute the hook,
315315
* resulting in race conditions as well as crashes of those not connected
316-
* to shared memory). Perhaps this can be improved someday.
316+
* to shared memory). Perhaps this can be improved someday. See also
317+
* SetMultiXactIdLimit.
317318
*/
318319
xidVacLimit = oldest_datfrozenxid + autovacuum_freeze_max_age;
319320
if (xidVacLimit < FirstNormalTransactionId)

‎src/backend/commands/cluster.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -850,7 +850,7 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
850850
* Since we're going to rewrite the whole table anyway, there's no reason
851851
* not to be aggressive about this.
852852
*/
853-
vacuum_set_xid_limits(0, 0, OldHeap->rd_rel->relisshared,
853+
vacuum_set_xid_limits(0, 0, 0, 0, OldHeap->rd_rel->relisshared,
854854
&OldestXmin, &FreezeXid, NULL, &MultiXactCutoff,
855855
NULL);
856856

0 commit comments

Comments
 (0)