On Tue, May 11, 2021 at 04:42:27PM +0900, Michael Paquier wrote: > Whatever the solution chosen, the thing I can see we agree on here is > that we need to do something, at least in the shape of an on/off > switch to have an escape path in case of problems. Peter, could we > get something by beta1 for that? FWIW, I would use a float GUC to > control that, and not a boolean switch, but I am just one voice here, > and that's not a feature I worked on.
So, I have been thinking more about this item, and a boolean switch still sounded weird to me, so attached is a patch to have two GUCs, one for manual VACUUM and autovacuum like any other parameters, to control this behavior, with a default set at 2% of the number of relation pages with dead items needed to do the index cleanup work. Even if we switch the parameter type used here, the easiest and most consistent way to pass down the parameter is just to use VacuumParams set within ExecVacuum() and the autovacuum code path. The docs need more work, I guess. Thoughts? -- Michael
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index cb27257bb6..9c3b9c5e1f 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -221,6 +221,10 @@ typedef struct VacuumParams VacOptTernaryValue truncate; /* Truncate empty pages at the end, * default value depends on reloptions */ + double cleanup_index_scale_factor; /* Minimum fraction of relation pages + * with dead items required for index + * cleanup */ + /* * The number of parallel vacuum workers. 0 by default which means choose * based on the number of indexes. -1 indicates parallel vacuum is diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index 95202d37af..9356b9c5b2 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -256,6 +256,8 @@ extern int VacuumCostPageDirty; extern int VacuumCostLimit; extern double VacuumCostDelay; +extern double VacuumCleanupIndexScale; + extern int64 VacuumPageHit; extern int64 VacuumPageMiss; extern int64 VacuumPageDirty; diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index aacdd0f575..24cfbdad15 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -41,6 +41,7 @@ extern int autovacuum_freeze_max_age; extern int autovacuum_multixact_freeze_max_age; extern double autovacuum_vac_cost_delay; extern int autovacuum_vac_cost_limit; +extern double autovacuum_cleanup_index_scale; /* autovacuum launcher PID, only valid when worker is shutting down */ extern int AutovacuumLauncherPid; diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h index 6b40f1eeb8..30e69a9640 100644 --- a/src/include/utils/guc_tables.h +++ b/src/include/utils/guc_tables.h @@ -84,6 +84,7 @@ enum config_group STATS_MONITORING, STATS_COLLECTOR, AUTOVACUUM, + VACUUM_INDEX, CLIENT_CONN_STATEMENT, CLIENT_CONN_LOCALE, CLIENT_CONN_PRELOAD, diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 17519a970f..8871fb8758 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -103,12 +103,6 @@ #define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */ #define VACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */ -/* - * Threshold that controls whether we bypass index vacuuming and heap - * vacuuming as an optimization - */ -#define BYPASS_THRESHOLD_PAGES 0.02 /* i.e. 2% of rel_pages */ - /* * When a table is small (i.e. smaller than this), save cycles by avoiding * repeated failsafe checks @@ -407,7 +401,8 @@ static void lazy_scan_prune(LVRelState *vacrel, Buffer buf, BlockNumber blkno, Page page, GlobalVisState *vistest, LVPagePruneState *prunestate); -static void lazy_vacuum(LVRelState *vacrel, bool onecall); +static void lazy_vacuum(LVRelState *vacrel, VacuumParams *params, + bool onecall); static bool lazy_vacuum_all_indexes(LVRelState *vacrel); static void lazy_vacuum_heap_rel(LVRelState *vacrel); static int lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno, @@ -1149,7 +1144,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive) } /* Remove the collected garbage tuples from table and indexes */ - lazy_vacuum(vacrel, false); + lazy_vacuum(vacrel, params, false); have_vacuumed_indexes = true; /* @@ -1567,7 +1562,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive) /* If any tuples need to be deleted, perform final vacuum cycle */ if (dead_tuples->num_tuples > 0) - lazy_vacuum(vacrel, !have_vacuumed_indexes); + lazy_vacuum(vacrel, params, !have_vacuumed_indexes); /* * Vacuum the remainder of the Free Space Map. We must do this whether or @@ -2052,7 +2047,7 @@ retry: * wraparound. */ static void -lazy_vacuum(LVRelState *vacrel, bool onecall) +lazy_vacuum(LVRelState *vacrel, VacuumParams *params, bool onecall) { bool do_bypass_optimization; @@ -2119,7 +2114,7 @@ lazy_vacuum(LVRelState *vacrel, bool onecall) * The discrepancy should be negligible. If this optimization is ever * expanded to cover more cases then this may need to be reconsidered. */ - threshold = (double) vacrel->rel_pages * BYPASS_THRESHOLD_PAGES; + threshold = (double) vacrel->rel_pages * params->cleanup_index_scale_factor; do_bypass_optimization = (vacrel->lpdead_item_pages < threshold && vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L)); diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index d549d0d86f..55c458da07 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -250,6 +250,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) /* user-invoked vacuum never uses this parameter */ params.log_min_duration = -1; + /* set scale factor for index cleanup based on GUC */ + params.cleanup_index_scale_factor = VacuumCleanupIndexScale; + /* Now go through the common routine */ vacuum(vacstmt->rels, ¶ms, NULL, isTopLevel); } diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index d516df0ac5..df9d52a2d2 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -124,6 +124,7 @@ int autovacuum_anl_thresh; double autovacuum_anl_scale; int autovacuum_freeze_max_age; int autovacuum_multixact_freeze_max_age; +double autovacuum_cleanup_index_scale = 0.02; double autovacuum_vac_cost_delay; int autovacuum_vac_cost_limit; @@ -2922,6 +2923,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, int vac_cost_limit; double vac_cost_delay; int log_min_duration; + int cleanup_index_scale_factor; /* * Calculate the vacuum cost parameters and the freeze ages. If there @@ -2968,6 +2970,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, ? avopts->multixact_freeze_table_age : default_multixact_freeze_table_age; + cleanup_index_scale_factor = autovacuum_cleanup_index_scale >= 0 ? + autovacuum_cleanup_index_scale : VacuumCostDelay; + tab = palloc(sizeof(autovac_table)); tab->at_relid = relid; tab->at_sharedrel = classForm->relisshared; @@ -2986,6 +2991,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age; tab->at_params.is_wraparound = wraparound; tab->at_params.log_min_duration = log_min_duration; + tab->at_params.cleanup_index_scale_factor = cleanup_index_scale_factor; tab->at_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; tab->at_relname = NULL; diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c index 381d9e548d..953fb2ae08 100644 --- a/src/backend/utils/init/globals.c +++ b/src/backend/utils/init/globals.c @@ -143,6 +143,11 @@ int VacuumCostPageMiss = 2; int VacuumCostPageDirty = 20; int VacuumCostLimit = 200; double VacuumCostDelay = 0; +/* + * Fraction number that controls whether we bypass index vacuuming and heap + * vacuuming as an optimization. + */ +double VacuumCleanupIndexScale = 0.02; /* i.e. 2% of rel_pages */ int64 VacuumPageHit = 0; int64 VacuumPageMiss = 0; diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index eb7f7181e4..3999bbeddb 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -759,6 +759,8 @@ const char *const config_group_names[] = gettext_noop("Statistics / Query and Index Statistics Collector"), /* AUTOVACUUM */ gettext_noop("Autovacuum"), + /* VACUUM_INDEX */ + gettext_noop("Vacuum / Index"), /* CLIENT_CONN_STATEMENT */ gettext_noop("Client Connection Defaults / Statement Behavior"), /* CLIENT_CONN_LOCALE */ @@ -3739,6 +3741,26 @@ static struct config_real ConfigureNamesReal[] = NULL, NULL, NULL }, + { + {"vacuum_cleanup_index_scale_factor", PGC_SIGHUP, VACUUM_INDEX, + gettext_noop("Fraction of relation pages, with at least one dead item, required to clean up indexes."), + NULL + }, + &VacuumCleanupIndexScale, + 0.02, 0.0, 0.05, + NULL, NULL, NULL + }, + + { + {"autovacuum_cleanup_index_scale_factor", PGC_SIGHUP, AUTOVACUUM, + gettext_noop("Fraction of relation pages, with at least one dead item, required to clean up indexes, for autovacuum."), + NULL + }, + &autovacuum_cleanup_index_scale, + -1, -1, 0.05, + NULL, NULL, NULL + }, + { {"autovacuum_vacuum_scale_factor", PGC_SIGHUP, AUTOVACUUM, gettext_noop("Number of tuple updates or deletes prior to vacuum as a fraction of reltuples."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index efde01ee56..d9b8d1ad42 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -173,6 +173,13 @@ #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 1-10000 credits +# - Vacuum / Index + +#vacuum_cleanup_index_scale_factor = 0.02 # Minimum fraction of relation + # pages with at least one dead item + # needed to trigger index cleanup during + # manual VACUUM. Default is 2%. + # - Background Writer - #bgwriter_delay = 200ms # 10-10000ms between rounds @@ -642,6 +649,11 @@ #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit +#autovacuum_cleanup_index_scale_factor = -1 # Minimum fraction of relation + # pages with at least one dead item + # needed to trigger index cleanup, + # -1 means use + # vacuum_cleanup_index_scale_factor #------------------------------------------------------------------------------ diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 45bd1f1b7e..ac77727564 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2241,6 +2241,38 @@ include_dir 'conf.d' </note> </sect2> + <sect2 id="runtime-config-vacuum-index"> + <title>Vacuum parameters for Indexes</title> + <para> + During the execution of <xref linkend="sql-vacuum"/> + and <xref linkend="sql-analyze"/> + commands, this controls the execution of vacuum operations + performed on indexes of the relation worked on. + </para> + + <variablelist> + <varlistentry id="guc-vacuum-cleanup-index-scale-factor" xreflabel="vacuum_cleanup_index_scale_factor"> + <term><varname>vacuum_cleanup_index_scale_factor</varname> (<type>floating point</type>) + <indexterm> + <primary><varname>vacuum_cleanup_index_scale_factor</varname></primary> + <secondary>configuration parameter</secondary> + </indexterm> + </term> + <listitem> + <para> + Specifies the minimum percentage of relation pages required to have + at least one dead item to trigger index cleanup in + <command>VACUUM</command> operations. The default is + <literal>0.02</literal>, meaning that at least 2% of the relation + pages need to have at least one dead item to trigger the cleanup + This parameter can only be set in the <filename>postgresql.conf</filename> + file or on the server command line. + </para> + </listitem> + </varlistentry> + </variablelist> + </sect2> + <sect2 id="runtime-config-resource-background-writer"> <title>Background Writer</title> @@ -8001,6 +8033,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </listitem> </varlistentry> + <varlistentry id="guc-autovacuum--cleanup-index-scale-factor" xreflabel="autovacuum_cleanup_index_scale_factor"> + <term><varname>autovacuum_cleanup_index_scale_factor</varname> (<type>floating point</type>) + <indexterm> + <primary><varname>autovacuum_cleanup_index_scale_factor</varname></primary> + <secondary>configuration parameter</secondary> + </indexterm> + </term> + <listitem> + <para> + Specifies the minimum percentage of relation pages required to have + at least one dead item to trigger index cleanup in automatic + <command>VACUUM</command> operations. If <literal>-1</literal> is + specified (which is the default), the regular + <xref linkend="guc-vacuum-cleanup-index-scale-factor"/> value will be used. + This parameter can only be set in the <filename>postgresql.conf</filename> + file or on the server command line. + </para> + </listitem> + </varlistentry> + </variablelist> </sect1>
signature.asc
Description: PGP signature