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 <alvhe...@alvh.no-ip.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" >
