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(at)gmail(dot)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(at)alvh(dot)no-ip(dot)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)
>>
>