Re: many backends hang on MultiXactOffsetSLRU

I hadn't found a satisfactory explanation about the top limit
related to SLRU, so this document will be useful. It's a nice development
that the relevant limit has been increased in pg17; I hope I don't
encounter a situation where this limit is exceeded in large systems.

Kind regards..

James Pang <jamespang886(at)gmail(dot)com>, 10 Eyl 2024 Sal, 11:35 tarihinde şunu
yazdı:

> 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)
>>>
>>