I hadn't found a satisfactory explanation about the top limit related to SLRU, so this document will be useful. It's a nice development that the relevant limit has been increased in pg17; I hope I don't encounter a situation where this limit is exceeded in large systems.
Kind regards.. James Pang <jamespang...@gmail.com>, 10 Eyl 2024 Sal, 11:35 tarihinde şunu yazdı: > 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) >>> >>