On Fri, Jan 21, 2022 at 05:41:58PM -0500, John Naylor wrote:
> On Wed, Jan 19, 2022 at 5:26 PM Michael Paquier <mich...@paquier.xyz> wrote:
> >
> > Could you avoid introducing a new grammar pattern in VACUUM? Any new
> > option had better be within the parenthesized part as it is extensible
> > at will with its set of DefElems.
>
> This new behavior is not an option that one can sensibly mix with
> other options as the user sees fit, but rather hard-codes the
> parameters for its single purpose. That said, I do understand your
> objection.
This seems better, and it's shorter too.
I'm sure you meant "&" here (fixed in attached patch to appease the cfbot):
+ if (options | VACOPT_MINIMAL)
It should either refuse to run if a list of tables is specified with MINIMAL,
or it should filter that list by XID condition.
As for the name, it could be MINIMAL or FAILSAFE or EMERGENCY or ??
I think the name should actually be a bit more descriptive, and maybe say XID,
like MINIMAL_XID or XID_EMERGENCY...
Normally, options are independent, but VACUUM (MINIMAL) is a "shortcut" to a
hardcoded set of options: freeze on, truncate off, cleanup off. So it refuses
to be combined with other options - good.
This is effectively a shortcut to hypothetical parameters for selecting tables
by XID/MXID age. In the future, someone could debate adding user-facing knobs
for table selection by age.
I still wonder if the relations should be processed in order of decreasing age.
An admin might have increased autovacuum_freeze_max_age up to 2e9, and your
query might return thousands of tables, with a wide range of sizes and ages.
Processing them in order of decreasing age would allow the admin to quickly
vacuum the oldest tables, and optionally interrupt vacuum to get out of single
user mode ASAP - even if their just want to run VACUUM(MINIMAL) in a normal
backend when services aren't offline. Processing them out of order might be
pretty surprising - they might run vacuum for an hour (or overnight), cancel
it, attempt to start the DB in normal mode, and conclude that it made no
visible progress.
On Fri, Jan 21, 2022 at 12:59 AM Masahiko Sawada <sawada.m...@gmail.com> wrote:
> > The purpose of this thread is to provide a way for users to run vacuum
> > only very old tables (while skipping index cleanup, etc.),
>
> Ah, thank you Sawada-san, now I understand why we have been talking
> past each other. The purpose is actually:
>
> - to have a simple, easy to type, command
> - intended for single-user mode, but not limited to it (so it's easy to test)
> - to get out of single user mode as quickly as possible
>From 03c567bb534219acdd76b0acc40e544c76f938e5 Mon Sep 17 00:00:00 2001
From: John Naylor <john.nay...@enterprisedb.com>
Date: Fri, 21 Jan 2022 17:41:58 -0500
Subject: [PATCH] do only critical work during single-user vacuum?
Jan 21 John Naylor
---
src/backend/commands/vacuum.c | 76 +++++++++++++++++++++++++++++++++--
src/include/commands/vacuum.h | 1 +
2 files changed, 74 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d1dadc54e47..c7bc97d3f76 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -52,6 +52,7 @@
#include "storage/proc.h"
#include "storage/procarray.h"
#include "utils/acl.h"
+#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/memutils.h"
@@ -114,6 +115,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
bool full = false;
bool disable_page_skipping = false;
bool process_toast = true;
+ bool wraparound = false;
ListCell *lc;
/* index_cleanup and truncate values unspecified for now */
@@ -200,6 +202,8 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
params.nworkers = nworkers;
}
}
+ else if (strcmp(opt->defname, "wraparound") == 0)
+ wraparound = defGetBoolean(opt);
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -246,17 +250,51 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
}
}
+ if (wraparound)
+ {
+ /* exclude incompatible options */
+ foreach(lc, vacstmt->options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ // WIP is there a better way?
+ if (strcmp(opt->defname, "wraparound") != 0 &&
+ strcmp(opt->defname, "verbose") != 0 &&
+ defGetBoolean(opt))
+
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("option \"%s\" is incompatible with WRAPAROUND", opt->defname),
+ parser_errposition(pstate, opt->location)));
+ }
+
+ /* skip unnecessary work, as in failsafe mode */
+ params.index_cleanup = VACOPTVALUE_DISABLED;
+ params.truncate = VACOPTVALUE_DISABLED;
+ }
+
/*
- * All freeze ages are zero if the FREEZE option is given; otherwise pass
- * them as -1 which means to use the default values.
+ * Set freeze ages to zero where appropriate; otherwise pass
+ * them as -1 which means to use the configured values.
*/
if (params.options & VACOPT_FREEZE)
{
+ /* All freeze ages are zero if the FREEZE option is given */
params.freeze_min_age = 0;
params.freeze_table_age = 0;
params.multixact_freeze_min_age = 0;
params.multixact_freeze_table_age = 0;
}
+ else if (params.options & VACOPT_MINIMAL)
+ {
+ /* it's unlikely any selected table will not be eligible for aggressive vacuum, but make sure */
+ params.freeze_table_age = 0;
+ params.multixact_freeze_table_age = 0;
+
+ // WIP: It might be worth trying to do less work here, or at least hard-coding the default values
+ params.freeze_min_age = -1;
+ params.multixact_freeze_min_age = -1;
+ }
else
{
params.freeze_min_age = -1;
@@ -894,6 +932,8 @@ get_all_vacuum_rels(int options)
Relation pgclass;
TableScanDesc scan;
HeapTuple tuple;
+ int32 table_xid_age,
+ table_mxid_age;
pgclass = table_open(RelationRelationId, AccessShareLock);
@@ -909,12 +949,42 @@ get_all_vacuum_rels(int options)
if (!vacuum_is_relation_owner(relid, classForm, options))
continue;
+ if (options & VACOPT_MINIMAL)
+ {
+ /*
+ * Only consider relations able to hold unfrozen XIDs (anything else
+ * should have InvalidTransactionId in relfrozenxid anyway).
+ */
+ if (classForm->relkind != RELKIND_RELATION &&
+ classForm->relkind != RELKIND_MATVIEW &&
+ classForm->relkind != RELKIND_TOASTVALUE)
+ {
+ Assert(!TransactionIdIsValid(classForm->relfrozenxid));
+ Assert(!MultiXactIdIsValid(classForm->relminmxid));
+ continue;
+ }
+
+ table_xid_age = DirectFunctionCall1(xid_age, classForm->relfrozenxid);
+ table_mxid_age = DirectFunctionCall1(mxid_age, classForm->relminmxid);
+
+ /* Hard-code 1 billion for the thresholds to avoid making assumptions
+ * about the configuration. This leaves some headroom for when the user
+ * returns to normal mode while also minimizing work.
+ * WIP: consider passing these constants via the params struct
+ */
+ // FIXME to speed up testing
+ // if ((table_xid_age < 1000 * 1000 * 1000) &&
+ // (table_mxid_age < 1000 * 1000 * 1000))
+ if ((table_xid_age < 1000 * 1000) &&
+ (table_mxid_age < 1000 * 1000))
+ continue;
+ }
/*
* We include partitioned tables here; depending on which operation is
* to be performed, caller will decide whether to process or ignore
* them.
*/
- if (classForm->relkind != RELKIND_RELATION &&
+ else if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW &&
classForm->relkind != RELKIND_PARTITIONED_TABLE)
continue;
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 5d0bdfa4279..3d8b8fbbb1a 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -188,6 +188,7 @@ typedef struct VacAttrStats
#define VACOPT_SKIP_LOCKED 0x20 /* skip if cannot get lock */
#define VACOPT_PROCESS_TOAST 0x40 /* process the TOAST table, if any */
#define VACOPT_DISABLE_PAGE_SKIPPING 0x80 /* don't skip any pages */
+#define VACOPT_MINIMAL 0x100 /* do minimal freezing work to prevent or get out of shutdown */
/*
* Values used by index_cleanup and truncate params.
--
2.17.1
