Re: SubtransControlLock and performance problems

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




>From: Laurenz Albe <laurenz.albe@cybertec.at>

>Sent: Monday, February 17, 2020 10:53 AM

>To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>

>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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux