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@xxxxxxxxxxxxxx> 於 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"
