public inbox for [email protected]  

help / color / mirror / Atom feed

From: Антон Глушаков <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: query hangs out
Date: Tue, 20 May 2025 18:43:36 +0300
Message-ID: <CAHnOmafAW_Dqc8NEkmi=HOOMp3xf1DZdtOXauU2c1N5hq9BnVw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHnOmadn1UB-t-=Umd_TSEZ=kw48=ecX3EnesABxbPdboB-ZUQ@mail.gmail.com>
	<[email protected]>

Thanks for the advice.
I tried to remove all indexes and constraints from the table - it did not
help.
I have a copy of the data (before truncate) - I can test any hypothesis

вт, 20 мая 2025 г. в 18:25, Laurenz Albe <[email protected]>:

> On Tue, 2025-05-20 at 16:48 +0300, Антон Глушаков wrote:
> > I encountered a very strange behavior.
> > For any query (even a simple count(*) to one specific table (a small
> 30MB table with 3 indexes,
> > without any specific data types - everything is standard out of the box
> vanilla Postgres) -
> > the query hangs dead. Waited more than 24 hours - the query did not
> complete).
> >
> >
> > Similarly, the vacuum process to the table hangs.
> > Only Kill -9 with a full restart helps
> >
> > I get a backtrace, from it - I then examined the pg_multixact directory,
> which at the time of
> > the problem had swelled to 900MB and had several thousand files.
> > I excluded long and inactive transactions, as well as prepared
> statements.
> >
> > The workaround in the end was this - truncate the table (it was
> successful), then vacuum freeze
> > each DB, and after that the files from pg_multixact disappeared.
> >
> > What could it be? vacuum\freeze\mulitxact  settings are default.
> > At the same time, the value pg_database.datminmxid=1
> > Could the problem with the hang be related to the many old files in
> pg_multixact ? (judging by the backtrace - yes)
>
> I can't say for certain, but I have seen cases like that where index
> corruption sent
> processes into an endless loop.  Next time you could try to rebuild the
> indexes.
>
> Yours,
> Laurenz Albe
>

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]
  Subject: Re: query hangs out
  In-Reply-To: <CAHnOmafAW_Dqc8NEkmi=HOOMp3xf1DZdtOXauU2c1N5hq9BnVw@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