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 1fsVMh-0006me-8t for pgsql-performance@arkaria.postgresql.org; Wed, 22 Aug 2018 15:48:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fsVMd-0006Gu-9S for pgsql-performance@arkaria.postgresql.org; Wed, 22 Aug 2018 15:48:07 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fsVMc-0006Gi-MS for pgsql-performance@lists.postgresql.org; Wed, 22 Aug 2018 15:48:07 +0000 Received: from mail-it0-x241.google.com ([2607:f8b0:4001:c0b::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fsVMZ-0004XI-Kx for pgsql-performance@lists.postgresql.org; Wed, 22 Aug 2018 15:48:05 +0000 Received: by mail-it0-x241.google.com with SMTP id h23-v6so3521702ita.5 for ; Wed, 22 Aug 2018 08:48:03 -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=5vDlD1YMIRF90iW3lqT/NerhTJjqcpT7N6JpEXuhmVY=; b=Ze1kG1Yu3SwG7dcU84mCUDK7jLHL8g3/d0pGeh/FE5PuyiR+7oKIMcRNpkjgHKQdoR O83z+3ltWFgWW11RS4mse3elVtRit3ibR5LUdU/AhTLx6IbefaxAf91tAWppRZtUijJH WypEbFujDdNBBBp/mKbZTdpFo8+wnEJ3dT7hzyqdBUfYSDVtFZGD9iypNLpDEq7xtyW8 ZmBtXenqG+TJORl6sDb+mPCXBZgQ2AXUlQlhCVAfbxhd94rWVE1DTn7feZfy56b+2l6z RtqjLLKeC6eF1KFq+ELeVODtQrR+clZ+QljAz3L/HDo8NnUZ/0FuoqUFCq60yVou+InU PCBw== 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=5vDlD1YMIRF90iW3lqT/NerhTJjqcpT7N6JpEXuhmVY=; b=pZogsF5d0U9k7YhSBBHQ9VE4xzCdHLFTUANJw9XAgY1MXWwl/WuPYz/nJ0xKtienDx V4tuxG8OYqiLsKAIOJ1U9Id1ilrjn5LBNyIYIlFeGMfV89Q+YcuQOrgSQlxMpcJg1xnL WkghjcrlA9KcwPF/yWejQtwZdzvg8I/S70AfzxPijH6RnKuqy8XHjy7lOIARnU6ZtQkH kHWkiIs2Z8VUJQawbNYjM7VLyzZEYULJtzLjmzWbLKcVd+CdHFuDuL06RtVZdaHpvk1k yYYI+bLglprkyDunqT5o5K/inpYCQ8lZvVgmjGUOIj9TcS/zEEOF7mnNcznnaHZYIlcJ 9OuA== X-Gm-Message-State: APzg51BOZFtp3h/bxMmLGj2koUUeRwVsvoHEOqATad4VlsvCh2lMFjBS 5E4DLbjzK98w1cbTiAWsGV8= X-Google-Smtp-Source: ANB0VdYQThmH3+E6f+hwe50tODBm51cKjxdxJFNo+ZEUQqZ+8/0HtyD84MRY2FimD1FOM7TyR6fOFA== X-Received: by 2002:a24:24c2:: with SMTP id f185-v6mr881251ita.18.1534952882635; Wed, 22 Aug 2018 08:48:02 -0700 (PDT) Received: from ?IPv6:::ffff:172.25.137.39? ([165.215.94.3]) by smtp.gmail.com with ESMTPSA id n140-v6sm964285itb.37.2018.08.22.08.48.01 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 22 Aug 2018 08:48:01 -0700 (PDT) Message-ID: <5b7d85b1.1c69fb81.97e37.6283@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 11:48:01 -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="_3AFF2C3F-BDA5-43A3-9CE1-934B6DC18901_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_3AFF2C3F-BDA5-43A3-9CE1-934B6DC18901_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Thanks, Jeremy =E2=80=A6 =E2=80=9C That said... FWIW, Aurora PostgreSQL version 9.6.3 uses parent/ch= ild transaction relationships pretty much the same way that community Postg= reSQL 9.6.3 does =E2=80=A6=E2=80=9D 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#AuroraPostg= reSQL.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@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 --_3AFF2C3F-BDA5-43A3-9CE1-934B6DC18901_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Thanks, Jeremy =E2=80=A6

 

=E2=80=9C That said.= .. FWIW, Aurora PostgreSQL version 9.6.3 uses parent/child transaction rela= tionships pretty much the same way that community PostgreSQL 9.6.3 does =E2= =80=A6=E2=80=9D

 

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.am= azon.com/AmazonRDS/latest/UserGuide/AuroraPostgreSQL.Reference.html#AuroraP= ostgreSQL.Reference.Waitevents

&= nbsp;

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

<= o:p> 

 

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

 

From: Jeremy Schneider
Sent: Monday, August 20, 2018 6:19 PM
<= b>To: Fred Habash
Cc: <= /b>pgsql-performa= nce@lists.postgresql.org
Subject: Re: Guideline To Resolve LW= Lock:SubtransControlLock

 

On 8/17/18 11:07, Fred Habash wrote:

> Aurora Postgres 9.6.3

 

Hi Fred! The Amazon team does watch the AWS forum= s 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

<= p class=3DMsoNormal>seen - similar to what Alvaro said his experience is. I= have seen

applications grind to a halt on Subtrans= ControlLock when they make heavy

use of exception b= locks in plpgsql code; in fact it's pretty

straight= forward 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= just move the contention point a little bit

> b= ackwards without actually fixing anything.

&nb= sp;

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

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

out= side of trying to reduce the use of exception blocks in your plpgsql

code. Increasing the cache size can give a little more he= ad room but

doesn't move the contention point signi= ficantly. That single global

control lock is hard t= o 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 a= nd we can

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

 

--

Jeremy Schneider

Database Engineer

Amazon Web Services

 

= --_3AFF2C3F-BDA5-43A3-9CE1-934B6DC18901_--