Re: many backends hang on MultiXactOffsetSLRU

most of query sessions using jdbc connections, the one who use ODBC
one savepoint/per statement, but it does not run any "select for update;
savepoint;update", since row lock conflict, so not easy to touch same row
with update/delete, no idea how that create multixact? a MultiXact may
contain an update or delete Xid. ?
in this server, we see thousands of session hang on
‘MultixactOffsetSLRU" but they are in " bind " stage instead of "execute",
why a backend in "bind" need to access Multixact?

Thanks,

James

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 於 2024年9月10日週二 下午5:00寫道：

> On 2024-Sep-10, James Pang wrote:
>
> > 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?
>
> I think SELECT FOR UPDATE combined with savepoints can create
> multixacts, in absence of foreign keys.
>
> A query that's waiting doesn't need to have *created* the multixact or
> subtrans -- it is sufficient that it's forced to look it up.
>
> If thousands of sessions tried to look up different multixact values
> (spread across more than 8 pages), then thrashing of the cache would
> result, with catastrophic performance. This can probably be caused by
> some operation that creates one multixact per tuple in a few thousand
> tuples.
>
> Maybe you could ease this by doing VACUUM on the table (perhaps with a
> low multixact freeze age), which might remove some of the multixacts.
>
> --
> Álvaro Herrera Breisgau, Deutschland —
> https://www.EnterpriseDB.com/
> "Para tener más hay que desear menos"
>