RE: Guideline To Resolve LWLock:SubtransControlLock

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

 



Thanks, Jeremy …

 

“ 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 …”

 

This is why I posted here first. This particular wait state did not appear to be Aurora specific and was not listed as part of https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraPostgreSQL.Reference.html#AuroraPostgreSQL.Reference.Waitevents

 

I go back and forth posting issues between the two forums depending on the nature of it.

 

 

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

 

From: Jeremy Schneider
Sent: Monday, August 20, 2018 6:19 PM
To: Fred Habash
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
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

 


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

  Powered by Linux