From: | "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | RE: huge SubtransSLRU and SubtransBuffer wait_event |
Date: | 2024-02-02 06:47:47 |
Message-ID: | PH0PR11MB51911AD1ED7661D84F847A42D6422@PH0PR11MB5191.namprd11.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)albe(at)cybertec(dot)at>; pgsql-performance(at)lists(dot)postgresql(dot)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(dot)albe(at)cybertec(dot)at>
Sent: Thursday, February 1, 2024 8:42 PM
To: James Pang (chaolpan) <chaolpan(at)cisco(dot)com>; pgsql-performance(at)lists(dot)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Blanch Bataller | 2024-02-02 07:45:47 | Re: Memory growth using many named prepared statements, in spite of using DISCARD ALL afterwards. |
Previous Message | James Pang (chaolpan) | 2024-02-01 15:34:15 | RE: huge SubtransSLRU and SubtransBuffer wait_event |