Skip to content

Commit 052026c

Browse files
melanieplagemanmelanieplageman
committed
Eagerly scan all-visible pages to amortize aggressive vacuum
Aggressive vacuums must scan every unfrozen tuple in order to advance the relfrozenxid/relminmxid. Because data is often vacuumed before it is old enough to require freezing, relations may build up a large backlog of pages that are set all-visible but not all-frozen in the visibility map. When an aggressive vacuum is triggered, all of these pages must be scanned. These pages have often been evicted from shared buffers and even from the kernel buffer cache. Thus, aggressive vacuums often incur large amounts of extra I/O at the expense of foreground workloads. To amortize the cost of aggressive vacuums, eagerly scan some all-visible but not all-frozen pages during normal vacuums. All-visible pages that are eagerly scanned and set all-frozen in the visibility map are counted as successful eager freezes and those not frozen are counted as failed eager freezes. If too many eager scans fail in a row, eager scanning is temporarily suspended until a later portion of the relation. The number of failures tolerated is configurable globally and per table. To effectively amortize aggressive vacuums, we cap the number of successes as well. Capping eager freeze successes also limits the amount of potentially wasted work if these pages are modified again before the next aggressive vacuum. Once we reach the maximum number of blocks successfully eager frozen, eager scanning is disabled for the remainder of the vacuum of the relation. Original design idea from Robert Haas, with enhancements from Andres Freund, Tomas Vondra, and me Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Robert Treat <rob@xzilla.net> Reviewed-by: Bilal Yavuz <byavuz81@gmail.com> Discussion: https://postgr.es/m/flat/CAAKRu_ZF_KCzZuOrPrOqjGVe8iRVWEAJSpzMgRQs%3D5-v84cXUg%40mail.gmail.com
1 parent 4dd09a1 commit 052026c

File tree

12 files changed

+552
-41
lines changed

12 files changed

+552
-41
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9147,6 +9147,45 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
91479147
</listitem>
91489148
</varlistentry>
91499149

9150+
<varlistentry id="guc-vacuum-max-eager-freeze-failure-rate" xreflabel="vacuum_max_eager_freeze_failure_rate">
9151+
<term><varname>vacuum_max_eager_freeze_failure_rate</varname> (<type>floating point</type>)
9152+
<indexterm>
9153+
<primary><varname>vacuum_max_eager_freeze_failure_rate</varname> configuration parameter</primary>
9154+
</indexterm>
9155+
</term>
9156+
<listitem>
9157+
<para>
9158+
Specifies the maximum number of pages (as a fraction of total pages in
9159+
the relation) that <command>VACUUM</command> may scan and
9160+
<emphasis>fail</emphasis> to set all-frozen in the visibility map
9161+
before disabling eager scanning. A value of <literal>0</literal>
9162+
disables eager scanning altogether. The default is
9163+
<literal>0.03</literal> (3%).
9164+
</para>
9165+
9166+
<para>
9167+
Note that when eager scanning is enabled, successful page freezes do
9168+
not count against the cap on eager freeze failures. Successful page
9169+
freezes are capped internally at 20% of the all-visible but not
9170+
all-frozen pages in the relation. Capping successful page freezes helps
9171+
amortize the overhead across multiple normal vacuums and limits the
9172+
potential downside of wasted eager freezes of pages that are modified
9173+
again before the next aggressive vacuum.
9174+
</para>
9175+
9176+
<para>
9177+
This parameter can only be set in the
9178+
<filename>postgresql.conf</filename> file or on the server command
9179+
line; but the setting can be overridden for individual tables by
9180+
changing the
9181+
<link linkend="reloption-vacuum-max-eager-freeze-failure-rate">
9182+
corresponding table storage parameter</link>.
9183+
For more information on tuning vacuum's freezing behavior,
9184+
see <xref linkend="vacuum-for-wraparound"/>.
9185+
</para>
9186+
</listitem>
9187+
</varlistentry>
9188+
91509189
</variablelist>
91519190
</sect2>
91529191
</sect1>

‎doc/src/sgml/maintenance.sgml

Lines changed: 25 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -496,9 +496,25 @@
496496
When that happens, <command>VACUUM</command> will eventually need to perform an
497497
<firstterm>aggressive vacuum</firstterm>, which will freeze all eligible unfrozen
498498
XID and MXID values, including those from all-visible but not all-frozen pages.
499-
In practice most tables require periodic aggressive vacuuming.
499+
</para>
500+
501+
<para>
502+
If a table is building up a backlog of all-visible but not all-frozen
503+
pages, a normal vacuum may choose to scan skippable pages in an effort to
504+
freeze them. Doing so decreases the number of pages the next aggressive
505+
vacuum must scan. These are referred to as <firstterm>eagerly
506+
scanned</firstterm> pages. Eager scanning can be tuned to attempt to freeze
507+
more all-visible pages by increasing <xref
508+
linkend="guc-vacuum-max-eager-freeze-failure-rate"/>. Even if eager
509+
scanning has kept the number of all-visible but not all-frozen pages to a
510+
minimum, most tables still require periodic aggressive vacuuming. However,
511+
any pages successfully eager frozen may be skipped during an aggressive
512+
vacuum, so eager freezing may minimize the overhead of aggressive vacuums.
513+
</para>
514+
515+
<para>
500516
<xref linkend="guc-vacuum-freeze-table-age"/>
501-
controls when <command>VACUUM</command> does that: all-visible but not all-frozen
517+
controls when a table is aggressively vacuumed. All all-visible but not all-frozen
502518
pages are scanned if the number of transactions that have passed since the
503519
last such scan is greater than <varname>vacuum_freeze_table_age</varname> minus
504520
<varname>vacuum_freeze_min_age</varname>. Setting
@@ -626,10 +642,12 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
626642
</tip>
627643

628644
<para>
629-
<command>VACUUM</command> normally only scans pages that have been modified
630-
since the last vacuum, but <structfield>relfrozenxid</structfield> can only be
631-
advanced when every page of the table
632-
that might contain unfrozen XIDs is scanned. This happens when
645+
While <command>VACUUM</command> scans mostly pages that have been
646+
modified since the last vacuum, it may also eagerly scan some
647+
all-visible but not all-frozen pages in an attempt to freeze them, but
648+
the <structfield>relfrozenxid</structfield> will only be advanced when
649+
every page of the table that might contain unfrozen XIDs is scanned.
650+
This happens when
633651
<structfield>relfrozenxid</structfield> is more than
634652
<varname>vacuum_freeze_table_age</varname> transactions old, when
635653
<command>VACUUM</command>'s <literal>FREEZE</literal> option is used, or when all
@@ -931,8 +949,7 @@ vacuum insert threshold = vacuum base insert threshold + vacuum insert scale fac
931949
If the <structfield>relfrozenxid</structfield> value of the table
932950
is more than <varname>vacuum_freeze_table_age</varname> transactions old,
933951
an aggressive vacuum is performed to freeze old tuples and advance
934-
<structfield>relfrozenxid</structfield>; otherwise, only pages that have been modified
935-
since the last vacuum are scanned.
952+
<structfield>relfrozenxid</structfield>.
936953
</para>
937954

938955
<para>

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1950,6 +1950,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
19501950
</listitem>
19511951
</varlistentry>
19521952

1953+
<varlistentry id="reloption-vacuum-max-eager-freeze-failure-rate" xreflabel="vacuum_max_eager_freeze_failure_rate">
1954+
<term><literal>vacuum_max_eager_freeze_failure_rate</literal>, <literal>toast.vacuum_max_eager_freeze_failure_rate</literal> (<type>floating point</type>)
1955+
<indexterm>
1956+
<primary><varname>vacuum_max_eager_freeze_failure_rate</varname></primary>
1957+
<secondary>storage parameter</secondary>
1958+
</indexterm>
1959+
</term>
1960+
<listitem>
1961+
<para>
1962+
Per-table value for <xref linkend="guc-vacuum-max-eager-freeze-failure-rate"/>
1963+
parameter.
1964+
</para>
1965+
</listitem>
1966+
</varlistentry>
1967+
19531968
<varlistentry id="reloption-user-catalog-table" xreflabel="user_catalog_table">
19541969
<term><literal>user_catalog_table</literal> (<type>boolean</type>)
19551970
<indexterm>

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

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -432,6 +432,16 @@ static relopt_real realRelOpts[] =
432432
},
433433
-1, 0.0, 100.0
434434
},
435+
{
436+
{
437+
"vacuum_max_eager_freeze_failure_rate",
438+
"Fraction of pages in a relation vacuum can scan and fail to freeze before disabling eager scanning.",
439+
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
440+
ShareUpdateExclusiveLock
441+
},
442+
-1, 0.0, 1.0
443+
},
444+
435445
{
436446
{
437447
"seq_page_cost",
@@ -1891,7 +1901,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
18911901
{"vacuum_index_cleanup", RELOPT_TYPE_ENUM,
18921902
offsetof(StdRdOptions, vacuum_index_cleanup)},
18931903
{"vacuum_truncate", RELOPT_TYPE_BOOL,
1894-
offsetof(StdRdOptions, vacuum_truncate)}
1904+
offsetof(StdRdOptions, vacuum_truncate)},
1905+
{"vacuum_max_eager_freeze_failure_rate", RELOPT_TYPE_REAL,
1906+
offsetof(StdRdOptions, vacuum_max_eager_freeze_failure_rate)}
18951907
};
18961908

18971909
return (bytea *) build_reloptions(reloptions, validate, kind,

0 commit comments

Comments
 (0)