diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index efac9eb..9e4e979 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -4730,6 +4730,33 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + autovacuum_multixact_freeze_max_age (integer) + + autovacuum_multixact_freeze_max_age configuration parameter + + + + Specifies the maximum age (in Multixacts) that a table's + pg_class.relminmxid field can + attain before a VACUUM operation is forced to + prevent Multixact ID wraparound within the table. + Note that the system will launch autovacuum processes to + prevent wraparound even when autovacuum is otherwise disabled. + + + + Vacuum also allows removal of old files from the + pg_multixact/members and pg_multixact/offsets + subdirectories, which is why the default is a relatively low + 50 million transactions. + This parameter can only be set at server start, but the setting + can be reduced for individual tables by changing storage parameters. + For more information see . + + + + autovacuum_vacuum_cost_delay (integer) @@ -5148,6 +5175,21 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + vacuum_multixact_freeze_table_age (integer) + + vacuum_multixact_freeze_table_age configuration parameter + + + + VACUUM performs a whole-table scan if the table's + pg_class.relminmxid field has reached + the age specified by this setting. The default is 5 million multixacts. + For more information see . + + + + vacuum_freeze_min_age (integer) @@ -5169,6 +5211,22 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + vacuum_multixact_freeze_min_age (integer) + + vacuum_multixact_freeze_min_age configuration parameter + + + + Specifies the cutoff age (in multixacts) that VACUUM + should use to decide whether to replace multixact IDs with a newer + transaction ID or multixact ID while scanning a table. The default + is 1 million multixacts. + For more information see . + + + + bytea_output (enum) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index ae6456b..7130cd8 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -108,7 +108,8 @@ To protect against loss of very old data due to - transaction ID wraparound. + transaction ID wraparound or + Multixact ID wraparound. @@ -599,6 +600,41 @@ HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb". page for details about using a single-user backend. + + Multixacts and Wraparound + + + Multixact ID + wraparound + + + + + Similar to transaction IDs, Multixact IDs are implemented as a 32-bit + counter and corresponding storage which requires careful aging management, + storage cleanup, and wraparound handling. Multixacts are used to implement + row locking by multiple transactions: since there is limited space in the + tuple header to store lock information, that information is stored separately + and only a reference to it is in the xmax field in the tuple + header. + + + + As with transaction IDs, VACUUM is in charge of removing old + values. Each VACUUM run sets + pg_class.relminmxid indicating the oldest + possible value still stored in that table; every time this value is older + than , a full-table + scan is forced. During a table scan, either partial or full-table, any + multixact older than + is replaced by a different value, which can be the zero value, a single + transaction ID, or a newer multixact; full-table scans enable advancing + the value for that table. Eventually, as all tables in all databases are + scanned and their oldest multixact values are advanced, on-disk storage + for older multixacts can be removed. + + diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 26eca67..38134dd 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -981,7 +981,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Custom parameter. Note that autovacuum will ignore attempts to set a per-table - autovacuum_freeze_min_age larger than the half system-wide + autovacuum_freeze_min_age larger than half the system-wide setting. @@ -1010,6 +1010,43 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI + + autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer) + + + Custom parameter. + Note that autovacuum will ignore attempts to set a per-table + autovacuum_multixact_freeze_min_age larger than half the + system-wide + setting. + + + + + + autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer) + + + Custom parameter. Note + that autovacuum will ignore attempts to set a per-table + autovacuum_multixact_freeze_max_age larger than the + system-wide setting (it can only be set smaller). Note that while you + can set autovacuum_multixact_freeze_max_age very small, + or even zero, this is usually unwise since it will force frequent + vacuuming. + + + + + + autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age (integer) + + + Custom parameter. + + + + diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index c439702..d7c3e11 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -164,6 +164,14 @@ static relopt_int intRelOpts[] = }, { { + "autovacuum_multixact_freeze_min_age", + "Minimum MultiXact age at which VACUUM should freeze a row MultiXact's, for autovacuum", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST + }, + -1, 0, 1000000000 + }, + { + { "autovacuum_freeze_max_age", "Age at which to autovacuum a table to prevent transaction ID wraparound", RELOPT_KIND_HEAP | RELOPT_KIND_TOAST @@ -172,11 +180,26 @@ static relopt_int intRelOpts[] = }, { { + "autovacuum_multixact_freeze_max_age", + "MultiXact age at which to autovacuum a table to prevent MultiXact wraparound", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST + }, + -1, 100000000, 2000000000 + }, + { + { "autovacuum_freeze_table_age", "Age at which VACUUM should perform a full table sweep to replace old Xid values with FrozenXID", RELOPT_KIND_HEAP | RELOPT_KIND_TOAST }, -1, 0, 2000000000 }, + { + { + "autovacuum_multixact_freeze_table_age", + "Age of MultiXact at which VACUUM should perform a full table sweep to replace old MultiXact values with newer ones", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST + }, -1, 0, 2000000000 + }, /* list terminator */ {{NULL}} }; diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 1cdb220..c903f0e 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -64,9 +64,13 @@ typedef struct static void rebuild_relation(Relation OldHeap, Oid indexOid, - int freeze_min_age, int freeze_table_age, bool verbose); + int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, int multixact_freeze_table_age, + bool verbose); static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, - int freeze_min_age, int freeze_table_age, bool verbose, + int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, int multixact_freeze_table_age, + bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti); static List *get_tables_to_cluster(MemoryContext cluster_context); @@ -179,7 +183,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel) * Do the job. We use a -1 freeze_min_age to avoid having CLUSTER * freeze tuples earlier than a plain VACUUM would. */ - cluster_rel(tableOid, indexOid, false, stmt->verbose, -1, -1); + cluster_rel(tableOid, indexOid, false, stmt->verbose, -1, -1, -1, -1); } else { @@ -230,7 +234,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel) PushActiveSnapshot(GetTransactionSnapshot()); /* Do the job. As above, use a -1 freeze_min_age. */ cluster_rel(rvtc->tableOid, rvtc->indexOid, true, stmt->verbose, - -1, -1); + -1, -1, -1, -1); PopActiveSnapshot(); CommitTransactionCommand(); } @@ -262,7 +266,8 @@ cluster(ClusterStmt *stmt, bool isTopLevel) */ void cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose, - int freeze_min_age, int freeze_table_age) + int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, int multixact_freeze_table_age) { Relation OldHeap; @@ -407,6 +412,7 @@ cluster_rel(Oid tableOid, Oid indexOid, bool recheck, bool verbose, /* rebuild_relation does all the dirty work */ rebuild_relation(OldHeap, indexOid, freeze_min_age, freeze_table_age, + multixact_freeze_min_age, multixact_freeze_table_age, verbose); /* NB: rebuild_relation does heap_close() on OldHeap */ @@ -566,7 +572,9 @@ mark_index_clustered(Relation rel, Oid indexOid, bool is_internal) */ static void rebuild_relation(Relation OldHeap, Oid indexOid, - int freeze_min_age, int freeze_table_age, bool verbose) + int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, int multixact_freeze_table_age, + bool verbose) { Oid tableOid = RelationGetRelid(OldHeap); Oid tableSpace = OldHeap->rd_rel->reltablespace; @@ -591,7 +599,9 @@ rebuild_relation(Relation OldHeap, Oid indexOid, /* Copy the heap data into the new table in the desired order */ copy_heap_data(OIDNewHeap, tableOid, indexOid, - freeze_min_age, freeze_table_age, verbose, + freeze_min_age, freeze_table_age, + multixact_freeze_min_age, multixact_freeze_table_age, + verbose, &swap_toast_by_content, &frozenXid, &cutoffMulti); /* @@ -733,7 +743,9 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace) */ static void copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, - int freeze_min_age, int freeze_table_age, bool verbose, + int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, int multixact_freeze_table_age, + bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti) { @@ -849,6 +861,7 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, * compute xids used to freeze and weed out dead tuples. */ vacuum_set_xid_limits(freeze_min_age, freeze_table_age, + multixact_freeze_min_age, multixact_freeze_table_age, OldHeap->rd_rel->relisshared, &OldestXmin, &FreezeXid, NULL, &MultiXactCutoff, NULL); diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index d50333f..b530363 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -55,6 +55,8 @@ */ int vacuum_freeze_min_age; int vacuum_freeze_table_age; +int vacuum_multixact_freeze_min_age; +int vacuum_multixact_freeze_table_age; /* A few variables that don't seem worth passing around as parameters */ @@ -398,6 +400,8 @@ get_rel_oids(Oid relid, const RangeVar *vacrel) void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, + int multixact_freeze_table_age, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit, @@ -406,9 +410,11 @@ vacuum_set_xid_limits(int freeze_min_age, MultiXactId *mxactFullScanLimit) { int freezemin; + int mxid_freezemin; TransactionId limit; TransactionId safeLimit; MultiXactId mxactLimit; + MultiXactId safeMxactLimit; /* * We can always ignore processes running lazy vacuum. This is because we @@ -462,13 +468,36 @@ vacuum_set_xid_limits(int freeze_min_age, *freezeLimit = limit; /* - * simplistic MultiXactId removal limit: use the same policy as for - * freezing Xids (except we use the oldest known mxact instead of the - * current next value). + * Determine the minimum multixact freeze age to use: as specified by + * caller, or vacuum_multixact_freeze_min_age, but in any case not more + * than half autovacuum_multixact_freeze_max_age, so that autovacuums to + * prevent MultiXact wraparound won't occur too frequently. */ - mxactLimit = GetOldestMultiXactId() - freezemin; + mxid_freezemin = multixact_freeze_min_age; + if (mxid_freezemin < 0) + mxid_freezemin = vacuum_multixact_freeze_min_age; + mxid_freezemin = Min(mxid_freezemin, + autovacuum_multixact_freeze_max_age / 2); + Assert(mxid_freezemin >= 0); + + /* compute the cutoff multi, being careful to generate a valid value */ + mxactLimit = GetOldestMultiXactId() - mxid_freezemin; if (mxactLimit < FirstMultiXactId) mxactLimit = FirstMultiXactId; + + safeMxactLimit = + ReadNextMultiXactId() - autovacuum_multixact_freeze_max_age; + if (safeMxactLimit < FirstMultiXactId) + safeMxactLimit = FirstMultiXactId; + + if (MultiXactIdPrecedes(mxactLimit, safeMxactLimit)) + { + ereport(WARNING, + (errmsg("oldest MultiXact is far in the past"), + errhint("Close open transactions with MultiXacts soon to avoid wraparound problems."))); + mxactLimit = safeMxactLimit; + } + *multiXactCutoff = mxactLimit; if (xidFullScanLimit != NULL) @@ -503,7 +532,17 @@ vacuum_set_xid_limits(int freeze_min_age, /* * Compute MultiXactId limit to cause a full-table vacuum, being * careful not to generate an invalid multi. We just copy the logic - * (and limits) from plain XIDs here. + * from plain XIDs here. + */ + freezetable = multixact_freeze_table_age; + if (freezetable < 0) + freezetable = vacuum_multixact_freeze_table_age; + freezetable = Min(freezetable, + autovacuum_multixact_freeze_max_age * 0.95); + Assert(freezetable >= 0); + + /* Compute MultiXact limit causing a full-table vacuum, being careful + * to generate a valid MultiXact value. */ mxactLimit = ReadNextMultiXactId() - freezetable; if (mxactLimit < FirstMultiXactId) @@ -511,6 +550,10 @@ vacuum_set_xid_limits(int freeze_min_age, *mxactFullScanLimit = mxactLimit; } + else + { + Assert(mxactFullScanLimit == NULL); + } } /* @@ -1150,7 +1193,9 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound) /* VACUUM FULL is now a variant of CLUSTER; see cluster.c */ cluster_rel(relid, InvalidOid, false, (vacstmt->options & VACOPT_VERBOSE) != 0, - vacstmt->freeze_min_age, vacstmt->freeze_table_age); + vacstmt->freeze_min_age, vacstmt->freeze_table_age, + vacstmt->multixact_freeze_min_age, + vacstmt->multixact_freeze_table_age); } else lazy_vacuum_rel(onerel, vacstmt, vac_strategy); diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index 402a9e7..9f70def 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -203,6 +203,8 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, vac_strategy = bstrategy; vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age, + vacstmt->multixact_freeze_min_age, + vacstmt->multixact_freeze_table_age, onerel->rd_rel->relisshared, &OldestXmin, &FreezeLimit, &xidFullScanLimit, &MultiXactCutoff, &mxactFullScanLimit); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3feced6..ed22e46 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -8474,6 +8474,8 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose n->options |= VACOPT_VERBOSE; n->freeze_min_age = $3 ? 0 : -1; n->freeze_table_age = $3 ? 0 : -1; + n->multixact_freeze_min_age = $3 ? 0 : -1; + n->multixact_freeze_table_age = $3 ? 0 : -1; n->relation = NULL; n->va_cols = NIL; $$ = (Node *)n; @@ -8488,6 +8490,8 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose n->options |= VACOPT_VERBOSE; n->freeze_min_age = $3 ? 0 : -1; n->freeze_table_age = $3 ? 0 : -1; + n->multixact_freeze_min_age = $3 ? 0 : -1; + n->multixact_freeze_table_age = $3 ? 0 : -1; n->relation = $5; n->va_cols = NIL; $$ = (Node *)n; @@ -8502,6 +8506,8 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose n->options |= VACOPT_VERBOSE; n->freeze_min_age = $3 ? 0 : -1; n->freeze_table_age = $3 ? 0 : -1; + n->multixact_freeze_min_age = $3 ? 0 : -1; + n->multixact_freeze_table_age = $3 ? 0 : -1; $$ = (Node *)n; } | VACUUM '(' vacuum_option_list ')' @@ -8509,9 +8515,17 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose VacuumStmt *n = makeNode(VacuumStmt); n->options = VACOPT_VACUUM | $3; if (n->options & VACOPT_FREEZE) + { n->freeze_min_age = n->freeze_table_age = 0; + n->multixact_freeze_min_age = 0; + n->multixact_freeze_table_age = 0; + } else + { n->freeze_min_age = n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; + } n->relation = NULL; n->va_cols = NIL; $$ = (Node *) n; @@ -8521,9 +8535,17 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose VacuumStmt *n = makeNode(VacuumStmt); n->options = VACOPT_VACUUM | $3; if (n->options & VACOPT_FREEZE) + { n->freeze_min_age = n->freeze_table_age = 0; + n->multixact_freeze_min_age = 0; + n->multixact_freeze_table_age = 0; + } else + { n->freeze_min_age = n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; + } n->relation = $5; n->va_cols = $6; if (n->va_cols != NIL) /* implies analyze */ @@ -8553,6 +8575,8 @@ AnalyzeStmt: n->options |= VACOPT_VERBOSE; n->freeze_min_age = -1; n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; n->relation = NULL; n->va_cols = NIL; $$ = (Node *)n; @@ -8565,6 +8589,8 @@ AnalyzeStmt: n->options |= VACOPT_VERBOSE; n->freeze_min_age = -1; n->freeze_table_age = -1; + n->multixact_freeze_min_age = -1; + n->multixact_freeze_table_age = -1; n->relation = $3; n->va_cols = $4; $$ = (Node *)n; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 38e206d..a6b89f8 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -116,6 +116,7 @@ double autovacuum_vac_scale; int autovacuum_anl_thresh; double autovacuum_anl_scale; int autovacuum_freeze_max_age; +int autovacuum_multixact_freeze_max_age; int autovacuum_vac_cost_delay; int autovacuum_vac_cost_limit; @@ -144,6 +145,8 @@ static MultiXactId recentMulti; /* Default freeze ages to use for autovacuum (varies by database) */ static int default_freeze_min_age; static int default_freeze_table_age; +static int default_multixact_freeze_min_age; +static int default_multixact_freeze_table_age; /* Memory context for long-lived data */ static MemoryContext AutovacMemCxt; @@ -185,6 +188,8 @@ typedef struct autovac_table bool at_doanalyze; int at_freeze_min_age; int at_freeze_table_age; + int at_multixact_freeze_min_age; + int at_multixact_freeze_table_age; int at_vacuum_cost_delay; int at_vacuum_cost_limit; bool at_wraparound; @@ -1129,7 +1134,7 @@ do_start_worker(void) /* Also determine the oldest datminmxid we will consider. */ recentMulti = ReadNextMultiXactId(); - multiForceLimit = recentMulti - autovacuum_freeze_max_age; + multiForceLimit = recentMulti - autovacuum_multixact_freeze_max_age; if (multiForceLimit < FirstMultiXactId) multiForceLimit -= FirstMultiXactId; @@ -1955,11 +1960,15 @@ do_autovacuum(void) { default_freeze_min_age = 0; default_freeze_table_age = 0; + default_multixact_freeze_min_age = 0; + default_multixact_freeze_table_age = 0; } else { default_freeze_min_age = vacuum_freeze_min_age; default_freeze_table_age = vacuum_freeze_table_age; + default_multixact_freeze_min_age = vacuum_multixact_freeze_min_age; + default_multixact_freeze_table_age = vacuum_multixact_freeze_table_age; } ReleaseSysCache(tuple); @@ -2510,6 +2519,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, { int freeze_min_age; int freeze_table_age; + int multixact_freeze_min_age; + int multixact_freeze_table_age; int vac_cost_limit; int vac_cost_delay; @@ -2543,12 +2554,22 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, ? avopts->freeze_table_age : default_freeze_table_age; + multixact_freeze_min_age = (avopts && avopts->multixact_freeze_min_age >= 0) + ? avopts->multixact_freeze_min_age + : default_multixact_freeze_min_age; + + multixact_freeze_table_age = (avopts && avopts->multixact_freeze_table_age >= 0) + ? avopts->multixact_freeze_table_age + : default_multixact_freeze_table_age; + tab = palloc(sizeof(autovac_table)); tab->at_relid = relid; tab->at_dovacuum = dovacuum; tab->at_doanalyze = doanalyze; tab->at_freeze_min_age = freeze_min_age; tab->at_freeze_table_age = freeze_table_age; + tab->at_multixact_freeze_min_age = multixact_freeze_min_age; + tab->at_multixact_freeze_table_age = multixact_freeze_table_age; tab->at_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; tab->at_wraparound = wraparound; diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index a055231..72cf3af 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1907,6 +1907,26 @@ static struct config_int ConfigureNamesInt[] = }, { + {"vacuum_multixact_freeze_min_age", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Minimum age at which VACUUM should freeze a MultiXactId in a table row."), + NULL + }, + &vacuum_multixact_freeze_min_age, + 1000000, 0, 200000000, + NULL, NULL, NULL + }, + + { + {"vacuum_multixact_freeze_table_age", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Multixact age at which VACUUM should scan whole table to freeze tuples."), + NULL + }, + &vacuum_multixact_freeze_table_age, + 5000000, 0, 200000000, + NULL, NULL, NULL + }, + + { {"vacuum_defer_cleanup_age", PGC_SIGHUP, REPLICATION_MASTER, gettext_noop("Number of transactions by which VACUUM and HOT cleanup should be deferred, if any."), NULL @@ -2296,6 +2316,16 @@ static struct config_int ConfigureNamesInt[] = NULL, NULL, NULL }, { + /* ditto */ + {"autovacuum_multixact_freeze_max_age", PGC_POSTMASTER, AUTOVACUUM, + gettext_noop("MultiXact age at which to autovacuum a table to prevent MultiXact wraparound."), + NULL + }, + &autovacuum_multixact_freeze_max_age, + 200000000, 100000000, 2000000000, + NULL, NULL, NULL + }, + { /* see max_connections */ {"autovacuum_max_workers", PGC_POSTMASTER, AUTOVACUUM, gettext_noop("Sets the maximum number of simultaneously running autovacuum worker processes."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 0303ac7..28da736 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -362,12 +362,12 @@ # panic #log_min_error_statement = error # values in order of decreasing detail: - # debug5 + # debug5 # debug4 # debug3 # debug2 # debug1 - # info + # info # notice # warning # error @@ -431,7 +431,7 @@ #track_counts = on #track_io_timing = off #track_functions = none # none, pl, all -#track_activity_query_size = 1024 # (change requires restart) +#track_activity_query_size = 1024 # (change requires restart) #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' @@ -465,6 +465,9 @@ #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) +#autovacuum_multixact_freeze_max_age = 200000000 # maximum Multixact age + # before forced vacuum + # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay @@ -492,6 +495,8 @@ #lock_timeout = 0 # in milliseconds, 0 is disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 +#vacuum_multixact_freeze_min_age = 1000000 +#vacuum_multixact_freeze_table_age = 5000000 #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h index 52ca1d1..9b0a530 100644 --- a/src/include/commands/cluster.h +++ b/src/include/commands/cluster.h @@ -20,7 +20,8 @@ extern void cluster(ClusterStmt *stmt, bool isTopLevel); extern void cluster_rel(Oid tableOid, Oid indexOid, bool recheck, - bool verbose, int freeze_min_age, int freeze_table_age); + bool verbose, int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, int multixact_freeze_table_age); extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid, bool recheck, LOCKMODE lockmode); extern void mark_index_clustered(Relation rel, Oid indexOid, bool is_internal); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 44a3c3b..4bc0f14 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -136,6 +136,8 @@ extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for * PostGIS */ extern int vacuum_freeze_min_age; extern int vacuum_freeze_table_age; +extern int vacuum_multixact_freeze_min_age; +extern int vacuum_multixact_freeze_table_age; /* in commands/vacuum.c */ @@ -156,6 +158,8 @@ extern void vac_update_relstats(Relation relation, TransactionId frozenxid, MultiXactId minmulti); extern void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age, + int multixact_freeze_min_age, + int multixact_freeze_table_age, bool sharedRel, TransactionId *oldestXmin, TransactionId *freezeLimit, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 0eac9fb..d749f70 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2430,6 +2430,8 @@ typedef struct VacuumStmt int options; /* OR of VacuumOption flags */ int freeze_min_age; /* min freeze age, or -1 to use default */ int freeze_table_age; /* age at which to scan whole table */ + int multixact_freeze_min_age; /* min multixact freeze age, or -1 to use default */ + int multixact_freeze_table_age; /* multixact age at which to scan whole table */ RangeVar *relation; /* single table to process, or NULL */ List *va_cols; /* list of column names, or NIL for all */ } VacuumStmt; diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h index e96f07a..70e0566 100644 --- a/src/include/postmaster/autovacuum.h +++ b/src/include/postmaster/autovacuum.h @@ -24,6 +24,7 @@ extern double autovacuum_vac_scale; extern int autovacuum_anl_thresh; extern double autovacuum_anl_scale; extern int autovacuum_freeze_max_age; +extern int autovacuum_multixact_freeze_max_age; extern int autovacuum_vac_cost_delay; extern int autovacuum_vac_cost_limit; diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 58cc3f7..47ae106 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -198,6 +198,9 @@ typedef struct AutoVacOpts int freeze_min_age; int freeze_max_age; int freeze_table_age; + int multixact_freeze_min_age; + int multixact_freeze_max_age; + int multixact_freeze_table_age; float8 vacuum_scale_factor; float8 analyze_scale_factor; } AutoVacOpts;