Hello,
I would like to suggest a new parameter, autovacuum_max_threshold, which
would set an upper limit on the number of tuples to delete/update/insert
prior to vacuum/analyze.
A good default might be 500000.
The idea would be to replace the following calculation :
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
with this one :
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples / (1
+ vac_scale_factor * reltuples / autovacuum_max_threshold)
(and the same for the others, vacinsthresh and anlthresh).
The attached graph plots vacthresh against pgclass.reltuples, with
default settings :
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
and
autovacuum_max_threshold = 500000 (the suggested default)
Thus, for small tables, vacthresh is only slightly smaller than 0.2 *
pgclass.reltuples, but it grows towards 500000 when reltuples → ∞
The idea is to reduce the need for autovacuum tuning.
The attached (draft) patch further illustrates the idea.
My guess is that a similar proposal has already been submitted... and
rejected 🙂 If so, I'm very sorry for the useless noise.
Best regards,
Frédéric
From 9027d857e3426f327a2a5f61aec11a7604bb48a9 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yh...@dalibo.com>
Date: Fri, 19 Apr 2024 14:05:37 +0200
Subject: [PATCH] Add new GUC autovacuum_max_threshold
---
src/backend/access/common/reloptions.c | 11 +++++++++++
src/backend/postmaster/autovacuum.c | 18 +++++++++++++++---
src/backend/utils/misc/guc_tables.c | 9 +++++++++
src/include/postmaster/autovacuum.h | 1 +
src/include/utils/rel.h | 1 +
5 files changed, 37 insertions(+), 3 deletions(-)
diff --git a/src/backend/access/common/reloptions.c
b/src/backend/access/common/reloptions.c
index 469de9bb49..11a6423aff 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -233,6 +233,15 @@ static relopt_int intRelOpts[] =
},
-1, 0, INT_MAX
},
+ {
+ {
+ "autovacuum_max_threshold",
+ "Maximum number of tuple XXX prior to vacuum/analyze",
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
+ },
+ -1, 0, INT_MAX
+ },
{
{
"autovacuum_vacuum_insert_threshold",
@@ -1845,6 +1854,8 @@ default_reloptions(Datum reloptions, bool validate,
relopt_kind kind)
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts,
enabled)},
{"autovacuum_vacuum_threshold", RELOPT_TYPE_INT,
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts,
vacuum_threshold)},
+ {"autovacuum_max_threshold", RELOPT_TYPE_INT,
+ offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts,
vacuum_max_threshold)},
{"autovacuum_vacuum_insert_threshold", RELOPT_TYPE_INT,
offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts,
vacuum_ins_threshold)},
{"autovacuum_analyze_threshold", RELOPT_TYPE_INT,
diff --git a/src/backend/postmaster/autovacuum.c
b/src/backend/postmaster/autovacuum.c
index 7dd9345c61..e453c7c824 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -119,6 +119,7 @@ int autovacuum_max_workers;
int autovacuum_work_mem = -1;
int autovacuum_naptime;
int autovacuum_vac_thresh;
+int autovacuum_max_thresh;
double autovacuum_vac_scale;
int autovacuum_vac_ins_thresh;
double autovacuum_vac_ins_scale;
@@ -3017,6 +3018,12 @@ recheck_relation_needs_vacanalyze(Oid relid,
*doanalyze = false;
}
+#define COMPUTE_TRESHOLD(res, scale_factor, base_thresh) \
+do { \
+ res = (float4) base_thresh + scale_factor * reltuples / \
+ (1 + scale_factor * reltuples / vac_max_thresh); \
+} while(0)
+
/*
* relation_needs_vacanalyze
*
@@ -3071,6 +3078,7 @@ relation_needs_vacanalyze(Oid relid,
/* constants from reloptions or GUC variables */
int vac_base_thresh,
+ vac_max_thresh,
vac_ins_base_thresh,
anl_base_thresh;
float4 vac_scale_factor,
@@ -3111,6 +3119,10 @@ relation_needs_vacanalyze(Oid relid,
? relopts->vacuum_threshold
: autovacuum_vac_thresh;
+ vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= 0)
+ ? relopts->vacuum_max_threshold
+ : autovacuum_max_thresh;
+
vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >=
0)
? relopts->vacuum_ins_scale_factor
: autovacuum_vac_ins_scale;
@@ -3181,9 +3193,9 @@ relation_needs_vacanalyze(Oid relid,
if (reltuples < 0)
reltuples = 0;
- vacthresh = (float4) vac_base_thresh + vac_scale_factor *
reltuples;
- vacinsthresh = (float4) vac_ins_base_thresh +
vac_ins_scale_factor * reltuples;
- anlthresh = (float4) anl_base_thresh + anl_scale_factor *
reltuples;
+ COMPUTE_TRESHOLD(vacthresh, vac_scale_factor,
vac_base_thresh);
+ COMPUTE_TRESHOLD(vacinsthresh, vac_ins_scale_factor,
vac_ins_base_thresh);
+ COMPUTE_TRESHOLD(anlthresh, anl_scale_factor,
anl_base_thresh);
/*
* Note that we don't need to take special consideration for
stat
diff --git a/src/backend/utils/misc/guc_tables.c
b/src/backend/utils/misc/guc_tables.c
index 8698c10f9c..3d90a5e4f7 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -3196,6 +3196,15 @@ struct config_int ConfigureNamesInt[] =
50, 0, INT_MAX,
NULL, NULL, NULL
},
+ {
+ {"autovacuum_max_threshold", PGC_SIGHUP, AUTOVACUUM,
+ gettext_noop("Maximum number of tuple XXX prior to
vacuum/analyze."),
+ NULL
+ },
+ &autovacuum_max_thresh,
+ 500000, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
{
{"autovacuum_vacuum_insert_threshold", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Minimum number of tuple inserts prior to
vacuum, or -1 to disable insert vacuums."),
diff --git a/src/include/postmaster/autovacuum.h
b/src/include/postmaster/autovacuum.h
index 65afd1ea1e..673cd4062b 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -32,6 +32,7 @@ extern PGDLLIMPORT int autovacuum_max_workers;
extern PGDLLIMPORT int autovacuum_work_mem;
extern PGDLLIMPORT int autovacuum_naptime;
extern PGDLLIMPORT int autovacuum_vac_thresh;
+extern PGDLLIMPORT int autovacuum_max_thresh;
extern PGDLLIMPORT double autovacuum_vac_scale;
extern PGDLLIMPORT int autovacuum_vac_ins_thresh;
extern PGDLLIMPORT double autovacuum_vac_ins_scale;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 1426a353cd..b280acc5e9 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -308,6 +308,7 @@ typedef struct AutoVacOpts
{
bool enabled;
int vacuum_threshold;
+ int vacuum_max_threshold;
int vacuum_ins_threshold;
int analyze_threshold;
int vacuum_cost_limit;
--
2.39.2
