Re: many backends hang on MultiXactOffsetSLRU

    There is no foreign keys, but there is one session who did transactions to tables with savepoints, one savepoints/per sql in same transaction. But sessions with query "SELECT “ do not use savepoints , just with a lot of sessions running same query and hang on MultiXact suddenly.  even only one session doing DML with savepoints , and all other queries sessions can see this kind of "MultiXact" waiting ,right? 

  

James Pang <jamespang886@gmail.com> 於 2024年9月10日週二 下午4:26寫道：

  There is no foreign keys, but there are several sessions who did transactions to tables with savepoints, one savepoints/per sql in same transaction. But sessions with query "SELECT “ do not use savepoints , just with a lot of sessions running same query and hang on MultiXact suddenly.  

Alvaro Herrera <alvherre@alvh.no-ip.org> 於 2024年9月10日週二 下午4:15寫道：

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)