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. ---------------- From: Jeremy Schneider 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 |
