From: | Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | John Naylor <john(dot)naylor(at)enterprisedb(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, "Bossart, Nathan" <bossartn(at)amazon(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: do only critical work during single-user vacuum? |
Date: | 2021-12-22 02:39:00 |
Message-ID: | CAD21AoCN_T1kdnnNNyzO3yVHd+HxOeV=2FP_Qh5vDM=38q=Yjg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Dec 22, 2021 at 6:56 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Tue, Dec 21, 2021 at 1:31 PM John Naylor
> <john(dot)naylor(at)enterprisedb(dot)com> wrote:
> > On second thought, we don't really need another number here. We could
> > simply go by the existing failsafe parameter, and if the admin wants a
> > different value, it's already possible to specify
> > vacuum_failsafe_age/vacuum_multixact_failsafe_age in a session,
> > including in single-user mode. Perhaps a new boolean called
> > FAILSAFE_ONLY. If no table is specified, then when generating the list
> > of tables, include only those with relfrozenxid/relminmxid greater
> > than their failsafe thresholds.
>
> That's equivalent to the quick and dirty patch I wrote (assuming that
> the user actually uses this new FAILSAFE_ONLY thing).
>
> But if we're going to add a new option to the VACUUM command (or
> something of similar scope), then we might as well add a new behavior
> that is reasonably exact -- something that (say) only *starts* a
> VACUUM for those tables whose relfrozenxid age currently exceeds half
> the autovacuum_freeze_max_age for the table (usually taken from the
> GUC, sometimes taken from the reloption), which also forces the
> failsafe. And with similar handling for
> relminmxid/autovacuum_multixact_freeze_max_age.
>
> In other words, while triggering the failsafe is important, simply *not
> starting* VACUUM for relations where there is really no need for it is
> at least as important. We shouldn't even think about pruning or
> freezing with these tables. (ISTM that the only thing that might be a
> bit controversial about any of this is my definition of "safe", which
> seems like about the right trade-off to me.)
+1
>
> This new command/facility should probably not be a new flag to the
> VACUUM command, as such. Rather, I think that it should either be an
> SQL-callable function, or a dedicated top-level command (that doesn't
> accept any tables). The only reason to have this is for scenarios
> where the user is already in a tough spot with wraparound failure,
> like that client of yours. Nobody wants to force the failsafe for one
> specific table. It's not general purpose, at all, and shouldn't claim
> to be.
Even not in the situation where the database has to run as the
single-user mode to freeze tuples, I think there would be some use
cases where users want to run vacuum (in failsafe mode) on tables with
relfrozenxid/relminmxid greater than their failsafe thresholds before
falling into that situation. I think it’s common that users are
somehow monitoring relfrozenxid/relminmxid and want to manually run
vacuum on them rather than relying on autovacuums. --min-xid-age
option and --min-mxid-age option of vacuumdb command would be good
examples. So I think this new command/facility might not necessarily
need to be specific to single-user mode.
Regards,
--
Masahiko Sawada
EDB: https://www.enterprisedb.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Masahiko Sawada | 2021-12-22 03:35:30 | Re: parallel vacuum comments |
Previous Message | Tomas Vondra | 2021-12-22 01:57:02 | Re: sequences vs. synchronous replication |