diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index c45979dee4..b7383a7136 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6250,6 +6250,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + autovacuum_table_priority (integer) + + autovacuum_table_priority configuration parameter + + + + + Specifies the priority of the table in automatic + VACUUM operations. 0 by default, bigger value gives + more priority. + + + + diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 46276ceff1..2476dfe943 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -291,6 +291,15 @@ static relopt_int intRelOpts[] = }, -1, -1, INT_MAX }, + { + { + "autovacuum_table_priority", + "Sets the priority of the table in autovacuum process", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + ShareUpdateExclusiveLock + }, + 0, INT_MIN, INT_MAX + }, { { "toast_tuple_target", @@ -1353,6 +1362,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, multixact_freeze_table_age)}, {"log_autovacuum_min_duration", RELOPT_TYPE_INT, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, log_min_duration)}, + {"autovacuum_table_priority", RELOPT_TYPE_INT, + offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, priority)}, {"toast_tuple_target", RELOPT_TYPE_INT, offsetof(StdRdOptions, toast_tuple_target)}, {"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL, diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 702f8d8188..174de4a08c 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -174,15 +174,22 @@ typedef struct avw_dbase PgStat_StatDBEntry *adw_entry; } avw_dbase; -/* struct to keep track of tables to vacuum and/or analyze, in 1st pass */ +/* struct for vacuumed or analyzed relation */ typedef struct av_relation +{ + Oid ar_relid; + int ar_priority; /* bigger- more important, used for sorting */ +} av_relation; + +/* struct to keep track of toast tables to vacuum and/or analyze, in 1st pass */ +typedef struct av_toastrelation { Oid ar_toastrelid; /* hash key - must be first */ Oid ar_relid; bool ar_hasrelopts; AutoVacOpts ar_reloptions; /* copy of AutoVacOpts from the main table's * reloptions, or NULL if none */ -} av_relation; +} av_toastrelation; /* struct to keep track of tables to vacuum and/or analyze, after rechecking */ typedef struct autovac_table @@ -1919,6 +1926,16 @@ get_database_list(void) return dblist; } +/* qsort comparator for av_relation, using priority */ +static int +autovac_comparator(const void *a, const void *b) +{ + av_relation *r1 = (av_relation *) lfirst(*(ListCell **) a); + av_relation *r2 = (av_relation *) lfirst(*(ListCell **) b); + + return r2->ar_priority - r1->ar_priority; +} + /* * Process a database table-by-table * @@ -1932,7 +1949,7 @@ do_autovacuum(void) HeapTuple tuple; HeapScanDesc relScan; Form_pg_database dbForm; - List *table_oids = NIL; + List *optables = NIL; List *orphan_oids = NIL; HASHCTL ctl; HTAB *table_toast_map; @@ -2021,7 +2038,7 @@ do_autovacuum(void) /* create hash table for toast <-> main relid mapping */ MemSet(&ctl, 0, sizeof(ctl)); ctl.keysize = sizeof(Oid); - ctl.entrysize = sizeof(av_relation); + ctl.entrysize = sizeof(av_toastrelation); table_toast_map = hash_create("TOAST to main relid map", 100, @@ -2101,9 +2118,14 @@ do_autovacuum(void) effective_multixact_freeze_max_age, &dovacuum, &doanalyze, &wraparound); - /* Relations that need work are added to table_oids */ + /* Relations that need work are added to optables */ if (dovacuum || doanalyze) - table_oids = lappend_oid(table_oids, relid); + { + av_relation *rel = (av_relation *) palloc(sizeof(av_relation)); + rel->ar_relid = relid; + rel->ar_priority = relopts != NULL ? relopts->priority : 0; + optables = lappend(optables, rel); + } /* * Remember TOAST associations for the second pass. Note: we must do @@ -2112,7 +2134,7 @@ do_autovacuum(void) */ if (OidIsValid(classForm->reltoastrelid)) { - av_relation *hentry; + av_toastrelation *hentry; bool found; hentry = hash_search(table_toast_map, @@ -2168,7 +2190,7 @@ do_autovacuum(void) relopts = extract_autovac_opts(tuple, pg_class_desc); if (relopts == NULL) { - av_relation *hentry; + av_toastrelation *hentry; bool found; hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); @@ -2186,7 +2208,12 @@ do_autovacuum(void) /* ignore analyze for toast tables */ if (dovacuum) - table_oids = lappend_oid(table_oids, relid); + { + av_relation *rel = (av_relation *) palloc(sizeof(av_relation)); + rel->ar_relid = relid; + rel->ar_priority = relopts != NULL ? relopts->priority : 0; + optables = lappend(optables, rel); + } } heap_endscan(relScan); @@ -2282,6 +2309,9 @@ do_autovacuum(void) MemoryContextSwitchTo(AutovacMemCxt); } + /* Sort tables by autovacuum priority */ + optables = list_qsort(optables, autovac_comparator); + /* * Create a buffer access strategy object for VACUUM to use. We want to * use the same one across all the vacuum operations we perform, since the @@ -2300,9 +2330,9 @@ do_autovacuum(void) /* * Perform operations on collected tables. */ - foreach(cell, table_oids) + foreach(cell, optables) { - Oid relid = lfirst_oid(cell); + Oid relid = ((av_relation *) lfirst(cell))->ar_relid; autovac_table *tab; bool skipit; int stdVacuumCostDelay; @@ -2437,6 +2467,9 @@ do_autovacuum(void) if (!tab->at_relname || !tab->at_nspname || !tab->at_datname) goto deleted; + ereport(DEBUG1, (errmsg("automatic vacuum of table \"%s.%s.%s\"", + tab->at_datname, tab->at_nspname, tab->at_relname))); + /* * We will abort vacuuming the current table if something errors out, * and continue with the next one in schedule; in particular, this @@ -2787,7 +2820,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, if (classForm->relkind == RELKIND_TOASTVALUE && avopts == NULL && table_toast_map != NULL) { - av_relation *hentry; + av_toastrelation *hentry; bool found; hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index aa8add544a..106cdf49ca 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -269,6 +269,7 @@ typedef struct AutoVacOpts int multixact_freeze_max_age; int multixact_freeze_table_age; int log_min_duration; + int priority; float8 vacuum_scale_factor; float8 analyze_scale_factor; } AutoVacOpts; diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out index c4107d5ca1..1f154baddf 100644 --- a/src/test/regress/expected/reloptions.out +++ b/src/test/regress/expected/reloptions.out @@ -50,23 +50,25 @@ CREATE TABLE reloptions_test2(i INT) WITH (fillfactor); ERROR: invalid value for integer option "fillfactor": true -- Simple ALTER TABLE ALTER TABLE reloptions_test SET (fillfactor=31, - autovacuum_analyze_scale_factor = 0.3); + autovacuum_analyze_scale_factor = 0.3, + autovacuum_table_priority = 1 +); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; - reloptions ------------------------------------------------------------------------------- - {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3} + reloptions +---------------------------------------------------------------------------------------------------------- + {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3,autovacuum_table_priority=1} (1 row) -- Set boolean option to true without specifying value ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; - reloptions ------------------------------------------------------------------------------ - {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32} + reloptions +--------------------------------------------------------------------------------------------------------- + {autovacuum_analyze_scale_factor=0.3,autovacuum_table_priority=1,autovacuum_enabled=true,fillfactor=32} (1 row) -- Check that RESET works well -ALTER TABLE reloptions_test RESET (fillfactor); +ALTER TABLE reloptions_test RESET (fillfactor, autovacuum_table_priority); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; reloptions --------------------------------------------------------------- diff --git a/src/test/regress/sql/reloptions.sql b/src/test/regress/sql/reloptions.sql index c9119fd863..c503e3aa04 100644 --- a/src/test/regress/sql/reloptions.sql +++ b/src/test/regress/sql/reloptions.sql @@ -32,7 +32,9 @@ CREATE TABLE reloptions_test2(i INT) WITH (fillfactor); -- Simple ALTER TABLE ALTER TABLE reloptions_test SET (fillfactor=31, - autovacuum_analyze_scale_factor = 0.3); + autovacuum_analyze_scale_factor = 0.3, + autovacuum_table_priority = 1 +); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; -- Set boolean option to true without specifying value @@ -40,7 +42,7 @@ ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; -- Check that RESET works well -ALTER TABLE reloptions_test RESET (fillfactor); +ALTER TABLE reloptions_test RESET (fillfactor, autovacuum_table_priority); SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass; -- Resetting all values causes the column to become null