Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commitfd31cd2

Browse files
committed
Don't vacuum all-frozen pages.
Commita892234 gave us enoughinfrastructure to avoid vacuuming pages where every tuple on thepage is already frozen. So, replace the notion of a scan_all orwhole-table vacuum with the less onerous notion of an "aggressive"vacuum, which will pages that are all-visible, but still skip thosethat are all-frozen.This should greatly reduce the cost of anti-wraparound vacuumingon large clusters where the majority of data is never touchedbetween one cycle and the next, because we'll no longer have toread all of those pages only to find out that we don't need todo anything with them.Patch by me, reviewed by Masahiko Sawada.
1 parent364a9f4 commitfd31cd2

File tree

3 files changed

+196
-119
lines changed

3 files changed

+196
-119
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 13 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -5984,12 +5984,15 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
59845984
</term>
59855985
<listitem>
59865986
<para>
5987-
<command>VACUUM</> performsa whole-table scan if the table's
5987+
<command>VACUUM</> performsan aggressive scan if the table's
59885988
<structname>pg_class</>.<structfield>relfrozenxid</> field has reached
5989-
the age specified by this setting. The default is 150 million
5990-
transactions. Although users can set this value anywhere from zero to
5991-
two billions, <command>VACUUM</> will silently limit the effective value
5992-
to 95% of <xref linkend="guc-autovacuum-freeze-max-age">, so that a
5989+
the age specified by this setting. An aggressive scan differs from
5990+
a regular <command>VACUUM</> in that it visits every page that might
5991+
contain unfrozen XIDs or MXIDs, not just those that might contain dead
5992+
tuples. The default is 150 million transactions. Although users can
5993+
set this value anywhere from zero to two billions, <command>VACUUM</>
5994+
will silently limit the effective value to 95% of
5995+
<xref linkend="guc-autovacuum-freeze-max-age">, so that a
59935996
periodical manual <command>VACUUM</> has a chance to run before an
59945997
anti-wraparound autovacuum is launched for the table. For more
59955998
information see
@@ -6028,9 +6031,12 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
60286031
</term>
60296032
<listitem>
60306033
<para>
6031-
<command>VACUUM</> performsa whole-table scan if the table's
6034+
<command>VACUUM</> performsan aggressive scan if the table's
60326035
<structname>pg_class</>.<structfield>relminmxid</> field has reached
6033-
the age specified by this setting. The default is 150 million multixacts.
6036+
the age specified by this setting. An aggressive scan differs from
6037+
a regular <command>VACUUM</> in that it visits every page that might
6038+
contain unfrozen XIDs or MXIDs, not just those that might contain dead
6039+
tuples. The default is 150 million multixacts.
60346040
Although users can set this value anywhere from zero to two billions,
60356041
<command>VACUUM</> will silently limit the effective value to 95% of
60366042
<xref linkend="guc-autovacuum-multixact-freeze-max-age">, so that a

‎doc/src/sgml/maintenance.sgml

Lines changed: 48 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -438,22 +438,27 @@
438438
</para>
439439

440440
<para>
441-
<command>VACUUM</> normally skips pages that don't have any dead row
442-
versions, but those pages might still have row versions with old XID
443-
values. To ensure all old row versions have been frozen, a
444-
scan of the whole table is needed.
445-
<xref linkend="guc-vacuum-freeze-table-age"> controls when
446-
<command>VACUUM</> does that: a whole table sweep is forced if
447-
the table hasn't been fully scanned for <varname>vacuum_freeze_table_age</>
448-
minus <varname>vacuum_freeze_min_age</> transactions. Setting it to 0
449-
forces <command>VACUUM</> to always scan all pages, effectively ignoring
450-
the visibility map.
441+
<command>VACUUM</> uses the <link linkend="storage-vm">visibility map</>
442+
to determine which pages of a relation must be scanned. Normally, it
443+
will skips pages that don't have any dead row versions even if those pages
444+
might still have row versions with old XID values. Therefore, normal
445+
scans won't succeed in freezing every row version in the table.
446+
Periodically, <command>VACUUM</> will perform an <firstterm>aggressive
447+
vacuum</>, skipping only those pages which contain neither dead rows nor
448+
any unfrozen XID or MXID values.
449+
<xref linkend="guc-vacuum-freeze-table-age">
450+
controls when <command>VACUUM</> does that: all-visible but not all-frozen
451+
pages are scanned if the number of transactions that have passed since the
452+
last such scan is greater than <varname>vacuum_freeze_table_age</> minus
453+
<varname>vacuum_freeze_min_age</>. Setting
454+
<varname>vacuum_freeze_table_age</> to 0 forces <command>VACUUM</> to
455+
use this more aggressive strategy for all scans.
451456
</para>
452457

453458
<para>
454459
The maximum time that a table can go unvacuumed is two billion
455460
transactions minus the <varname>vacuum_freeze_min_age</> value at
456-
the time<command>VACUUM</> last scannedthewhole table. If it were to go
461+
the timeofthelast aggressive vacuum. If it were to go
457462
unvacuumed for longer than
458463
that, data loss could result. To ensure that this does not happen,
459464
autovacuum is invoked on any table that might contain unfrozen rows with
@@ -491,7 +496,7 @@
491496
normal delete and update activity is run in that window. Setting it too
492497
close could lead to anti-wraparound autovacuums, even though the table
493498
was recently vacuumed to reclaim space, whereas lower values lead to more
494-
frequentwhole-table scans.
499+
frequentaggressive vacuuming.
495500
</para>
496501

497502
<para>
@@ -527,7 +532,7 @@
527532
<structname>pg_database</>. In particular,
528533
the <structfield>relfrozenxid</> column of a table's
529534
<structname>pg_class</> row contains the freeze cutoff XID that was used
530-
by the lastwhole-table <command>VACUUM</> for that table. All rows
535+
by the lastaggressive <command>VACUUM</> for that table. All rows
531536
inserted by transactions with XIDs older than this cutoff XID are
532537
guaranteed to have been frozen. Similarly,
533538
the <structfield>datfrozenxid</> column of a database's
@@ -552,20 +557,23 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
552557
</para>
553558

554559
<para>
555-
<command>VACUUM</> normally
556-
only scans pages that have been modified since the last vacuum, but
557-
<structfield>relfrozenxid</> can only be advanced when the whole table is
558-
scanned. The whole table is scanned when <structfield>relfrozenxid</> is
559-
more than <varname>vacuum_freeze_table_age</> transactions old, when
560-
<command>VACUUM</>'s <literal>FREEZE</> option is used, or when all pages
561-
happen to
560+
<command>VACUUM</> normally only scans pages that have been modified
561+
since the last vacuum, but <structfield>relfrozenxid</> can only be
562+
advanced when every page of the table
563+
that might contain unfrozen XIDs is scanned. This happens when
564+
<structfield>relfrozenxid</> is more than
565+
<varname>vacuum_freeze_table_age</> transactions old, when
566+
<command>VACUUM</>'s <literal>FREEZE</> option is used, or when all
567+
pages that are not already all-frozen happen to
562568
require vacuuming to remove dead row versions. When <command>VACUUM</>
563-
scans the whole table, after it's finished <literal>age(relfrozenxid)</>
564-
should be a little more than the <varname>vacuum_freeze_min_age</> setting
565-
that was used (more by the number of transactions started since the
566-
<command>VACUUM</> started). If no whole-table-scanning <command>VACUUM</>
567-
is issued on the table until <varname>autovacuum_freeze_max_age</> is
568-
reached, an autovacuum will soon be forced for the table.
569+
scans every page in the table that is not already all-frozen, it should
570+
set <literal>age(relfrozenxid)</> to a value just a little more than the
571+
<varname>vacuum_freeze_min_age</> setting
572+
that was used (more by the number of transcations started since the
573+
<command>VACUUM</> started). If no <structfield>relfrozenxid</>-advancing
574+
<command>VACUUM</> is issued on the table until
575+
<varname>autovacuum_freeze_max_age</> is reached, an autovacuum will soon
576+
be forced for the table.
569577
</para>
570578

571579
<para>
@@ -634,35 +642,37 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
634642
</para>
635643

636644
<para>
637-
During a<command>VACUUM</>table scan, either partial or of the whole
638-
table,any multixact ID older than
645+
Whenever<command>VACUUM</>scans any part of a table, it will replace
646+
any multixact ID it encounters which is older than
639647
<xref linkend="guc-vacuum-multixact-freeze-min-age">
640-
is replacedby a different value, which can be the zero value, a single
648+
by a different value, which can be the zero value, a single
641649
transaction ID, or a newer multixact ID. For each table,
642650
<structname>pg_class</>.<structfield>relminmxid</> stores the oldest
643651
possible multixact ID still appearing in any tuple of that table.
644652
If this value is older than
645-
<xref linkend="guc-vacuum-multixact-freeze-table-age">, a whole-table
646-
scan is forced. <function>mxid_age()</> can be used on
653+
<xref linkend="guc-vacuum-multixact-freeze-table-age">, an aggressive
654+
vacuum is forced. As discussed in the previous section, an aggressive
655+
vacuum means that only those pages which are known to be all-frozen will
656+
be skipped. <function>mxid_age()</> can be used on
647657
<structname>pg_class</>.<structfield>relminmxid</> to find its age.
648658
</para>
649659

650660
<para>
651-
Whole-table <command>VACUUM</> scans, regardless of
661+
Aggressive <command>VACUUM</> scans, regardless of
652662
what causes them, enable advancing the value for that table.
653663
Eventually, as all tables in all databases are scanned and their
654664
oldest multixact values are advanced, on-disk storage for older
655665
multixacts can be removed.
656666
</para>
657667

658668
<para>
659-
As a safety device,a whole-table vacuum scan will occur for any table
669+
As a safety device,an aggressive vacuum scan will occur for any table
660670
whose multixact-age is greater than
661-
<xref linkend="guc-autovacuum-multixact-freeze-max-age">.Whole-table
671+
<xref linkend="guc-autovacuum-multixact-freeze-max-age">.Aggressive
662672
vacuum scans will also occur progressively for all tables, starting with
663673
those that have the oldest multixact-age, if the amount of used member
664674
storage space exceeds the amount 50% of the addressable storage space.
665-
Both of these kinds ofwhole-table scans will occur even if autovacuum is
675+
Both of these kinds ofaggressive scans will occur even if autovacuum is
666676
nominally disabled.
667677
</para>
668678
</sect3>
@@ -743,9 +753,9 @@ vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuple
743753
<command>UPDATE</command> and <command>DELETE</command> operation. (It
744754
is only semi-accurate because some information might be lost under heavy
745755
load.) If the <structfield>relfrozenxid</> value of the table is more
746-
than <varname>vacuum_freeze_table_age</> transactions old,the whole
747-
table isscanned to freeze old tuples and advance
748-
<structfield>relfrozenxid</>, otherwise only pages that have been modified
756+
than <varname>vacuum_freeze_table_age</> transactions old,an aggressive
757+
vacuum isperformed to freeze old tuples and advance
758+
<structfield>relfrozenxid</>; otherwise, only pages that have been modified
749759
since the last vacuum are scanned.
750760
</para>
751761

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp