Re: huge SubtransSLRU and SubtransBuffer wait_event

2024-02-02 Thread Alvaro Herrera 
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 ;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 HerreraBreisgau, 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)

Re: huge SubtransSLRU and SubtransBuffer wait_event

2024-02-02 Thread Lars Aksel Opsahl 
>From: James Pang (chaolpan) Sent: Friday, February 2, 2024 
>7:47 AMTo: Laurenz Albe ; 
>[email protected] 
>Subject: RE: huge SubtransSLRU and 
>SubtransBuffer wait_event

>

>  Our case is   1) we use PL/PGSQL procedure1-->procedure2 (update table 
> ;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.


Hi


We had the same problem here 
https://gitlab.com/nibioopensource/resolve-overlap-and-gap . Here we can have 
more than 50 threads pushing millions of rows into common tables and one single 
final Postgis Topology structure as a final step. We also need to run try 
catch. The code is wrapped into functions and procedures and called from psql .


Just to test we tried compile with a higher number of subtrans locks and that 
just made this problem appear just a little bit later.


For us the solution was to save temporary results in array like this 
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/commit/679bea2b4b1ba4c9e84923b65c62c32c3aed6c21#a22cbe80eb0e36ea21e4f8036e0a4109b2ff2379_611_617

. The clue is to do as much work as possible without involving any common data 
structures for instance like using arrays to hold temp results and not use a 
shared final table before it's really needed.


Then later at a final step we insert all prepared data into a final common data 
structure and where we also try to avoid try catch when possible. Then system 
can then run with verry high CPU load for 99% of the work and just at then 
verry end we start to involve the common database structure.


Another thing to avoid locks is let each thread work on it's down data as much 
possible, this means breaking up the input and sort what's unique data for this 
tread and postpone the common data to a later stage. When for instance working 
with Postgis Topology we actually split data to be sure that not two threads 
works on the same area and then at later state another thread push shared 
data/area in to the final data structure.


This steps seems to have solved this problem for us which started out here 
https://postgrespro.com/list/thread-id/2478202


Lars

Re: huge SubtransSLRU and SubtransBuffer wait_event

2024-02-02 Thread Nikolay Samokhvalov 
On Thu, Feb 1, 2024 at 04:42 Laurenz Albe  wrote:

> Today, the only feasible solution is not to create more than 64
> subtransactions
> (savepoints or PL/pgSQL EXCEPTION clauses) per transaction.


Sometimes, a single subtransaction is enough to experience a bad
SubtransSLRU spike:
https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful#problem-4-subtrans-slru-overflow

I think 64+ nesting level is quite rare, but this kind of problem that hits
you when you have high XID growth (lots of writes) + long-running
transaction is quite easy to bump into. Or this case involving
MultiXactIDs:
https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/

Nik

>

Re: huge SubtransSLRU and SubtransBuffer wait_event

2024-02-02 Thread Laurenz Albe 
On Fri, 2024-02-02 at 02:04 -0800, Nikolay Samokhvalov wrote:
> On Thu, Feb 1, 2024 at 04:42 Laurenz Albe  wrote:
> > Today, the only feasible solution is not to create more than 64 
> > subtransactions
> > (savepoints or PL/pgSQL EXCEPTION clauses) per transaction.
> 
> I think 64+ nesting level is quite rare

It doesn't have to be 64 *nested* subtransactions.  This is enough:

CREATE TABLE tab (x integer);

DO
$$DECLARE
   i integer;
BEGIN
   FOR i IN 1..70 LOOP
  BEGIN
 INSERT INTO tab VALUES (i);
  EXCEPTION
 WHEN unique_violation THEN
NULL; -- ignore
  END;
   END LOOP;
END;$$;

Yours,
Laurenz Albe