Horton

Re: many backends hang on MultiXactOffsetSLRU

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Amine Tengilimoglu <aminetengilimoglu@gmail.com>
Cc: James Pang <jamespang886@gmail.com>, pgsql-performance@lists.postgresql.org
Date: 2024-09-10T08:15:42Z
Lists: pgsql-performance 
On 2024-Sep-10, Amine Tengilimoglu wrote:

>      Hi,
> 
>            I encountered this in a project we migrated to PostgreSQL
> before, and unfortunately, it’s a situation that completely degrades
> performance. We identified the cause as savepoints being used excessively
> and without control. Once they reduced the number of savepoints, the issue
> was resolved. However, the documentation also mentions that it could be
> caused by foreign keys.

Yeah, it's exactly the same problem; when it comes from savepoints the
issue is pg_subtrans, and when foreign keys are involved, it is
pg_multixact.  Both of those use the SLRU subsystem, which was heavily
modified in pg17 as I mentioned in my reply to James.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"I think my standards have lowered enough that now I think 'good design'
is when the page doesn't irritate the living f*ck out of me." (JWZ)