Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X1lS9-0006eA-OU for pgsql-performance@arkaria.postgresql.org; Mon, 30 Jun 2014 23:57:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X1lS9-0001aw-6V for pgsql-performance@arkaria.postgresql.org; Mon, 30 Jun 2014 23:57:41 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X1lS7-0001YD-Jb for pgsql-performance@postgresql.org; Mon, 30 Jun 2014 23:57:39 +0000 Received: from smtp88.ord1c.emailsrvr.com ([108.166.43.88]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1X1lS4-0004af-ND for pgsql-performance@postgresql.org; Mon, 30 Jun 2014 23:57:37 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp12.relay.ord1c.emailsrvr.com (SMTP Server) with ESMTP id 9E539809AE; Mon, 30 Jun 2014 19:57:35 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp12.relay.ord1c.emailsrvr.com (Authenticated sender: jeff.frost-AT-pgexperts.com) with ESMTPSA id 50DA680B85; Mon, 30 Jun 2014 19:57:34 -0400 (EDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_B30DD48F-697D-4395-A834-59D1E0DB4182" Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.2\)) Subject: Re: Postgres Replaying WAL slowly From: Jeff Frost In-Reply-To: <24455.1404169460@sss.pgh.pa.us> Date: Mon, 30 Jun 2014 16:57:33 -0700 Cc: Andres Freund , Matheus de Oliveira , Soni M , Heikki Linnakangas , pgsql-performance Message-Id: References: <20140630171416.GK21422@awork2.anarazel.de> <8DA8C140-818D-4184-AAE6-CA37278B983C@pgexperts.com> <20140630183908.GT26930@awork2.anarazel.de> <0A7B96D4-DB10-49A8-A268-F28960F2E137@pgexperts.com> <20300.1404156750@sss.pgh.pa.us> <20140630201523.GV26930@awork2.anarazel.de> <943ECC8E-8899-422E-937C-50DE20E1F7AC@pgexperts.com> <21474.1404160784@sss.pgh.pa.us> <2E438E7D-E3C1-47A1-A1AF-9FE50A14A302@pgexperts.com> <571D044B-260A-4E06-ACBC-8869B8F73C0F@pgexperts.com> <24455.1404169460@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.1878.2) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_B30DD48F-697D-4395-A834-59D1E0DB4182 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii On Jun 30, 2014, at 4:04 PM, Tom Lane wrote: > Ah ... that's more like a number I can believe something would have > trouble coping with. Did you see a noticeable slowdown with this? > Now that we've seen that number, of course it's possible there was an > even higher peak occurring when you saw the trouble. >=20 > Perhaps there's an O(N^2) behavior in StandbyReleaseLocks, or maybe > it just takes awhile to handle that many locks. >=20 > Did you check whether the locks were all on temp tables of the > ON COMMIT DROP persuasion? Unfortunately not, because I went for a poor man's: SELECT count(*) FROM = pg_locks WHERE mode =3D 'AccessExclusiveLock'=20 run in cron every minute. That said, I'd bet it was mostly ON COMMIT DROP temp tables. The unfortunate thing is I wouldn't know how to correlate that spike = with the corresponding slowdown because the replica is about 5.5hrs = lagged at the moment. Hopefully it will get caught up tonight and we can see if there's a = correlation tomorrow.= --Apple-Mail=_B30DD48F-697D-4395-A834-59D1E0DB4182 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
On Jun 30, 2014, at 4:04 PM, Tom Lane = <tgl@sss.pgh.pa.us> = wrote:

Ah ... that's more like a number I = can believe something would have
trouble coping with.  Did = you see a noticeable slowdown with this?
Now that we've seen that = number, of course it's possible there was an
even higher peak occurring when you saw the = trouble.

Perhaps there's an O(N^2) behavior in StandbyReleaseLocks, = or maybe
it just takes awhile to handle that many locks.

Did you check whether the = locks were all on temp tables of the
ON COMMIT DROP = persuasion?


Unfortunately= not, because I went for a poor man's: SELECT count(*) FROM pg_locks = WHERE mode =3D 'AccessExclusiveLock' 
run in cron every = minute.

That said, I'd bet it was mostly ON = COMMIT DROP temp tables.

The unfortunate thing = is I wouldn't know how to correlate that spike with the corresponding = slowdown because the replica is about 5.5hrs lagged at the = moment.

Hopefully it will get caught up tonight = and we can see if there's a correlation tomorrow.
= --Apple-Mail=_B30DD48F-697D-4395-A834-59D1E0DB4182--