Message-ID: <32ad0fda77629362dbdc90136e6d5f667d496e01.camel@cybertec.at>
Subject: Re: query hangs out
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?UTF-8?Q?=D0=90=D0=BD=D1=82=D0=BE=D0=BD_?=
 =?UTF-8?Q?=D0=93=D0=BB=D1=83=D1=88=D0=B0=D0=BA=D0=BE=D0=B2?=
	 <a.glushakov86@gmail.com>, pgsql-admin@lists.postgresql.org
Date: Tue, 20 May 2025 17:25:50 +0200
In-Reply-To: 
 <CAHnOmadn1UB-t-=Umd_TSEZ=kw48=ecX3EnesABxbPdboB-ZUQ@mail.gmail.com>
References: 
	<CAHnOmadn1UB-t-=Umd_TSEZ=kw48=ecX3EnesABxbPdboB-ZUQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.1 (3.56.1-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/32ad0fda77629362dbdc90136e6d5f667d496e01.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-05-20 at 16:48 +0300, =D0=90=D0=BD=D1=82=D0=BE=D0=BD =D0=93=D0=
=BB=D1=83=D1=88=D0=B0=D0=BA=D0=BE=D0=B2 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 v=
anilla Postgres) -
> the query hangs dead. Waited more than 24 hours - the query did not compl=
ete).
>=20
>=20
> Similarly, the vacuum process to the table hangs.
> Only Kill -9 with a full restart helps
>=20
> 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=
.
>=20
> The workaround in the end was this - truncate the table (it was successfu=
l), then vacuum freeze
> each DB, and after that the files from pg_multixact disappeared.
>=20
> What could it be? vacuum\freeze\mulitxact=C2=A0 settings are default.
> At the same time, the value pg_database.datminmxid=3D1
> Could the problem with the hang be related to the many old files in pg_mu=
ltixact ? (judging by the backtrace - yes)

I can't say for certain, but I have seen cases like that where index corrup=
tion sent
processes into an endless loop.  Next time you could try to rebuild the ind=
exes.

Yours,
Laurenz Albe