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

From: John Naylor <[email-masked]>
Date: Thu, 3 Feb 2022 13:34:28 -0500

On Thu, Feb 3, 2022 at 1:06 PM Robert Haas <[email-masked]> wrote:
>
> On Thu, Dec 9, 2021 at 8:56 PM Andres Freund <[email-masked]> wrote:
> > I think we should move *away* from single user mode, rather than the
> > opposite. It's a substantial code burden and it's hard to use.
>
> Yes. This thread seems to be largely devoted to the topic of making
> single-user vacuum work better, but I don't see anyone asking the
> question "why do we have a message that tells people to vacuum in
> single user mode in the first place?". It's basically bad advice, with
> one small exception that I'll talk about in a minute.

The word "advice" sounds like people have a choice, rather than the
system not accepting commands anymore. It would be much less painful
if the system closed connections and forbade all but superusers to
connect, but that sounds like a lot of work. (happy to be proven
otherwise)

-- 
John Naylor
EDB: http://www.enterprisedb.com

---

From: Robert Haas <[email-masked]>
Date: Thu, 3 Feb 2022 13:42:20 -0500

> connect, but that sounds like a lot of work. (happy to be proven

They *do* have a choice. They can continue to operate the system in
multi-user mode, they can have read access to their data, and they can
run VACUUM and other non-XID-allocating commands to fix the issue.
Sure, their application can't run commands that allocate XIDs, but
it's not going to be able to do that if they go to single-user mode
either.

I don't understand why we would want the system to stop accepting
connections other than superuser connections. That would provide
strictly less functionality and I don't understand what it would gain.
But it would still be better than going into single-user mode, which
provides even less functionality and has basically no advantages of
any kind.

Why are you convinced that the user HAS to go to single-user mode? I
don't think they have to do that, and I don't think they should want
to do that.

-- 
Robert Haas

---

From: John Naylor <[email-masked]>
Date: Thu, 3 Feb 2022 16:18:27 -0500

> it's not going to be able to do that if they go to single-user mode

I just checked some client case notes where they tried just that
before getting outside help, and both SELECT and VACUUM FREEZE
commands were rejected. The failure is clearly indicated in the log.
-- 
John Naylor
EDB: http://www.enterprisedb.com

---

From: Andres Freund <[email-masked]>
Date: Thu, 3 Feb 2022 13:50:48 -0800

Hi,

> it's not going to be able to do that if they go to single-user mode

I wonder if we shouldn't add some exceptions to the xid allocation
prevention. It makes sense that we don't allow random DML. But it's e.g. often
more realistic to drop / truncate a few tables with unimportant content,
rather than spend the time vacuuming those.  We could e.g. allow xid
consumption within VACUUM, TRUNCATE, DROP TABLE / INDEX when run at the top
level for longer than we allow it for anything else.

> provides even less functionality and has basically no advantages of

Indeed. Single user is the worst response to this (and just about anything
else, really). Even just getting into the single user mode takes a while
(shutdown checkpoint). The user interface is completely different (and
awful). The buffer cache is completely cold. The system is slower because
there's no wal writer / checkpointer running.  Which basically is a list of
things one absolutely do not wants when confronted with a wraparound
situation.

Greetings,

Andres Freund

---

From: John Naylor <[email-masked]>
Date: Tue, 15 Feb 2022 11:04:46 +0700

On Fri, Feb 4, 2022 at 4:58 AM Robert Haas <[email-masked]> wrote:

> As I said
> before, I know TRUNCATE has been an issue in the past, and if that's
> not already fixed in v14, we should. If there's other stuff, we should
> fix that too.

The failsafe mode does disable truncation as of v14:

commit 60f1f09ff44308667ef6c72fbafd68235e55ae27
Author: Peter Geoghegan <[email-masked]>
Date:   Tue Apr 13 12:58:31 2021 -0700

Don't truncate heap when VACUUM's failsafe is in effect.
--

To demonstrate to myself, I tried a few vacuums in a debugger session
with a breakpoint at GetNewTransactionId(). I've only seen it reach
here when heap truncation happens (or the not relevant for wraparound
situations FULL and ANALYZE).

With the maximum allowable setting of autovacuum_freeze_max_age of 2
billion, the highest allowable vacuum_failsafe_age is 2.1 billion, so
heap truncation will be shut off before the warnings start.

> And then we should KILL WITH FIRE
> the message telling people to use single user mode -- and once we do
> that, the question of what the behavior ought to be when someone does
> run VACUUM in single user mode becomes a lot less important.

Okay, so it sounds like changing the message is enough for v15? The
other two things mentioned are nice-to-haves, but wouldn't need to
hold back this minimal change, it seems:

> level for longer than we allow it for anything else.

It seems like this would require having access to "nodetag(parsetree)"
of the statement available in GetNewTransactionId. I don't immediately
see an easy way to do that...is a global var within the realm of
acceptability?

On Fri, Feb 4, 2022 at 8:35 AM Andres Freund <[email-masked]> wrote:

> we'd actually tell the user a bit more what about what is causing the
> problem.
>
> We can compute the:
> 1) oldest slot by xmin, with name
> 2) oldest walsender by xmin, with pid
> 3) oldest prepared transaction id by xid / xmin, with name
> 4) oldest in-progress transaction id by xid / xmin, with name
> 5) oldest database datfrozenxid, with database name
[...]
> Also, adding an SRF providing the above in a useful format would be great for
> monitoring and for "remote debugging" of problems.

I concur it sounds very useful, and not terribly hard, but probably a
v16 project.

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 14 Feb 2022 20:21:41 -0800

> Don't truncate heap when VACUUM's failsafe is in effect.

That's true, but bear in mind that it only does so when the specific
table being vacuumed actually triggers the failsafe. I believe that
VACUUM(EMERGENCY) doesn't just limit itself to vacuuming tables where
this is guaranteed (or even likely). If I'm not mistaken, it's
possible (even likely) that there will be a table whose
age(relfrozenxid) is high enough for VACUUM(EMERGENCY) to target the
table, and yet not so high that the failsafe will kick in at the
earliest opportunity.

> situations FULL and ANALYZE).

It's possible for a manually issued VACUUM to directly disable
truncation (same with index_cleanup). Without getting into the
question of what the ideal behavior might be right now, I can say for
sure that it wouldn't be difficult to teach VACUUM(EMERGENCY) to pass
down the same options.

The failsafe is essentially a mechanism that dynamically changes these
options for an ongoing vacuum, once age(relfrozenxid) crosses a
certain threshold. There is nothing fundamentally special about that.

-- 
Peter Geoghegan

---

From: John Naylor <[email-masked]>
Date: Tue, 15 Feb 2022 13:04:42 +0700

> table, and yet not so high that the failsafe will kick in at the

Well, the point of inventing this new vacuum mode was because I
thought that upon reaching xidStopLimit, we couldn't issue commands,
period, under the postmaster. If it was easier to get a test instance
to xidStopLimit, I certainly would have discovered this sooner. When
Andres wondered about getting away from single user mode, I assumed
that would involve getting into areas too deep to tackle for v15. As
Robert pointed out, lazy_truncate_heap is the only thing that can't
happen for vacuum at this point, and fully explains why in versions <
14 our client's attempts to vacuum resulted in error. Since the
failsafe mode turns off truncation, vacuum should now *just work* near
wraparound. If there is any doubt, we can tighten the check for
entering failsafe.

Now, it's certainly possible that autovacuum is either not working at
all because of something broken, or is not working on the oldest
tables at the moment, so one thing we could do is to make VACUUM [with
no tables listed] get the tables from pg_class in reverse order of
max(xid age, mxid age). That way, the horizon will eventually pull
back over time and the admin can optionally cancel the vacuum at some
point. Since the order is harmless when it's not needed, we can do
that unconditionally.
-- 
John Naylor
EDB: http://www.enterprisedb.com

---

From: Robert Haas <[email-masked]>
Date: Tue, 15 Feb 2022 09:16:06 -0500

> wraparound. If there is any doubt, we can tighten the check for

+1 to all of that.

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 15 Feb 2022 09:28:47 -0800

On Mon, Feb 14, 2022 at 10:04 PM John Naylor
<[email-masked]> wrote:
> to xidStopLimit, I certainly would have discovered this sooner.

I did notice from my own testing of the failsafe (by artificially
inducing wraparound failure using an XID burning C function) that
autovacuum seemed to totally correct the problem, even when the system
had already crossed xidStopLimit - it came back on its own. I wasn't
completely sure of how robust this effect was, though.

> When

Obviously having to enter single user mode is horrid. If we can
reasonably update the advice to something more reasonable now, then
that would help users that find themselves in this situation a great
deal.

> point. Since the order is harmless when it's not needed, we can do

My ongoing work on freezing/relfrozenxid tends to make the age of
relfrozenxid much more indicative of the amount of work that VACUUM
would have to do when run -- not limited to freezing. You could
probably do this anyway, but it's nice that that'll be true.

-- 
Peter Geoghegan

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 15 Feb 2022 15:17:25 -0800

> completely sure of how robust this effect was, though.

It seemed worth noting this in comments above
should_attempt_truncation(). Pushed a commit to do that just now.

Thanks
-- 
Peter Geoghegan

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 17 Feb 2022 01:47:31 +0900

> completely sure of how robust this effect was, though.

FYI, I've tested the situation that I assumed autovacuum can not
correct the problem; when the system had already crossed xidStopLimit,
it keeps failing to vacuum on tables that appear in the front of the
list and have sufficient garbage to trigger the truncation but are not
older than the failsafe limit. But contrary to my assumption, it did
correct the problem since autovacuum continues to the next table in
the list even after an error. This probably means that autovacuum
eventually succeeds to process all tables that trigger the failsafe
mode, ensuring advancing datfrozenxid, which is great.

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/

---

From: Peter Geoghegan <[email-masked]>
Date: Wed, 16 Feb 2022 09:50:41 -0800

> mode, ensuring advancing datfrozenxid, which is great.

Right; it seems as if the situation is much improved, even when the
failsafe didn't prevent the system from going over xidStopLimit. If
autovacuum alone can bring the system back to a normal state as soon
as possible, without a human needing to do anything special, then
clearly the general risk is much smaller. Even this worst case
scenario where "the failsafe has failed" is not so bad anymore, in
practice. I don't think that it really matters if some concurrent
non-emergency VACUUMs fail when attempting to truncate the table (it's
no worse than ANALYZE failing, for example).

Good news!

-- 
Peter Geoghegan

---

From: Robert Haas <[email-masked]>
Date: Wed, 16 Feb 2022 12:56:42 -0500

> Good news!

+1. But I think we might want to try to write documentation around
this. We should explicitly tell people NOT to use single-user mode,
because that stupid message has been there for a long time and a lot
of people have probably internalized it by now. And we should also
tell them that they SHOULD check for prepared transactions, old
replication slots, etc.