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 <jamespang...@gmail.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 <alvhe...@alvh.no-ip.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) >> >
