Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fsZQA-0002r7-Oe for pgsql-performance@arkaria.postgresql.org; Wed, 22 Aug 2018 20:08:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fsZQ9-0003TP-7m for pgsql-performance@arkaria.postgresql.org; Wed, 22 Aug 2018 20:08:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fsZQ8-0003TI-Tz for pgsql-performance@lists.postgresql.org; Wed, 22 Aug 2018 20:08:01 +0000 Received: from mail-io0-x241.google.com ([2607:f8b0:4001:c06::241]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fsZQ5-0000sI-9Q for pgsql-performance@lists.postgresql.org; Wed, 22 Aug 2018 20:08:00 +0000 Received: by mail-io0-x241.google.com with SMTP id l7-v6so2457289iok.6 for ; Wed, 22 Aug 2018 13:07:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:mime-version:to:cc:from:subject:date:importance :in-reply-to:references; bh=NkBov0MvoktQ3j1h8kTGObH0HjpiJ8Tp2RuoLhE7RPg=; b=gcje4Fdz4z0q4GNS5Mk0IvzX17Yl0WyVZzTYnRTmb6W51tlaQjq0uiIhghujSJk2yE /h9i6Fxm3VcCT1619rdY8LZ0GbBkn4IcWn3fDwgOuTOuyoY7vz6RDqrHpFsKtSaD2aCH ljgR6gypV0hQ8aA0LVPoD75ERpT8x6+073/Ej9NfgagRET3+ggMJCQIrtQyz704kP6O2 9v8K/3JEMnZcJGnycJ8H8BZvvwy0A+awL1h+YijsLYH9rry1jok5S124YMOobp9/Mor5 c/dg17qF9kVzuex8MkoTkvmBr+FWgUhTfipIsEcSohoGhGlqrQUu/8Lp0IzZloPBTi+d im5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:mime-version:to:cc:from:subject:date :importance:in-reply-to:references; bh=NkBov0MvoktQ3j1h8kTGObH0HjpiJ8Tp2RuoLhE7RPg=; b=FVNhRWxvwYMkTQwMmbdKhbHzKfGE/DZf9Gm8KuCxzK2QVW9bA6UDgp467dGPVRNQXF RGCOgrH7YqT3e7GdUBrEw/PkNXSlIrBpwUJ1w+V2GxXaqFc/CVL3izlDK8ZDf+N60vcB e7GjXkYfQcJ6dJjG797vmJkU93ZvIqhjqaVFNlPSEaCj0+M8wG3FxNM9w53wBhLLYTwK YyL0UTbIj8maBbseCW2otPgJ7XCnh84zQH8bJtanWINv9nbtiSRrBmidDrm4OqJyWYag jXiCCMvdRf7g/9v2fMkr5WUS2YeZ075oLZ1Jm30YCJ6qT8buvALwLOrNZ33t02bVxno9 0PBA== X-Gm-Message-State: APzg51BESI+rorQuv2sP29NX9rIrHp7dwfVq1QWdtlxB0sTBmSv1Rmez 9Q1lp4Zh9LI68cdgM3Sz/9s= X-Google-Smtp-Source: ANB0VdZd3q5wwJBwZ3pjkd/dzUh71f2YuYJQnZCH6NA2w9cNTk9B089llD6LQvTOixUFVpKMLPIBag== X-Received: by 2002:a5e:df04:: with SMTP id f4-v6mr13556376ioq.235.1534968474946; Wed, 22 Aug 2018 13:07:54 -0700 (PDT) Received: from ?IPv6:::ffff:172.25.137.39? ([165.215.94.3]) by smtp.gmail.com with ESMTPSA id y14-v6sm861203iol.60.2018.08.22.13.07.53 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 22 Aug 2018 13:07:53 -0700 (PDT) Message-ID: <5b7dc299.1c69fb81.b6e72.5a7b@mx.google.com> MIME-Version: 1.0 To: Jeremy Schneider Cc: "pgsql-performance@lists.postgresql.org" From: Fd Habash Subject: RE: Guideline To Resolve LWLock:SubtransControlLock Date: Wed, 22 Aug 2018 16:07:53 -0400 Importance: normal X-Priority: 3 In-Reply-To: <473e3dba-f052-23df-6f04-6df208220205@amazon.com> References: <20180816183632.lo34je5dudbros4a@alvherre.pgsql> <473e3dba-f052-23df-6f04-6df208220205@amazon.com> Content-Type: multipart/alternative; boundary="_D6401913-C903-4BD1-A39C-9F726DCDF614_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_D6401913-C903-4BD1-A39C-9F726DCDF614_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Jeremy =E2=80=A6 In your statement, what constitutes =E2=80=98heavy use of exception blocks= =E2=80=99? =20 Thanks=20 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@lists.postgresql.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=3D227 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. --=20 Jeremy Schneider Database Engineer Amazon Web Services --_D6401913-C903-4BD1-A39C-9F726DCDF614_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Jeremy =E2=80=A6

 

In your statement, what cons= titutes =E2=80=98heavy use of exception blocks=E2=80=99? =C2=A0

 

Thanks

 

 

 

I have seen=

applications grind to a halt on SubtransControlLoc= k when they make heavy

use of exception blocks in p= lpgsql code; in fact it's pretty

straightforward to= demonstrate this behavior with pgbench on community

PostgreSQL.

 

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

 

From: J= eremy Schneider
Sent: Monday, August 20, 2018 6:19 PM
T= o: Fred Habash
Cc: = pgsql-performance= @lists.postgresql.org
Subject: Re: Guideline To Resolve LWLoc= k: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 a= nd that's the place

to raise questions that are spe= cific 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=3D227

 

That said... FWIW, Aurora PostgreSQL version 9.= 6.3 uses parent/child

transaction relationships pre= tty much the same way that community

PostgreSQL 9.6= .3 does. The uses you pointed out (savepoints and

e= xceptions in plpgsql) are the most common causes of contention I've

seen - similar to what Alvaro said his experience is. I ha= ve seen

applications grind to a halt on SubtransCon= trolLock when they make heavy

use of exception bloc= ks in plpgsql code; in fact it's pretty

straightfor= ward 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.=C2=A0 But maybe they'll ju= st move the contention point a little bit

> back= wards without actually fixing anything.

 =

When it comes to resolution, I agree with Al= varo's assessment here;

unfortunately, I don't know= of a great solution on community PostgreSQL

outsid= e 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 signific= antly. That single global

control lock is hard to g= et around when you try to use subtransactions

at sc= ale.

 

-Jer= emy

 

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 an= d we can

put further Aurora-specific discussion on = the AWS forums.

 

--

Jeremy Schneider

Database Engineer

Amazon Web Services

 

= --_D6401913-C903-4BD1-A39C-9F726DCDF614_--