| From: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
|---|---|
| To: | "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> |
| Cc: | 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 08:12:48 |
| Message-ID: | 202402020812.6yy7rpe2xfhb@alvherre.pgsql |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On 2024-Feb-02, James Pang (chaolpan) wrote:
> Possible to increase Subtrans SLRU buffer size ?
Not at present -- you need to recompile after changing
NUM_SUBTRANS_BUFFERS in src/include/access/subtrans.h,
NUM_MULTIXACTOFFSET_BUFFERS and NUM_MULTIXACTMEMBER_BUFFERS in
src/include/access/multixact.h.
There's pending work to let these be configurable in version 17.
> 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.
> PL/PGSQL proc1--> procedure2(updates table) it use substransation in
> procedure2 ,right?
If your functions/procedures use EXCEPTION clauses, that would create
subtransactions also.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Lars Aksel Opsahl | 2024-02-02 09:26:49 | Re: huge SubtransSLRU and SubtransBuffer wait_event |
| Previous 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. |