From: Noah Misch <noah@leadboat.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: robertmhaas@gmail.com, Alexander Lakhin <exclusion@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Subject: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
Date: Wed, 10 Jan 2024 11:38:51 -0800
Message-ID: <20240110193851.f0.nmisch@google.com> (raw)
In-Reply-To: <CAH2-Wznv94Q_Td8OS8bAN7fYLpfU6CGgjn6Xau5eJ_sDxEGeBA@mail.gmail.com>
References: <17257-1e46de26bec11433@postgresql.org>
<d5d5af5d-ba46-aff3-9f91-776c70246cc3@gmail.com>
<CAH2-WzknORjSVufsDQKr7VVnXspRhdsjKBh8brwHEkrUpqVXjg@mail.gmail.com>
<20231225024402.77@rfd.leadboat.com>
<CAH2-Wz=X88igoxQvAGW2cFrTsEXK+VJJHap=90-z=juD5DqYdw@mail.gmail.com>
<20240106202413.e5@rfd.leadboat.com>
<CAH2-Wzk+H1PLdYm8K8M8Uaz_ikXNob8a2KBMd_JRALrzwPcjww@mail.gmail.com>
<20240109214447.88@rfd.leadboat.com>
<CAH2-Wzn57T=d7eB90m0wr+AiAXetk-NWA=ntS89R2mOcDimNsQ@mail.gmail.com>
<CAH2-Wznv94Q_Td8OS8bAN7fYLpfU6CGgjn6Xau5eJ_sDxEGeBA@mail.gmail.com>
On Wed, Jan 10, 2024 at 02:06:42PM -0500, Peter Geoghegan wrote:
> On Tue, Jan 9, 2024 at 6:16 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > On Tue, Jan 9, 2024 at 4:44 PM Noah Misch <noah@leadboat.com> wrote:
> > > On Tue, Jan 09, 2024 at 03:59:19PM -0500, Peter Geoghegan wrote:
> > > > Did the affected system that you investigated happen to have an
> > > > atypically high number of databases? The system 15.4 system that I saw
> > > > the problem on had almost 3,000 databases.
> > >
> > > No, single-digit database count here.
> >
> > My suspicion was that this factor might increase the propensity of
> > calls to GetOldestNonRemovableTransactionId (used to establish
> > VACUUM's OldestXmin) to not agree with the GlobalVis* based state used
> > by pruneheap.c, in the way that we need to worry about here (i.e.
> > inconsistencies that lead to VACUUM getting stuck inside
> > lazy_scan_prune's loop).
>
> Another question about your database/system: does VACUUM get stuck
> while scanning a page some time after it has already completed a round
> of index vacuuming?
I don't know. That particular system experienced the infinite loop only once.
> That's what I see here -- I don't think that pruning leaves behind
> even a single live heap tuple, despite scanning thousands of pages
> before reaching the page that it gets stuck on. Could be another red
> herring. But it doesn't seem impossible that some of the nbtree calls
> to procarray.c routines performed by code added by my commit
> 9dd963ae25, "Recycle nbtree pages deleted during same VACUUM", are
> somehow related. That is, that code could be part of the chain of
> events that cause the problem (whether or not the code itself is
> technically at fault).
>
> I'm referring to calls such as the
> "GetOldestNonRemovableTransactionId(NULL)" and
> "GlobalVisCheckRemovableFullXid()" calls that take place inside
> _bt_pendingfsm_finalize(). It's not like we do stuff like that in very
> many other places.
I see what you mean about the rarity and potential importance of
"GetOldestNonRemovableTransactionId(NULL)". There's just one other caller,
vac_update_datfrozenxid(), which calls it for an unrelated cause.
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: pgsql-bugs@postgresql.org
Cc: noah@leadboat.com, pg@bowt.ie, boekewurm+postgres@gmail.com, exclusion@gmail.com, pgsql-bugs@lists.postgresql.org, andres@anarazel.de
Subject: Re: BUG #17257: (auto)vacuum hangs within lazy_scan_prune()
In-Reply-To: <20240110193851.f0.nmisch@google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox