> 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
-----Original Message-----
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Sent: Friday, February 2, 2024 4:13 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance@lists.postgresql.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)