RE: Guideline To Resolve LWLock:SubtransControlLock

Jeremy …

In your statement, what constitutes ‘heavy use of exception blocks’?

Thanks

I have seen
applications grind to a halt on SubtransControlLock when they make heavy
use of exception blocks in plpgsql code; in fact it's pretty
straightforward to demonstrate this behavior with pgbench on community
PostgreSQL.

----------------
Thank you

From: Jeremy Schneider
Sent: Monday, August 20, 2018 6:19 PM
To: Fred Habash
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: Guideline To Resolve LWLock:SubtransControlLock

On 8/17/18 11:07, Fred Habash wrote:
> Aurora Postgres 9.6.3

Hi Fred! The Amazon team does watch the AWS forums and that's the place
to raise questions that are specific to PostgreSQL on RDS or questions
specific to Aurora. In fact we would love to see this question over
there since it might be something other people see as well.

https://forums.aws.amazon.com/forum.jspa?forumID=227

That said... FWIW, Aurora PostgreSQL version 9.6.3 uses parent/child
transaction relationships pretty much the same way that community
PostgreSQL 9.6.3 does. The uses you pointed out (savepoints and
exceptions in plpgsql) are the most common causes of contention I've
seen - similar to what Alvaro said his experience is. I have seen
applications grind to a halt on SubtransControlLock when they make heavy
use of exception blocks in plpgsql code; in fact it's pretty
straightforward to demonstrate this behavior with pgbench on community
PostgreSQL.

On 8/20/18 14:00, Alvaro Herrera wrote:
>> And do both require a recompile?
>
> Yes. But maybe they'll just move the contention point a little bit
> backwards without actually fixing anything.

When it comes to resolution, I agree with Alvaro's assessment here;
unfortunately, I don't know of a great solution on community PostgreSQL
outside of trying to reduce the use of exception blocks in your plpgsql
code. Increasing the cache size can give a little more head room but
doesn't move the contention point significantly. That single global
control lock is hard to get around when you try to use subtransactions
at scale.

-Jeremy

P.S. This applies on the Aurora PostgreSQL 9.6.3 build too but I'm
discussing here in the context of community PostgreSQL code and we can
put further Aurora-specific discussion on the AWS forums.

--
Jeremy Schneider
Database Engineer
Amazon Web Services