Re: do only critical work during single-user vacuum?

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Masahiko Sawada <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: John Naylor <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Bossart, Nathan <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: do only critical work during single-user vacuum?
Date: Wed, 22 Dec 2021 11:39:00 +0900
Message-ID: <CAD21AoCN_T1kdnnNNyzO3yVHd+HxOeV=2FP_Qh5vDM=38q=Yjg@mail.gmail.com> (raw)
In-Reply-To: <CAH2-Wzmt29ZUwXW+9ACvxMoN9AjSMuH_Oabk1_mJt78f58WdvQ@mail.gmail.com>
References: <CAFBsxsFL5LoWanSLDjt_SjS9=FU_9XWi22iHMexRS2SubkX-Vw@mail.gmail.com>
	<CAH2-Wz=Z5Jjd1y8kCfXd6e-GSvKiL8XEmxoJJFNHcekM4aw-Vg@mail.gmail.com>
	<CAH2-Wzn3pE8rNzBxh-Qpmq=zxFWFpoC7LWLbyDg_1XZ3wJOtUA@mail.gmail.com>
	<CAFBsxsG-VDhXYa25uERfDGD4dO_AZ3d+Dv2NF+DJBADZiXejpw@mail.gmail.com>
	<CAH2-Wznop1jM3j6E1xTcG--kFQ1hW5cMmM8B1965Mz+uUg6HSg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAH2-Wz=9mfmSL5QDG9GmXt2Av8NBvNSo+CF7FPW3g80awHab7Q@mail.gmail.com>
	<[email protected]>
	<CAH2-Wz=Eb-jrhFK6=_WNT6V1JAK-qFOTu_4-yOpAhkEES4VeNA@mail.gmail.com>
	<[email protected]>
	<CAD21AoCz8=-jBTUBcQSveOMCLkJ=yUw5TYtfVPWKTXiLuan_CQ@mail.gmail.com>
	<CAH2-Wzn+PJ99oJRnN32ojvRpgH4xjmTOJZbt+GTHpDWN655Adg@mail.gmail.com>
	<CAD21AoAXHZ0J-Xd0RfP_VpkUnGv0G_gCbwt3aCuy6weOKNaHNw@mail.gmail.com>
	<CAFBsxsHCBWTJx5j6zKogh5-_wuggcHJT7g+5_TQVMyKvgCT4Fw@mail.gmail.com>
	<CAFBsxsE1TWidnMs=RMx1eb2QxqmNqdDaLLN5E7SP_v5_g2ZRwA@mail.gmail.com>
	<CAH2-Wzmt29ZUwXW+9ACvxMoN9AjSMuH_Oabk1_mJt78f58WdvQ@mail.gmail.com>

On Wed, Dec 22, 2021 at 6:56 AM Peter Geoghegan <[email protected]> wrote:
>
> On Tue, Dec 21, 2021 at 1:31 PM John Naylor
> <[email protected]> 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/

view thread (32+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: do only critical work during single-user vacuum?
  In-Reply-To: <CAD21AoCN_T1kdnnNNyzO3yVHd+HxOeV=2FP_Qh5vDM=38q=Yjg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox