Skip to content

Commit 4ff695b

Browse files
alvherrealvherre
committed
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.
1 parent a75fb9b commit 4ff695b

File tree

9 files changed

+63
-24
lines changed

9 files changed

+63
-24
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -881,9 +881,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
881881
<literal>toast.</literal>, which can be used to control the behavior of the
882882
table's secondary <acronym>TOAST</> table, if any
883883
(see <xref linkend="storage-toast"> for more information about TOAST).
884-
Note that the TOAST table inherits the
885-
<literal>autovacuum_*</literal> values from its parent table, if there are
886-
no <literal>toast.autovacuum_*</literal> settings set.
884+
Note that the TOAST table uses the parameter values defined for
885+
the main table, for each parameter applicable to TOAST tables and
886+
for which no value is set in the TOAST table itself.
887887
</para>
888888

889889
<variablelist>
@@ -1060,13 +1060,23 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
10601060
</listitem>
10611061
</varlistentry>
10621062

1063+
<varlistentry>
1064+
<term><literal>log_autovacuum_min_duration</literal>, <literal>toast.log_autovacuum_min_duration</literal> (<type>integer</type>)</term>
1065+
<listitem>
1066+
<para>
1067+
Custom <xref linkend="guc-log-autovacuum-min-duration"> parameter.
1068+
</para>
1069+
</listitem>
1070+
</varlistentry>
1071+
10631072
<varlistentry>
10641073
<term><literal>user_catalog_table</literal> (<type>boolean</type>)</term>
10651074
<listitem>
10661075
<para>
10671076
Declare a table as an additional catalog table, e.g. for the purpose of
10681077
logical replication. See
10691078
<xref linkend="logicaldecoding-capabilities"> for details.
1079+
This parameter cannot be set for TOAST tables.
10701080
</para>
10711081
</listitem>
10721082
</varlistentry>

‎src/backend/access/common/reloptions.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -209,6 +209,14 @@ static relopt_int intRelOpts[] =
209209
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
210210
}, -1, 0, 2000000000
211211
},
212+
{
213+
{
214+
"log_autovacuum_min_duration",
215+
"Sets the minimum execution time above which autovacuum actions will be logged",
216+
RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
217+
},
218+
-1, -1, INT_MAX
219+
},
212220
{
213221
{
214222
"pages_per_range",
@@ -1210,6 +1218,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
12101218
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, multixact_freeze_max_age)},
12111219
{"autovacuum_multixact_freeze_table_age", RELOPT_TYPE_INT,
12121220
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, multixact_freeze_table_age)},
1221+
{"log_autovacuum_min_duration", RELOPT_TYPE_INT,
1222+
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, log_min_duration)},
12131223
{"autovacuum_vacuum_scale_factor", RELOPT_TYPE_REAL,
12141224
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, vacuum_scale_factor)},
12151225
{"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL,

‎src/backend/commands/analyze.c

Lines changed: 17 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -85,7 +85,8 @@ static MemoryContext anl_context = NULL;
8585
static BufferAccessStrategy vac_strategy;
8686

8787

88-
static void do_analyze_rel(Relation onerel, int options, List *va_cols,
88+
static void do_analyze_rel(Relation onerel, int options,
89+
VacuumParams *params, List *va_cols,
8990
AcquireSampleRowsFunc acquirefunc, BlockNumber relpages,
9091
bool inh, bool in_outer_xact, int elevel);
9192
static void BlockSampler_Init(BlockSampler bs, BlockNumber nblocks,
@@ -115,8 +116,9 @@ static Datum ind_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
115116
* analyze_rel() -- analyze one relation
116117
*/
117118
void
118-
analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols,
119-
bool in_outer_xact, BufferAccessStrategy bstrategy)
119+
analyze_rel(Oid relid, RangeVar *relation, int options,
120+
VacuumParams *params, List *va_cols, bool in_outer_xact,
121+
BufferAccessStrategy bstrategy)
120122
{
121123
Relation onerel;
122124
int elevel;
@@ -151,7 +153,7 @@ analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols,
151153
else
152154
{
153155
onerel = NULL;
154-
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
156+
if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
155157
ereport(LOG,
156158
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
157159
errmsg("skipping analyze of \"%s\" --- lock not available",
@@ -266,14 +268,14 @@ analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols,
266268
/*
267269
* Do the normal non-recursive ANALYZE.
268270
*/
269-
do_analyze_rel(onerel, options, va_cols, acquirefunc, relpages,
271+
do_analyze_rel(onerel, options, params, va_cols, acquirefunc, relpages,
270272
false, in_outer_xact, elevel);
271273

272274
/*
273275
* If there are child tables, do recursive ANALYZE.
274276
*/
275277
if (onerel->rd_rel->relhassubclass)
276-
do_analyze_rel(onerel, options, va_cols, acquirefunc, relpages,
278+
do_analyze_rel(onerel, options, params, va_cols, acquirefunc, relpages,
277279
true, in_outer_xact, elevel);
278280

279281
/*
@@ -301,9 +303,10 @@ analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols,
301303
* appropriate acquirefunc for each child table.
302304
*/
303305
static void
304-
do_analyze_rel(Relation onerel, int options, List *va_cols,
305-
AcquireSampleRowsFunc acquirefunc, BlockNumber relpages,
306-
bool inh, bool in_outer_xact, int elevel)
306+
do_analyze_rel(Relation onerel, int options, VacuumParams *params,
307+
List *va_cols, AcquireSampleRowsFunc acquirefunc,
308+
BlockNumber relpages, bool inh, bool in_outer_xact,
309+
int elevel)
307310
{
308311
int attr_cnt,
309312
tcnt,
@@ -359,10 +362,10 @@ do_analyze_rel(Relation onerel, int options, List *va_cols,
359362
save_nestlevel = NewGUCNestLevel();
360363

361364
/* measure elapsed time iff autovacuum logging requires it */
362-
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
365+
if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
363366
{
364367
pg_rusage_init(&ru0);
365-
if (Log_autovacuum_min_duration > 0)
368+
if (params->log_min_duration > 0)
366369
starttime = GetCurrentTimestamp();
367370
}
368371

@@ -647,11 +650,11 @@ do_analyze_rel(Relation onerel, int options, List *va_cols,
647650
vac_close_indexes(nindexes, Irel, NoLock);
648651

649652
/* Log the action if appropriate */
650-
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
653+
if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
651654
{
652-
if (Log_autovacuum_min_duration == 0 ||
655+
if (params->log_min_duration == 0 ||
653656
TimestampDifferenceExceeds(starttime, GetCurrentTimestamp(),
654-
Log_autovacuum_min_duration))
657+
params->log_min_duration))
655658
ereport(LOG,
656659
(errmsg("automatic analyze of table \"%s.%s.%s\" system usage: %s",
657660
get_database_name(MyDatabaseId),

‎src/backend/commands/vacuum.c

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -114,6 +114,9 @@ ExecVacuum(VacuumStmt *vacstmt, bool isTopLevel)
114114
/* user-invoked vacuum is never "for wraparound" */
115115
params.is_wraparound = false;
116116

117+
/* user-invoked vacuum never uses this parameter */
118+
params.log_min_duration = -1;
119+
117120
/* Now go through the common routine */
118121
vacuum(vacstmt->options, vacstmt->relation, InvalidOid, &params,
119122
vacstmt->va_cols, NULL, isTopLevel);
@@ -304,7 +307,7 @@ vacuum(int options, RangeVar *relation, Oid relid, VacuumParams *params,
304307
PushActiveSnapshot(GetTransactionSnapshot());
305308
}
306309

307-
analyze_rel(relid, relation, options,
310+
analyze_rel(relid, relation, options, params,
308311
va_cols, in_outer_xact, vac_strategy);
309312

310313
if (use_own_xacts)
@@ -1233,7 +1236,7 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
12331236
else
12341237
{
12351238
onerel = NULL;
1236-
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
1239+
if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
12371240
ereport(LOG,
12381241
(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
12391242
errmsg("skipping vacuum of \"%s\" --- lock not available",

‎src/backend/commands/vacuumlazy.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -196,7 +196,7 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
196196
Assert(params != NULL);
197197

198198
/* measure elapsed time iff autovacuum logging requires it */
199-
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
199+
if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
200200
{
201201
pg_rusage_init(&ru0);
202202
starttime = GetCurrentTimestamp();
@@ -328,13 +328,13 @@ lazy_vacuum_rel(Relation onerel, int options, VacuumParams *params,
328328
vacrelstats->new_dead_tuples);
329329

330330
/* and log the action if appropriate */
331-
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
331+
if (IsAutoVacuumWorkerProcess() && params->log_min_duration >= 0)
332332
{
333333
TimestampTz endtime = GetCurrentTimestamp();
334334

335-
if (Log_autovacuum_min_duration == 0 ||
335+
if (params->log_min_duration == 0 ||
336336
TimestampDifferenceExceeds(starttime, endtime,
337-
Log_autovacuum_min_duration))
337+
params->log_min_duration))
338338
{
339339
StringInfoData buf;
340340
TimestampDifference(starttime, endtime, &secs, &usecs);

‎src/backend/postmaster/autovacuum.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2493,6 +2493,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
24932493
int multixact_freeze_table_age;
24942494
int vac_cost_limit;
24952495
int vac_cost_delay;
2496+
int log_min_duration;
24962497

24972498
/*
24982499
* Calculate the vacuum cost parameters and the freeze ages. If there
@@ -2515,6 +2516,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
25152516
? autovacuum_vac_cost_limit
25162517
: VacuumCostLimit;
25172518

2519+
/* -1 in autovac setting means use log_autovacuum_min_duration */
2520+
log_min_duration = (avopts && avopts->log_min_duration >= 0)
2521+
? avopts->log_min_duration
2522+
: Log_autovacuum_min_duration;
2523+
25182524
/* these do not have autovacuum-specific settings */
25192525
freeze_min_age = (avopts && avopts->freeze_min_age >= 0)
25202526
? avopts->freeze_min_age
@@ -2545,6 +2551,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
25452551
tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age;
25462552
tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
25472553
tab->at_params.is_wraparound = wraparound;
2554+
tab->at_params.log_min_duration = log_min_duration;
25482555
tab->at_vacuum_cost_limit = vac_cost_limit;
25492556
tab->at_vacuum_cost_delay = vac_cost_delay;
25502557
tab->at_relname = NULL;

‎src/bin/psql/tab-complete.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1788,6 +1788,7 @@ psql_completion(const char *text, int start, int end)
17881788
"autovacuum_vacuum_scale_factor",
17891789
"autovacuum_vacuum_threshold",
17901790
"fillfactor",
1791+
"log_autovacuum_min_duration",
17911792
"toast.autovacuum_enabled",
17921793
"toast.autovacuum_freeze_max_age",
17931794
"toast.autovacuum_freeze_min_age",
@@ -1799,6 +1800,7 @@ psql_completion(const char *text, int start, int end)
17991800
"toast.autovacuum_vacuum_cost_limit",
18001801
"toast.autovacuum_vacuum_scale_factor",
18011802
"toast.autovacuum_vacuum_threshold",
1803+
"toast.log_autovacuum_min_duration",
18021804
"user_catalog_table",
18031805
NULL
18041806
};

‎src/include/commands/vacuum.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -142,6 +142,9 @@ typedef struct VacuumParams
142142
int multixact_freeze_table_age; /* multixact age at which to
143143
* scan whole table */
144144
bool is_wraparound; /* force a for-wraparound vacuum */
145+
int log_min_duration; /* minimum execution threshold in ms at
146+
* which verbose logs are activated,
147+
* -1 to use default */
145148
} VacuumParams;
146149

147150
/* GUC parameters */
@@ -191,7 +194,7 @@ extern void lazy_vacuum_rel(Relation onerel, int options,
191194

192195
/* in commands/analyze.c */
193196
extern void analyze_rel(Oid relid, RangeVar *relation, int options,
194-
List *va_cols, bool in_outer_xact,
197+
VacuumParams *params, List *va_cols, bool in_outer_xact,
195198
BufferAccessStrategy bstrategy);
196199
extern bool std_typanalyze(VacAttrStats *stats);
197200
extern double anl_random_fract(void);

‎src/include/utils/rel.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -209,6 +209,7 @@ typedef struct AutoVacOpts
209209
int multixact_freeze_min_age;
210210
int multixact_freeze_max_age;
211211
int multixact_freeze_table_age;
212+
int log_min_duration;
212213
float8 vacuum_scale_factor;
213214
float8 analyze_scale_factor;
214215
} AutoVacOpts;

0 commit comments

Comments
 (0)