From: | Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: SubtransControlLock and performance problems |
Date: | 2020-02-17 15:03:56 |
Message-ID: | HE1P189MB026658CE70A359C45AB0F64F9D160@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
>From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
>Sent: Monday, February 17, 2020 10:53 AM
>To: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>; pgsql-performance(at)lists(dot)postgresql(dot)org <pgsql-performance(at)lists(dot)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-02-17 16:35:52 | Re: SubtransControlLock and performance problems |
Previous Message | Laurenz Albe | 2020-02-17 09:53:21 | Re: SubtransControlLock and performance problems |