RE: huge SubtransSLRU and SubtransBuffer wait_event

  Our case is   1) we use PL/PGSQL procedure1-->procedure2 (update table xxxx;commit);   2) application JDBC client call procedure1 (it's a long running job, sometimes it could last > 1hours).   During this time window,  other Postgresql JDBC clients (100-200) coming in in same time , then quickly see MultiXactoffset and SubtransSLRU increased very quickly. 
  Possible to increase  Subtrans SLRU buffer size ?    PL/PGSQL proc1--> procedure2(updates table) it use substransation in procedure2 ,right? 

Thanks,

James

-----Original Message-----
From: James Pang (chaolpan) 
Sent: Thursday, February 1, 2024 11:34 PM
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance@lists.postgresql.org
Subject: RE: huge SubtransSLRU and SubtransBuffer wait_event

Today, the only feasible solution is not to create more than 64 subtransactions (savepoints or PL/pgSQL EXCEPTION clauses) per transaction.

Don't use extensions or the JDBC driver option to simulate statement level rollback, that is the road to hell.
    You mean extensions to simulate a subtransaction like pg_background ?  for JDBC driver option to simulate statement level rollback, could you share more details ? 

Thanks,

James

-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, February 1, 2024 8:42 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>; pgsql-performance@lists.postgresql.org
Subject: Re: huge SubtransSLRU and SubtransBuffer wait_event

On Thu, 2024-02-01 at 11:50 +0000, James Pang (chaolpan) wrote:
> We have a Postgresqlv14.8 server, client use Postgresql JDBC 
> connections, today, our server see a lot of  “SubtransBuffer” and “SubtransSLRU” wait_event.
> Could you help direct me what’s the possible cause and how to resolve this waits ?

Today, the only feasible solution is not to create more than 64 subtransactions (savepoints or PL/pgSQL EXCEPTION clauses) per transaction.

Don't use extensions or the JDBC driver option to simulate statement level rollback, that is the road to hell.

Yours,
Laurenz Albe