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)