From 052026c9b903380b428a4c9ba2ec90726db81288 Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Tue, 11 Feb 2025 13:52:19 -0500 Subject: 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 Reviewed-by: Masahiko Sawada Reviewed-by: Andres Freund Reviewed-by: Robert Treat Reviewed-by: Bilal Yavuz Discussion: https://postgr.es/m/flat/CAAKRu_ZF_KCzZuOrPrOqjGVe8iRVWEAJSpzMgRQs%3D5-v84cXUg%40mail.gmail.com --- doc/src/sgml/config.sgml | 39 ++++++++++++++++++++++++++++++++++++++ doc/src/sgml/maintenance.sgml | 33 ++++++++++++++++++++++++-------- doc/src/sgml/ref/create_table.sgml | 15 +++++++++++++++ 3 files changed, 79 insertions(+), 8 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 17795616b5d..fc186657a53 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -9147,6 +9147,45 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + vacuum_max_eager_freeze_failure_rate (floating point) + + vacuum_max_eager_freeze_failure_rate configuration parameter + + + + + Specifies the maximum number of pages (as a fraction of total pages in + the relation) that VACUUM may scan and + fail to set all-frozen in the visibility map + before disabling eager scanning. A value of 0 + disables eager scanning altogether. The default is + 0.03 (3%). + + + + Note that when eager scanning is enabled, successful page freezes do + not count against the cap on eager freeze failures. Successful page + freezes are capped internally at 20% of the all-visible but not + all-frozen pages in the relation. Capping successful page freezes helps + amortize the overhead across multiple normal vacuums and limits the + potential downside of wasted eager freezes of pages that are modified + again before the next aggressive vacuum. + + + + This parameter can only be set in the + postgresql.conf file or on the server command + line; but the setting can be overridden for individual tables by + changing the + + corresponding table storage parameter. + For more information on tuning vacuum's freezing behavior, + see . + + + + diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index f84ad7557d9..b5b9da7f8a9 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -496,9 +496,25 @@ When that happens, VACUUM will eventually need to perform an aggressive vacuum, which will freeze all eligible unfrozen XID and MXID values, including those from all-visible but not all-frozen pages. - In practice most tables require periodic aggressive vacuuming. + + + + If a table is building up a backlog of all-visible but not all-frozen + pages, a normal vacuum may choose to scan skippable pages in an effort to + freeze them. Doing so decreases the number of pages the next aggressive + vacuum must scan. These are referred to as eagerly + scanned pages. Eager scanning can be tuned to attempt to freeze + more all-visible pages by increasing . Even if eager + scanning has kept the number of all-visible but not all-frozen pages to a + minimum, most tables still require periodic aggressive vacuuming. However, + any pages successfully eager frozen may be skipped during an aggressive + vacuum, so eager freezing may minimize the overhead of aggressive vacuums. + + + - controls when VACUUM does that: all-visible but not all-frozen + controls when a table is aggressively vacuumed. All all-visible but not all-frozen pages are scanned if the number of transactions that have passed since the last such scan is greater than vacuum_freeze_table_age minus vacuum_freeze_min_age. Setting @@ -626,10 +642,12 @@ SELECT datname, age(datfrozenxid) FROM pg_database; - VACUUM normally only scans pages that have been modified - since the last vacuum, but relfrozenxid can only be - advanced when every page of the table - that might contain unfrozen XIDs is scanned. This happens when + While VACUUM scans mostly pages that have been + modified since the last vacuum, it may also eagerly scan some + all-visible but not all-frozen pages in an attempt to freeze them, but + the relfrozenxid will only be advanced when + every page of the table that might contain unfrozen XIDs is scanned. + This happens when relfrozenxid is more than vacuum_freeze_table_age transactions old, when VACUUM's FREEZE option is used, or when all @@ -931,8 +949,7 @@ vacuum insert threshold = vacuum base insert threshold + vacuum insert scale fac If the relfrozenxid value of the table is more than vacuum_freeze_table_age transactions old, an aggressive vacuum is performed to freeze old tuples and advance - relfrozenxid; otherwise, only pages that have been modified - since the last vacuum are scanned. + relfrozenxid. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 9acbc4dd34d..0a3e520f215 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1950,6 +1950,21 @@ WITH ( MODULUS numeric_literal, REM + + vacuum_max_eager_freeze_failure_rate, toast.vacuum_max_eager_freeze_failure_rate (floating point) + + vacuum_max_eager_freeze_failure_rate + storage parameter + + + + + Per-table value for + parameter. + + + + user_catalog_table (boolean) -- cgit v1.2.3