From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Антон Глушаков <a(dot)glushakov86(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: query hangs out |
Date: | 2025-05-20 15:25:50 |
Message-ID: | 32ad0fda77629362dbdc90136e6d5f667d496e01.camel@cybertec.at |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Антон Глушаков | 2025-05-20 15:43:36 | Re: query hangs out |
Previous Message | Антон Глушаков | 2025-05-20 13:48:02 | query hangs out |