From 4ff695b17d32a9c330952192dbc789d31a5e2f5e Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 3 Apr 2015 11:55:50 -0300 Subject: [PATCH] Add log_min_autovacuum_duration per-table option This is useful to control autovacuum log volume, for situations where monitoring only a set of tables is necessary. Author: Michael Paquier Reviewed by: A team led by Naoya Anzai (also including Akira Kurosawa, Taiki Kondo, Huong Dangminh), Fujii Masao. --- doc/src/sgml/ref/create_table.sgml | 16 ++++++++++--- src/backend/access/common/reloptions.c | 10 +++++++++ src/backend/commands/analyze.c | 31 ++++++++++++++------------ src/backend/commands/vacuum.c | 7 ++++-- src/backend/commands/vacuumlazy.c | 8 +++---- src/backend/postmaster/autovacuum.c | 7 ++++++ src/bin/psql/tab-complete.c | 2 ++ src/include/commands/vacuum.h | 5 ++++- src/include/utils/rel.h | 1 + 9 files changed, 63 insertions(+), 24 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 324d59371a..be7ebd5f54 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -881,9 +881,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI toast., which can be used to control the behavior of the table's secondary TOAST table, if any (see for more information about TOAST). - Note that the TOAST table inherits the - autovacuum_* values from its parent table, if there are - no toast.autovacuum_* settings set. + Note that the TOAST table uses the parameter values defined for + the main table, for each parameter applicable to TOAST tables and + for which no value is set in the TOAST table itself. @@ -1060,6 +1060,15 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI + + log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer) + + + Custom parameter. + + + + user_catalog_table (boolean) @@ -1067,6 +1076,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Declare a table as an additional catalog table, e.g. for the purpose of logical replication. See for details. + This parameter cannot be set for TOAST tables. diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index f008fab821..8176b6a6d4 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -209,6 +209,14 @@ static relopt_int intRelOpts[] = RELOPT_KIND_HEAP | RELOPT_KIND_TOAST }, -1, 0, 2000000000 }, + { + { + "log_autovacuum_min_duration", + "Sets the minimum execution time above which autovacuum actions will be logged", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST + }, + -1, -1, INT_MAX + }, { { "pages_per_range", @@ -1210,6 +1218,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, multixact_freeze_max_age)}, {"autovacuum_multixact_freeze_table_age", RELOPT_TYPE_INT, 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_vacuum_scale_factor", RELOPT_TYPE_REAL, offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, vacuum_scale_factor)}, {"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL, diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index d4d19148e5..15ec0ad551 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -85,7 +85,8 @@ static MemoryContext anl_context = NULL; static BufferAccessStrategy vac_strategy; -static void do_analyze_rel(Relation onerel, int options, List *va_cols, +static void do_analyze_rel(Relation onerel, int options, + VacuumParams *params, List *va_cols, AcquireSampleRowsFunc acquirefunc, BlockNumber relpages, bool inh, bool in_outer_xact, int elevel); static void BlockSampler_Init(BlockSampler bs, BlockNumber nblocks, @@ -115,8 +116,9 @@ static Datum ind_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull); * analyze_rel() -- analyze one relation */ void -analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols, - bool in_outer_xact, BufferAccessStrategy bstrategy) +analyze_rel(Oid relid, RangeVar *relation, int options, + VacuumParams *params, List *va_cols, bool in_outer_xact, + BufferAccessStrategy bstrategy) { Relation onerel; int elevel; @@ -151,7 +153,7 @@ analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols, else { onerel = NULL; - if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0) + if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0) ereport(LOG, (errcode(ERRCODE_LOCK_NOT_AVAILABLE), errmsg("skipping analyze of \"%s\" --- lock not available", @@ -266,14 +268,14 @@ analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols, /* * Do the normal non-recursive ANALYZE. */ - do_analyze_rel(onerel, options, va_cols, acquirefunc, relpages, + do_analyze_rel(onerel, options, params, va_cols, acquirefunc, relpages, false, in_outer_xact, elevel); /* * If there are child tables, do recursive ANALYZE. */ if (onerel->rd_rel->relhassubclass) - do_analyze_rel(onerel, options, va_cols, acquirefunc, relpages, + do_analyze_rel(onerel, options, params, va_cols, acquirefunc, relpages, true, in_outer_xact, elevel); /* @@ -301,9 +303,10 @@ analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols, * appropriate acquirefunc for each child table. */ static void -do_analyze_rel(Relation onerel, int options, List *va_cols, - AcquireSampleRowsFunc acquirefunc, BlockNumber relpages, - bool inh, bool in_outer_xact, int elevel) +do_analyze_rel(Relation onerel, int options, VacuumParams *params, + List *va_cols, AcquireSampleRowsFunc acquirefunc, + BlockNumber relpages, bool inh, bool in_outer_xact, + int elevel) { int attr_cnt, tcnt, @@ -359,10 +362,10 @@ do_analyze_rel(Relation onerel, int options, List *va_cols, save_nestlevel = NewGUCNestLevel(); /* measure elapsed time iff autovacuum logging requires it */ - if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0) + if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0) { pg_rusage_init(&ru0); - if (Log_autovacuum_min_duration > 0) + if (params->log_min_duration > 0) starttime = GetCurrentTimestamp(); } @@ -647,11 +650,11 @@ do_analyze_rel(Relation onerel, int options, List *va_cols, vac_close_indexes(nindexes, Irel, NoLock); /* Log the action if appropriate */ - if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0) + if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0) { - if (Log_autovacuum_min_duration == 0 || + if (params->log_min_duration == 0 || TimestampDifferenceExceeds(starttime, GetCurrentTimestamp(), - Log_autovacuum_min_duration)) + params->log_min_duration)) ereport(LOG, (errmsg("automatic analyze of table \"%s.%s.%s\" system usage: %s", get_database_name(MyDatabaseId), diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index bd57b683d8..7ead161760 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -114,6 +114,9 @@ ExecVacuum(VacuumStmt *vacstmt, bool isTopLevel) /* user-invoked vacuum is never "for wraparound" */ params.is_wraparound = false; + /* user-invoked vacuum never uses this parameter */ + params.log_min_duration = -1; + /* Now go through the common routine */ vacuum(vacstmt->options, vacstmt->relation, InvalidOid, ¶ms, vacstmt->va_cols, NULL, isTopLevel); @@ -304,7 +307,7 @@ vacuum(int options, RangeVar *relation, Oid relid, VacuumParams *params, PushActiveSnapshot(GetTransactionSnapshot()); } - analyze_rel(relid, relation, options, + analyze_rel(relid, relation, options, params, va_cols, in_outer_xact, vac_strategy); if (use_own_xacts) @@ -1233,7 +1236,7 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params) else { onerel = NULL; - if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0) + if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0) ereport(LOG, (errcode(ERRCODE_LOCK_NOT_AVAILABLE), errmsg("skipping vacuum of \"%s\" --- lock not available", diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index cd5ca4c2f9..c3d6e59890 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -196,7 +196,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, Assert(params != NULL); /* measure elapsed time iff autovacuum logging requires it */ - if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0) + if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0) { pg_rusage_init(&ru0); starttime = GetCurrentTimestamp(); @@ -328,13 +328,13 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params, vacrelstats->new_dead_tuples); /* and log the action if appropriate */ - if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0) + if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0) { TimestampTz endtime = GetCurrentTimestamp(); - if (Log_autovacuum_min_duration == 0 || + if (params->log_min_duration == 0 || TimestampDifferenceExceeds(starttime, endtime, - Log_autovacuum_min_duration)) + params->log_min_duration)) { StringInfoData buf; TimestampDifference(starttime, endtime, &secs, &usecs); diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index b2d37e5d47..db46134979 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -2493,6 +2493,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, int multixact_freeze_table_age; int vac_cost_limit; int vac_cost_delay; + int log_min_duration; /* * Calculate the vacuum cost parameters and the freeze ages. If there @@ -2515,6 +2516,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, ? autovacuum_vac_cost_limit : VacuumCostLimit; + /* -1 in autovac setting means use log_autovacuum_min_duration */ + log_min_duration = (avopts && avopts->log_min_duration >= 0) + ? avopts->log_min_duration + : Log_autovacuum_min_duration; + /* these do not have autovacuum-specific settings */ freeze_min_age = (avopts && avopts->freeze_min_age >= 0) ? avopts->freeze_min_age @@ -2545,6 +2551,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age; 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_vacuum_cost_limit = vac_cost_limit; tab->at_vacuum_cost_delay = vac_cost_delay; tab->at_relname = NULL; diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 5a4fd5c873..38fde39b30 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1788,6 +1788,7 @@ psql_completion(const char *text, int start, int end) "autovacuum_vacuum_scale_factor", "autovacuum_vacuum_threshold", "fillfactor", + "log_autovacuum_min_duration", "toast.autovacuum_enabled", "toast.autovacuum_freeze_max_age", "toast.autovacuum_freeze_min_age", @@ -1799,6 +1800,7 @@ psql_completion(const char *text, int start, int end) "toast.autovacuum_vacuum_cost_limit", "toast.autovacuum_vacuum_scale_factor", "toast.autovacuum_vacuum_threshold", + "toast.log_autovacuum_min_duration", "user_catalog_table", NULL }; diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 9fd2516923..71f016552b 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -142,6 +142,9 @@ typedef struct VacuumParams int multixact_freeze_table_age; /* multixact age at which to * scan whole table */ bool is_wraparound; /* force a for-wraparound vacuum */ + int log_min_duration; /* minimum execution threshold in ms at + * which verbose logs are activated, + * -1 to use default */ } VacuumParams; /* GUC parameters */ @@ -191,7 +194,7 @@ extern void lazy_vacuum_rel(Relation onerel, int options, /* in commands/analyze.c */ extern void analyze_rel(Oid relid, RangeVar *relation, int options, - List *va_cols, bool in_outer_xact, + VacuumParams *params, List *va_cols, bool in_outer_xact, BufferAccessStrategy bstrategy); extern bool std_typanalyze(VacAttrStats *stats); extern double anl_random_fract(void); diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 6bd786df2d..9e17d87413 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -209,6 +209,7 @@ typedef struct AutoVacOpts int multixact_freeze_min_age; int multixact_freeze_max_age; int multixact_freeze_table_age; + int log_min_duration; float8 vacuum_scale_factor; float8 analyze_scale_factor; } AutoVacOpts; -- 2.40.0