Re: SubtransControlLock and performance problems
Lars Aksel Opsahl wrote: > What happens is that after some minutes the CPU can fall to maybe 20% usage > and most of > the threads are blocked by SubtransControlLock, and when the number > SubtransControlLock > goes down the CPU load increases again. The jobs usually goes through without > any errors, > but it takes to long time because of the SubtransControlLock blocks. That's typically a sign that you are using more than 64 subtransactions per transaction. That could either be SAVEPOINT SQL statements or PL/pgSQL code with blocks containing the EXCEPTION clause. The data structure in shared memory that holds information for each session can cache 64 subtransactions, beyond that it has to access "pg_subtrans" to get the required information, which leads to contention. Often the problem is caused by a misguided attempt to wrape every single statement in a subtransaction to emulate the behavior of other database systems, for example with the "autosave = always" option of the JDBC driver. The solution is to use fewer subtransactions per transaction. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: SubtransControlLock and performance problems
>From: Laurenz Albe >Sent: Monday, February 17, 2020 10:53 AM >To: Lars Aksel Opsahl ; >[email protected] >Subject: Re: SubtransControlLock and performance problems > >Lars Aksel Opsahl wrote: >> What happens is that after some minutes the CPU can fall to maybe 20% usage >> and most of >> the threads are blocked by SubtransControlLock, and when the number >> SubtransControlLock >> goes down the CPU load increases again. The jobs usually goes through >> without any errors, >> but it takes to long time because of the SubtransControlLock blocks. > >That's typically a sign that you are using more than 64 subtransactions per >transaction. > >That could either be SAVEPOINT SQL statements or PL/pgSQL code with blocks >containing the EXCEPTION clause. > >The data structure in shared memory that holds information for each session >can cache 64 subtransactions, beyond that it has to access "pg_subtrans" to get > the required information, which leads to contention. > > Often the problem is caused by a misguided attempt to wrape every single > statement in a subtransaction to emulate the behavior of other database > systems, for example with the "autosave = always" option of the JDBC driver. > > The solution is to use fewer subtransactions per transaction. > Hi I have tested in branch ( https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_func) where I use only have functions and no procedures and I still have the same problem with subtransaction locks. Can I based on this assume that the problem is only related to exceptions ? Does this mean that if have 32 threads running in parallel and I get 2 exceptions in each thread I have reached a state where I will get contention ? Is it any way increase from 64 to a much higher level, when compiling the code ? Basically what I do here is that I catch exceptions when get them and tries to solve the problem in a alternative way. Thanks a lot. Lars
Re: SubtransControlLock and performance problems
On Mon, 2020-02-17 at 15:03 +, Lars Aksel Opsahl wrote: > I have tested in branch ( > https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_func) > where I use only have functions and no procedures and I still have the same > problem with subtransaction locks. > > Can I based on this assume that the problem is only related to exceptions ? No, it is related to BEGIN ... EXCEPTION ... END blocks, no matter if an exception is thrown or not. As soon as execution enters such a block, a subtransaction is started. > Does this mean that if have 32 threads running in parallel and I get 2 > exceptions in each thread I have reached a state where I will get contention ? No, it means that if you enter a block with an EXCEPTION clause more than 64 times in a single transaction, performance will drop. > Is it any way increase from 64 to a much higher level, when compiling the > code ? Yes, you can increase PGPROC_MAX_CACHED_SUBXIDS in src/include/storage/proc.h > Basically what I do here is that I catch exceptions when get them and tries > to solve the problem in a alternative way. Either use shorter transactions, or start fewer subtransactions. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: SubtransControlLock and performance problems
Hi po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe napsal: > On Mon, 2020-02-17 at 15:03 +, Lars Aksel Opsahl wrote: > > I have tested in branch ( > https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_func > ) > > where I use only have functions and no procedures and I still have the > same problem with subtransaction locks. > > > > Can I based on this assume that the problem is only related to > exceptions ? > > No, it is related to BEGIN ... EXCEPTION ... END blocks, no matter if > an exception is thrown or not. > > As soon as execution enters such a block, a subtransaction is started. > > > Does this mean that if have 32 threads running in parallel and I get 2 > exceptions in each thread I have reached a state where I will get > contention ? > > No, it means that if you enter a block with an EXCEPTION clause more > than 64 times in a single transaction, performance will drop. > > > Is it any way increase from 64 to a much higher level, when compiling > the code ? > > Yes, you can increase PGPROC_MAX_CACHED_SUBXIDS in > src/include/storage/proc.h > > > Basically what I do here is that I catch exceptions when get them and > tries to solve the problem in a alternative way. > > Either use shorter transactions, or start fewer subtransactions. > > Yours, > Laurenz Albe > it is interesting topic, but I don't see it in my example CREATE OR REPLACE FUNCTION public.fx(integer) RETURNS void LANGUAGE plpgsql AS $function$ begin for i in 1..$1 loop begin --raise notice 'xx'; exception when others then raise notice 'yyy'; end; end loop; end; $function$ the execution time is without performance drops. Is there some prerequisite to see performance problems? Pavel -- > Cybertec | https://www.cybertec-postgresql.com > > > >
Re: SubtransControlLock and performance problems
Pavel Stehule writes: > po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe > napsal: >> Either use shorter transactions, or start fewer subtransactions. > it is interesting topic, but I don't see it in my example > CREATE OR REPLACE FUNCTION public.fx(integer) > RETURNS void > LANGUAGE plpgsql > AS $function$ > begin > for i in 1..$1 loop > begin > --raise notice 'xx'; > exception when others then > raise notice 'yyy'; > end; > end loop; > end; > $function$ This example doesn't create or modify any table rows within the subtransactions, so (I think) we won't assign XIDs to them. It's consumption of subtransaction XIDs that causes the issue. regards, tom lane
Re: SubtransControlLock and performance problems
po 17. 2. 2020 v 19:23 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > po 17. 2. 2020 v 17:36 odesílatel Laurenz Albe > > > napsal: > >> Either use shorter transactions, or start fewer subtransactions. > > > it is interesting topic, but I don't see it in my example > > > CREATE OR REPLACE FUNCTION public.fx(integer) > > RETURNS void > > LANGUAGE plpgsql > > AS $function$ > > begin > > for i in 1..$1 loop > > begin > > --raise notice 'xx'; > > exception when others then > > raise notice 'yyy'; > > end; > > end loop; > > end; > > $function$ > > This example doesn't create or modify any table rows within the > subtransactions, so (I think) we won't assign XIDs to them. > It's consumption of subtransaction XIDs that causes the issue. > I tested CREATE OR REPLACE FUNCTION public.fx(integer) RETURNS void LANGUAGE plpgsql AS $function$ begin for i in 1..$1 loop begin insert into foo values(i); exception when others then raise notice 'yyy'; end; end loop; end; $function$ and I don't see any significant difference between numbers less than 64 and higher > regards, tom lane >
Re: SubtransControlLock and performance problems
On 2020-Feb-16, Lars Aksel Opsahl wrote: > On a server with 32 cores and 250 GB memory, with CentOS 7 and kernel > 4.4.214-1.el7.elrepo.x86_64, I try to run 30 parallel threads using > dblink. (https://github.com/larsop/postgres_execute_parallel) . I have > tried to disconnect and reconnect in the dblink code and that did not > help. I think one issue is that pg_clog has 128 buffers (per commit 5364b357fb1) while subtrans only has 32. It might be productive to raise the number of subtrans buffers (see #define NUM_SUBTRANS_BUFFERS in src/include/access/subtrans.h; requires a recompile.) Considering that each subtrans entry is 16 times larger than clog (2 bits vs. 4 bytes), you'd require 2048 subtrans buffers to cover the same XID range without I/O if my math is right. That's only 16 MB ... though slru.c code might not be prepared to deal with that many buffers. Worth some experimentation, I guess. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
