public inbox for [email protected]  

help / color / mirror / Atom feed

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

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

view thread (8+ 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]
  Subject: Re: query hangs out
  In-Reply-To: <[email protected]>

* 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