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"
