From: Melanie Plageman <melanieplageman@gmail.com>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Eagerly scan all-visible pages to amortize aggressive vacuum
Date: Tue, 11 Feb 2025 18:55:28 +0000
Message-ID: <E1thvPs-006b6z-5H@gemulon.postgresql.org> (raw)
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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/052026c9b903380b428a4c9ba2ec90726db81288
Modified Files
--------------
doc/src/sgml/config.sgml | 39 +++
doc/src/sgml/maintenance.sgml | 33 +-
doc/src/sgml/ref/create_table.sgml | 15 +
src/backend/access/common/reloptions.c | 14 +-
src/backend/access/heap/vacuumlazy.c | 435 ++++++++++++++++++++++++--
src/backend/commands/vacuum.c | 15 +
src/backend/postmaster/autovacuum.c | 6 +
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/bin/psql/tab-complete.in.c | 2 +
src/include/commands/vacuum.h | 17 +
src/include/utils/rel.h | 6 +
12 files changed, 552 insertions(+), 41 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: melanieplageman@gmail.com, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Eagerly scan all-visible pages to amortize aggressive vacuum
In-Reply-To: <E1thvPs-006b6z-5H@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox