Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

> 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.

one question:
we need to increase all SLRU buffers together , MULTIXACT, XACT,
Subtrans, COMMIT TS , for example, got all of them doubled based on
existing size ? or only increase Subtrans , or Subtrans and multixact ?

Thanks,

James

James Pang (chaolpan) <chaolpan(at)cisco(dot)com> 於 2024年3月1日週五 下午2:45寫道：

>
>
> -----Original Message-----
> From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
> Sent: Friday, February 2, 2024 4:13 PM
> 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
> Subject: Re: huge SubtransSLRU and SubtransBuffer wait_event
>
> 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)
>