From 9027d857e3426f327a2a5f61aec11a7604bb48a9 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= 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