From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: SubtransControlLock and performance problems
Date: Mon, 17 Feb 2020 17:35:52 +0100
Message-ID: <61eeb178848bdc2e0dd6c81ce94f3ace7c1348ff.camel@cybertec.at> (raw)
In-Reply-To: <HE1P189MB026658CE70A359C45AB0F64F9D160@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
References: <HE1P189MB0266B070D1452B04A7C9F6D99D170@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
<eec0413432096c46c96580a7776f13a6f27a9858.camel@cybertec.at>
<HE1P189MB026658CE70A359C45AB0F64F9D160@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM>
On Mon, 2020-02-17 at 15:03 +0000, 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, Lars.Opsahl@nibio.no, pgsql-performance@lists.postgresql.org
Subject: Re: SubtransControlLock and performance problems
In-Reply-To: <61eeb178848bdc2e0dd6c81ce94f3ace7c1348ff.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox