agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Nathan Bossart <nathandbossart@gmail.com>
To: Sami Imseih <samimseih@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Jeremy Schneider <schneider@ardentperf.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: another autovacuum scheduling thread
Date: Wed, 29 Oct 2025 10:51:18 -0500
Message-ID: <aQI39ln_jZ8qorLE@nathan> (raw)
In-Reply-To: <CAA5RZ0sdhUjVVKYbBGs1qFsYC3-Mn+as=K5v8ydVGR5iziabFQ@mail.gmail.com>
References: <CAA5RZ0sfQ-VSCSafsrvyJ7wsW1utLwtPVJ5N6hB0726BGRDrgQ@mail.gmail.com>
<CAApHDvpxE8ci83d02dRE3-fMetb4Dc89-80FrjkGDz2q+ByJog@mail.gmail.com>
<CAA5RZ0upTpKqgrdNfMSX7UJdjx=+=CsQ6Xct+vcCZPvUVhdZvw@mail.gmail.com>
<CAApHDvp1=FOs6GneTzLSCHnCmC7z1_80=U3M=CKd82-pwS3YHg@mail.gmail.com>
<aPuWev3D9M4iGCUt@nathan>
<CAApHDvoM5MEHHBc0TNdrzkpq39WdEHSZhdWrtnx9zOWNXTSFGw@mail.gmail.com>
<aP-YgrcPi0EhgR9x@nathan>
<CAApHDvpOq09uVq7aXcuSBPAhZBTfAL-m2c4FOF2PphFe-YcnRg@mail.gmail.com>
<aQEvm40W3aVizp5Q@nathan>
<CAA5RZ0sdhUjVVKYbBGs1qFsYC3-Mn+as=K5v8ydVGR5iziabFQ@mail.gmail.com>
On Tue, Oct 28, 2025 at 05:44:37PM -0500, Sami Imseih wrote:
> My compiler is complaining about v6
>
> "../src/backend/postmaster/autovacuum.c:3293:32: warning: operation on
> ‘*score’ may be undefined [-Wsequence-point]
> 3293 | *score = *score = Max(*score, (double)
> instuples / Max(vacinsthresh, 1));
> [2/2] Linking target src/backend/postgres"
>
> shouldn't just be like below?
>
> *score =Max(*score, (double) instuples / Max(vacinsthresh, 1));
Oops. I fixed that typo in v7.
--
nathan
From a225d5965286c87403bdcad2aaad0ec5def7e115 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 10 Oct 2025 12:28:37 -0500
Subject: [PATCH v7 1/1] autovacuum scheduling improvements
---
src/backend/postmaster/autovacuum.c | 197 ++++++++++++++++++++++------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 158 insertions(+), 40 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 5084af7dfb6..e48bb06253b 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -97,6 +97,7 @@
#include "storage/procsignal.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
+#include "utils/float.h"
#include "utils/fmgroids.h"
#include "utils/fmgrprotos.h"
#include "utils/guc_hooks.h"
@@ -310,6 +311,12 @@ static AutoVacuumShmemStruct *AutoVacuumShmem;
static dlist_head DatabaseList = DLIST_STATIC_INIT(DatabaseList);
static MemoryContext DatabaseListCxt = NULL;
+typedef struct
+{
+ Oid oid;
+ double score;
+} TableToProcess;
+
/*
* Dummy pointer to persuade Valgrind that we've not leaked the array of
* avl_dbase structs. Make it global to ensure the compiler doesn't
@@ -351,7 +358,8 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
+ bool *dovacuum, bool *doanalyze, bool *wraparound,
+ double *score);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -1889,6 +1897,15 @@ get_database_list(void)
return dblist;
}
+static int
+TableToProcessComparator(const ListCell *a, const ListCell *b)
+{
+ TableToProcess *t1 = (TableToProcess *) lfirst(a);
+ TableToProcess *t2 = (TableToProcess *) lfirst(b);
+
+ return float8_cmp_internal(t2->score, t1->score);
+}
+
/*
* Process a database table-by-table
*
@@ -1902,7 +1919,7 @@ do_autovacuum(void)
HeapTuple tuple;
TableScanDesc relScan;
Form_pg_database dbForm;
- List *table_oids = NIL;
+ List *tables_to_process = NIL;
List *orphan_oids = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
@@ -2014,6 +2031,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ double score = 0.0;
if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW)
@@ -2054,11 +2072,19 @@ do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &score);
- /* Relations that need work are added to table_oids */
+ /* Relations that need work are added to tables_to_process */
if (dovacuum || doanalyze)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ TableToProcess *table = palloc(sizeof(TableToProcess));
+
+ table->oid = relid;
+ table->score = score;
+
+ tables_to_process = lappend(tables_to_process, table);
+ }
/*
* Remember TOAST associations for the second pass. Note: we must do
@@ -2114,6 +2140,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ double score = 0.0;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
@@ -2146,11 +2173,19 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &score);
/* ignore analyze for toast tables */
if (dovacuum)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ TableToProcess *table = palloc(sizeof(TableToProcess));
+
+ table->oid = relid;
+ table->score = score;
+
+ tables_to_process = lappend(tables_to_process, table);
+ }
/* Release stuff to avoid leakage */
if (free_relopts)
@@ -2274,6 +2309,8 @@ do_autovacuum(void)
MemoryContextSwitchTo(AutovacMemCxt);
}
+ list_sort(tables_to_process, TableToProcessComparator);
+
/*
* Optionally, create a buffer access strategy object for VACUUM to use.
* We use the same BufferAccessStrategy object for all tables VACUUMed by
@@ -2302,9 +2339,9 @@ do_autovacuum(void)
/*
* Perform operations on collected tables.
*/
- foreach(cell, table_oids)
+ foreach_ptr(TableToProcess, table, tables_to_process)
{
- Oid relid = lfirst_oid(cell);
+ Oid relid = table->oid;
HeapTuple classTup;
autovac_table *tab;
bool isshared;
@@ -2535,7 +2572,7 @@ deleted:
pg_atomic_test_set_flag(&MyWorkerInfo->wi_dobalance);
}
- list_free(table_oids);
+ list_free_deep(tables_to_process);
/*
* Perform additional work items, as requested by backends.
@@ -2934,6 +2971,7 @@ recheck_relation_needs_vacanalyze(Oid relid,
bool *wraparound)
{
PgStat_StatTabEntry *tabentry;
+ double score;
/* fetch the pgstat table entry */
tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
@@ -2941,15 +2979,12 @@ recheck_relation_needs_vacanalyze(Oid relid,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- dovacuum, doanalyze, wraparound);
+ dovacuum, doanalyze, wraparound,
+ &score);
/* Release tabentry to avoid leakage */
if (tabentry)
pfree(tabentry);
-
- /* ignore ANALYZE for toast tables */
- if (classForm->relkind == RELKIND_TOASTVALUE)
- *doanalyze = false;
}
/*
@@ -2990,6 +3025,32 @@ recheck_relation_needs_vacanalyze(Oid relid,
* autovacuum_vacuum_threshold GUC variable. Similarly, a vac_scale_factor
* value < 0 is substituted with the value of
* autovacuum_vacuum_scale_factor GUC variable. Ditto for analyze.
+ *
+ * This function also returns a score that can be used to sort the list of
+ * tables to process. The idea is to have autovacuum prioritize tables that
+ * are furthest beyond their thresholds (e.g., a table nearing transaction ID
+ * wraparound should be vacuumed first). This prioritization scheme is
+ * certainly far from perfect; there are simply too many possibilities for any
+ * scoring technique to work across all workloads, and the situation might
+ * change significantly between the time we calculate the score and the time
+ * that autovacuum gets to processing it. However, we have attempted to
+ * develop something that is expected to work for a large portion of workloads
+ * with reasonable parameter settings.
+ *
+ * The score is calculated as the maximum of the ratios of each of the table's
+ * relevant values to its threshold. For example, if the number of inserted
+ * tuples is 100, and the insert threshold for the table is 80, the insert
+ * score is 1.25. If all other scores are below that value, the returned score
+ * will be 1.25. The other criteria considered for the score are the table
+ * ages (both relfrozenxid and relminmxid) compared to the corresponding
+ * freeze-max-age setting, the number of updated/deleted tuples compared to the
+ * vacuum threshold, and the number of inserted/updated/deleted tuples compared
+ * to the analyze threshold.
+ *
+ * One exception to the previous paragraph is for tables nearing wraparound,
+ * i.e., those that have surpassed the effective failsafe ages. In that case,
+ * the relfrozen/relminmxid-based score is scaled aggressively so that the
+ * table has a decent chance of sorting to the top of the list.
*/
static void
relation_needs_vacanalyze(Oid relid,
@@ -3000,7 +3061,8 @@ relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ double *score)
{
bool force_vacuum;
bool av_enabled;
@@ -3029,11 +3091,16 @@ relation_needs_vacanalyze(Oid relid,
int multixact_freeze_max_age;
TransactionId xidForceLimit;
TransactionId relfrozenxid;
+ TransactionId relminmxid;
MultiXactId multiForceLimit;
Assert(classForm != NULL);
Assert(OidIsValid(relid));
+ /* initialize variables that aren't guaranteed to be set below */
+ *score = 0.0;
+ *doanalyze = false;
+
/*
* Determine vacuum/analyze equation parameters. We have two possible
* sources: the passed reloptions (which could be a main table or a toast
@@ -3081,32 +3148,73 @@ relation_needs_vacanalyze(Oid relid,
av_enabled = (relopts ? relopts->enabled : true);
+ relfrozenxid = classForm->relfrozenxid;
+ relminmxid = classForm->relminmxid;
+
/* Force vacuum if table is at risk of wraparound */
xidForceLimit = recentXid - freeze_max_age;
if (xidForceLimit < FirstNormalTransactionId)
xidForceLimit -= FirstNormalTransactionId;
- relfrozenxid = classForm->relfrozenxid;
force_vacuum = (TransactionIdIsNormal(relfrozenxid) &&
TransactionIdPrecedes(relfrozenxid, xidForceLimit));
if (!force_vacuum)
{
- MultiXactId relminmxid = classForm->relminmxid;
-
multiForceLimit = recentMulti - multixact_freeze_max_age;
if (multiForceLimit < FirstMultiXactId)
multiForceLimit -= FirstMultiXactId;
force_vacuum = MultiXactIdIsValid(relminmxid) &&
MultiXactIdPrecedes(relminmxid, multiForceLimit);
}
- *wraparound = force_vacuum;
+ *wraparound = *dovacuum = force_vacuum;
+
+ /* Update the score. */
+ if (force_vacuum)
+ {
+ Oid xid_age;
+ Oid mxid_age;
+ double xid_score;
+ double mxid_score;
+ int effective_xid_failsafe_age;
+ int effective_mxid_failsafe_age;
+
+ /*
+ * To calculate the (M)XID age portion of the score, divide the age by
+ * its respective *_freeze_max_age parameter.
+ */
+ xid_age = TransactionIdIsNormal(relfrozenxid) ? recentXid - relfrozenxid : 0;
+ mxid_age = MultiXactIdIsValid(relminmxid) ? recentMulti - relminmxid : 0;
+
+ xid_score = (double) xid_age / freeze_max_age;
+ mxid_score = (double) mxid_age / multixact_freeze_max_age;
+
+ /*
+ * To ensure tables are given increased priority once they begin
+ * approaching wraparound, we scale the score aggressively if the ages
+ * surpass vacuum_failsafe_age or vacuum_multixact_failsafe_age.
+ *
+ * As in vacuum_xid_failsafe_check(), the effective failsafe age is no
+ * less than 105% the value of the respective *_freeze_max_age
+ * parameter. Note that per-table settings could result in a low
+ * score even if the table surpasses the failsafe settings. However,
+ * this is a strange enough corner case that we don't bother trying to
+ * handle it.
+ */
+ effective_xid_failsafe_age = Max(vacuum_failsafe_age,
+ autovacuum_freeze_max_age * 1.05);
+ effective_mxid_failsafe_age = Max(vacuum_multixact_failsafe_age,
+ autovacuum_multixact_freeze_max_age * 1.05);
+
+ if (xid_age >= effective_xid_failsafe_age)
+ xid_score = pow(xid_score, Max(1.0, (double) xid_age / 100000000));
+ if (mxid_age >= effective_mxid_failsafe_age)
+ mxid_score = pow(mxid_score, Max(1.0, (double) mxid_age / 100000000));
+
+ *score = Max(xid_score, mxid_score);
+ }
/* User disabled it in pg_class.reloptions? (But ignore if at risk) */
if (!av_enabled && !force_vacuum)
- {
- *doanalyze = false;
- *dovacuum = false;
return;
- }
/*
* If we found stats for the table, and autovacuum is currently enabled,
@@ -3169,25 +3277,34 @@ relation_needs_vacanalyze(Oid relid,
NameStr(classForm->relname),
vactuples, vacthresh, anltuples, anlthresh);
- /* Determine if this table needs vacuum or analyze. */
- *dovacuum = force_vacuum || (vactuples > vacthresh) ||
- (vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
- *doanalyze = (anltuples > anlthresh);
- }
- else
- {
/*
- * Skip a table not found in stat hash, unless we have to force vacuum
- * for anti-wrap purposes. If it's not acted upon, there's no need to
- * vacuum it.
+ * Determine if this table needs vacuum, and update the score if it
+ * does.
*/
- *dovacuum = force_vacuum;
- *doanalyze = false;
- }
+ if (vactuples > vacthresh)
+ {
+ *dovacuum = true;
+ *score = Max(*score, (double) vactuples / Max(vacthresh, 1));
+ }
+
+ if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh)
+ {
+ *dovacuum = true;
+ *score = Max(*score, (double) instuples / Max(vacinsthresh, 1));
+ }
- /* ANALYZE refuses to work with pg_statistic */
- if (relid == StatisticRelationId)
- *doanalyze = false;
+ /*
+ * Determine if this table needs analyze, and update the score if it
+ * does. Note that we don't analyze TOAST tables and pg_statistic.
+ */
+ if (anltuples > anlthresh &&
+ relid != StatisticRelationId &&
+ classForm->relkind != RELKIND_TOASTVALUE)
+ {
+ *doanalyze = true;
+ *score = Max(*score, (double) anltuples / Max(anlthresh, 1));
+ }
+ }
}
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ac2da4c98cf..bb977229f75 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3007,6 +3007,7 @@ TableScanDesc
TableScanDescData
TableSpaceCacheEntry
TableSpaceOpts
+TableToProcess
TablespaceList
TablespaceListCell
TapeBlockTrailer
--
2.39.5 (Apple Git-154)
Attachments:
[text/plain] v7-0001-autovacuum-scheduling-improvements.patch (13.1K, ../aQI39ln_jZ8qorLE@nathan/2-v7-0001-autovacuum-scheduling-improvements.patch)
download | inline diff:
From a225d5965286c87403bdcad2aaad0ec5def7e115 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 10 Oct 2025 12:28:37 -0500
Subject: [PATCH v7 1/1] autovacuum scheduling improvements
---
src/backend/postmaster/autovacuum.c | 197 ++++++++++++++++++++++------
src/tools/pgindent/typedefs.list | 1 +
2 files changed, 158 insertions(+), 40 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 5084af7dfb6..e48bb06253b 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -97,6 +97,7 @@
#include "storage/procsignal.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
+#include "utils/float.h"
#include "utils/fmgroids.h"
#include "utils/fmgrprotos.h"
#include "utils/guc_hooks.h"
@@ -310,6 +311,12 @@ static AutoVacuumShmemStruct *AutoVacuumShmem;
static dlist_head DatabaseList = DLIST_STATIC_INIT(DatabaseList);
static MemoryContext DatabaseListCxt = NULL;
+typedef struct
+{
+ Oid oid;
+ double score;
+} TableToProcess;
+
/*
* Dummy pointer to persuade Valgrind that we've not leaked the array of
* avl_dbase structs. Make it global to ensure the compiler doesn't
@@ -351,7 +358,8 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
+ bool *dovacuum, bool *doanalyze, bool *wraparound,
+ double *score);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -1889,6 +1897,15 @@ get_database_list(void)
return dblist;
}
+static int
+TableToProcessComparator(const ListCell *a, const ListCell *b)
+{
+ TableToProcess *t1 = (TableToProcess *) lfirst(a);
+ TableToProcess *t2 = (TableToProcess *) lfirst(b);
+
+ return float8_cmp_internal(t2->score, t1->score);
+}
+
/*
* Process a database table-by-table
*
@@ -1902,7 +1919,7 @@ do_autovacuum(void)
HeapTuple tuple;
TableScanDesc relScan;
Form_pg_database dbForm;
- List *table_oids = NIL;
+ List *tables_to_process = NIL;
List *orphan_oids = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
@@ -2014,6 +2031,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ double score = 0.0;
if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW)
@@ -2054,11 +2072,19 @@ do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &score);
- /* Relations that need work are added to table_oids */
+ /* Relations that need work are added to tables_to_process */
if (dovacuum || doanalyze)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ TableToProcess *table = palloc(sizeof(TableToProcess));
+
+ table->oid = relid;
+ table->score = score;
+
+ tables_to_process = lappend(tables_to_process, table);
+ }
/*
* Remember TOAST associations for the second pass. Note: we must do
@@ -2114,6 +2140,7 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ double score = 0.0;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
@@ -2146,11 +2173,19 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &score);
/* ignore analyze for toast tables */
if (dovacuum)
- table_oids = lappend_oid(table_oids, relid);
+ {
+ TableToProcess *table = palloc(sizeof(TableToProcess));
+
+ table->oid = relid;
+ table->score = score;
+
+ tables_to_process = lappend(tables_to_process, table);
+ }
/* Release stuff to avoid leakage */
if (free_relopts)
@@ -2274,6 +2309,8 @@ do_autovacuum(void)
MemoryContextSwitchTo(AutovacMemCxt);
}
+ list_sort(tables_to_process, TableToProcessComparator);
+
/*
* Optionally, create a buffer access strategy object for VACUUM to use.
* We use the same BufferAccessStrategy object for all tables VACUUMed by
@@ -2302,9 +2339,9 @@ do_autovacuum(void)
/*
* Perform operations on collected tables.
*/
- foreach(cell, table_oids)
+ foreach_ptr(TableToProcess, table, tables_to_process)
{
- Oid relid = lfirst_oid(cell);
+ Oid relid = table->oid;
HeapTuple classTup;
autovac_table *tab;
bool isshared;
@@ -2535,7 +2572,7 @@ deleted:
pg_atomic_test_set_flag(&MyWorkerInfo->wi_dobalance);
}
- list_free(table_oids);
+ list_free_deep(tables_to_process);
/*
* Perform additional work items, as requested by backends.
@@ -2934,6 +2971,7 @@ recheck_relation_needs_vacanalyze(Oid relid,
bool *wraparound)
{
PgStat_StatTabEntry *tabentry;
+ double score;
/* fetch the pgstat table entry */
tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
@@ -2941,15 +2979,12 @@ recheck_relation_needs_vacanalyze(Oid relid,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- dovacuum, doanalyze, wraparound);
+ dovacuum, doanalyze, wraparound,
+ &score);
/* Release tabentry to avoid leakage */
if (tabentry)
pfree(tabentry);
-
- /* ignore ANALYZE for toast tables */
- if (classForm->relkind == RELKIND_TOASTVALUE)
- *doanalyze = false;
}
/*
@@ -2990,6 +3025,32 @@ recheck_relation_needs_vacanalyze(Oid relid,
* autovacuum_vacuum_threshold GUC variable. Similarly, a vac_scale_factor
* value < 0 is substituted with the value of
* autovacuum_vacuum_scale_factor GUC variable. Ditto for analyze.
+ *
+ * This function also returns a score that can be used to sort the list of
+ * tables to process. The idea is to have autovacuum prioritize tables that
+ * are furthest beyond their thresholds (e.g., a table nearing transaction ID
+ * wraparound should be vacuumed first). This prioritization scheme is
+ * certainly far from perfect; there are simply too many possibilities for any
+ * scoring technique to work across all workloads, and the situation might
+ * change significantly between the time we calculate the score and the time
+ * that autovacuum gets to processing it. However, we have attempted to
+ * develop something that is expected to work for a large portion of workloads
+ * with reasonable parameter settings.
+ *
+ * The score is calculated as the maximum of the ratios of each of the table's
+ * relevant values to its threshold. For example, if the number of inserted
+ * tuples is 100, and the insert threshold for the table is 80, the insert
+ * score is 1.25. If all other scores are below that value, the returned score
+ * will be 1.25. The other criteria considered for the score are the table
+ * ages (both relfrozenxid and relminmxid) compared to the corresponding
+ * freeze-max-age setting, the number of updated/deleted tuples compared to the
+ * vacuum threshold, and the number of inserted/updated/deleted tuples compared
+ * to the analyze threshold.
+ *
+ * One exception to the previous paragraph is for tables nearing wraparound,
+ * i.e., those that have surpassed the effective failsafe ages. In that case,
+ * the relfrozen/relminmxid-based score is scaled aggressively so that the
+ * table has a decent chance of sorting to the top of the list.
*/
static void
relation_needs_vacanalyze(Oid relid,
@@ -3000,7 +3061,8 @@ relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ double *score)
{
bool force_vacuum;
bool av_enabled;
@@ -3029,11 +3091,16 @@ relation_needs_vacanalyze(Oid relid,
int multixact_freeze_max_age;
TransactionId xidForceLimit;
TransactionId relfrozenxid;
+ TransactionId relminmxid;
MultiXactId multiForceLimit;
Assert(classForm != NULL);
Assert(OidIsValid(relid));
+ /* initialize variables that aren't guaranteed to be set below */
+ *score = 0.0;
+ *doanalyze = false;
+
/*
* Determine vacuum/analyze equation parameters. We have two possible
* sources: the passed reloptions (which could be a main table or a toast
@@ -3081,32 +3148,73 @@ relation_needs_vacanalyze(Oid relid,
av_enabled = (relopts ? relopts->enabled : true);
+ relfrozenxid = classForm->relfrozenxid;
+ relminmxid = classForm->relminmxid;
+
/* Force vacuum if table is at risk of wraparound */
xidForceLimit = recentXid - freeze_max_age;
if (xidForceLimit < FirstNormalTransactionId)
xidForceLimit -= FirstNormalTransactionId;
- relfrozenxid = classForm->relfrozenxid;
force_vacuum = (TransactionIdIsNormal(relfrozenxid) &&
TransactionIdPrecedes(relfrozenxid, xidForceLimit));
if (!force_vacuum)
{
- MultiXactId relminmxid = classForm->relminmxid;
-
multiForceLimit = recentMulti - multixact_freeze_max_age;
if (multiForceLimit < FirstMultiXactId)
multiForceLimit -= FirstMultiXactId;
force_vacuum = MultiXactIdIsValid(relminmxid) &&
MultiXactIdPrecedes(relminmxid, multiForceLimit);
}
- *wraparound = force_vacuum;
+ *wraparound = *dovacuum = force_vacuum;
+
+ /* Update the score. */
+ if (force_vacuum)
+ {
+ Oid xid_age;
+ Oid mxid_age;
+ double xid_score;
+ double mxid_score;
+ int effective_xid_failsafe_age;
+ int effective_mxid_failsafe_age;
+
+ /*
+ * To calculate the (M)XID age portion of the score, divide the age by
+ * its respective *_freeze_max_age parameter.
+ */
+ xid_age = TransactionIdIsNormal(relfrozenxid) ? recentXid - relfrozenxid : 0;
+ mxid_age = MultiXactIdIsValid(relminmxid) ? recentMulti - relminmxid : 0;
+
+ xid_score = (double) xid_age / freeze_max_age;
+ mxid_score = (double) mxid_age / multixact_freeze_max_age;
+
+ /*
+ * To ensure tables are given increased priority once they begin
+ * approaching wraparound, we scale the score aggressively if the ages
+ * surpass vacuum_failsafe_age or vacuum_multixact_failsafe_age.
+ *
+ * As in vacuum_xid_failsafe_check(), the effective failsafe age is no
+ * less than 105% the value of the respective *_freeze_max_age
+ * parameter. Note that per-table settings could result in a low
+ * score even if the table surpasses the failsafe settings. However,
+ * this is a strange enough corner case that we don't bother trying to
+ * handle it.
+ */
+ effective_xid_failsafe_age = Max(vacuum_failsafe_age,
+ autovacuum_freeze_max_age * 1.05);
+ effective_mxid_failsafe_age = Max(vacuum_multixact_failsafe_age,
+ autovacuum_multixact_freeze_max_age * 1.05);
+
+ if (xid_age >= effective_xid_failsafe_age)
+ xid_score = pow(xid_score, Max(1.0, (double) xid_age / 100000000));
+ if (mxid_age >= effective_mxid_failsafe_age)
+ mxid_score = pow(mxid_score, Max(1.0, (double) mxid_age / 100000000));
+
+ *score = Max(xid_score, mxid_score);
+ }
/* User disabled it in pg_class.reloptions? (But ignore if at risk) */
if (!av_enabled && !force_vacuum)
- {
- *doanalyze = false;
- *dovacuum = false;
return;
- }
/*
* If we found stats for the table, and autovacuum is currently enabled,
@@ -3169,25 +3277,34 @@ relation_needs_vacanalyze(Oid relid,
NameStr(classForm->relname),
vactuples, vacthresh, anltuples, anlthresh);
- /* Determine if this table needs vacuum or analyze. */
- *dovacuum = force_vacuum || (vactuples > vacthresh) ||
- (vac_ins_base_thresh >= 0 && instuples > vacinsthresh);
- *doanalyze = (anltuples > anlthresh);
- }
- else
- {
/*
- * Skip a table not found in stat hash, unless we have to force vacuum
- * for anti-wrap purposes. If it's not acted upon, there's no need to
- * vacuum it.
+ * Determine if this table needs vacuum, and update the score if it
+ * does.
*/
- *dovacuum = force_vacuum;
- *doanalyze = false;
- }
+ if (vactuples > vacthresh)
+ {
+ *dovacuum = true;
+ *score = Max(*score, (double) vactuples / Max(vacthresh, 1));
+ }
+
+ if (vac_ins_base_thresh >= 0 && instuples > vacinsthresh)
+ {
+ *dovacuum = true;
+ *score = Max(*score, (double) instuples / Max(vacinsthresh, 1));
+ }
- /* ANALYZE refuses to work with pg_statistic */
- if (relid == StatisticRelationId)
- *doanalyze = false;
+ /*
+ * Determine if this table needs analyze, and update the score if it
+ * does. Note that we don't analyze TOAST tables and pg_statistic.
+ */
+ if (anltuples > anlthresh &&
+ relid != StatisticRelationId &&
+ classForm->relkind != RELKIND_TOASTVALUE)
+ {
+ *doanalyze = true;
+ *score = Max(*score, (double) anltuples / Max(anlthresh, 1));
+ }
+ }
}
/*
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ac2da4c98cf..bb977229f75 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3007,6 +3007,7 @@ TableScanDesc
TableScanDescData
TableSpaceCacheEntry
TableSpaceOpts
+TableToProcess
TablespaceList
TablespaceListCell
TapeBlockTrailer
--
2.39.5 (Apple Git-154)
view thread (150+ messages) latest in thread
Message-ID: <aQI39ln_jZ8qorLE@nathan>
Permalink: ../aQI39ln_jZ8qorLE@nathan/
Also on: postgresql.org/message-id/aQI39ln_jZ8qorLE@nathan
reply
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-hackers@postgresql.org
Cc: nathandbossart@gmail.com, samimseih@gmail.com, dgrowleyml@gmail.com, robertmhaas@gmail.com, schneider@ardentperf.com
Subject: Re: another autovacuum scheduling thread
In-Reply-To: <aQI39ln_jZ8qorLE@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox