pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jeff Frost <jeff@pgexperts.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andres Freund <andres@2ndquadrant.com>
Cc: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: Soni M <diptatapa@gmail.com>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Postgres Replaying WAL slowly
Date: Tue, 1 Jul 2014 10:12:14 -0700
Message-ID: <608DF51D-AEFB-422B-AD2C-ABA9A114AA27@pgexperts.com> (raw)
In-Reply-To: <BB7C727F-201D-4CA7-AB00-8EDF6EBFD04B@pgexperts.com>
References: <20140630171416.GK21422@awork2.anarazel.de>
	<CAAMgDXnT8cQbwxcNXfFKONrRVAKoGhR-3FWYSX_5gJcFrEMPMQ@mail.gmail.com>
	<8DA8C140-818D-4184-AAE6-CA37278B983C@pgexperts.com>
	<20140630183908.GT26930@awork2.anarazel.de>
	<0A7B96D4-DB10-49A8-A268-F28960F2E137@pgexperts.com>
	<F3E1168D-8D49-407E-A8B7-B3ED81C93DCB@pgexperts.com>
	<20300.1404156750@sss.pgh.pa.us>
	<C64129CF-D034-4311-AD32-09EF048C3E93@pgexperts.com>
	<CAJghg4Lq2rg7mMzRfEartAQK1c83veoW3TWroQsSvXMqBQ9h+w@mail.gmail.com>
	<B22B06CF-2BC0-4AB6-8E3C-FACDF8193F23@pgexperts.com>
	<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>
	<BB7C727F-201D-4CA7-AB00-8EDF6EBFD04B@pgexperts.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

On Jun 30, 2014, at 4:57 PM, Jeff Frost <jeff@pgexperts.com> wrote:

> 
> 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 = '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.

And indeed it did catch up overnight and the lag increased shortly after a correlating spike in AccessExclusiveLocks that were generated by temp table creation with on commit drop.

view thread (33+ messages)  latest in thread

Message-ID: <608DF51D-AEFB-422B-AD2C-ABA9A114AA27@pgexperts.com>
Permalink:  ../608DF51D-AEFB-422B-AD2C-ABA9A114AA27@pgexperts.com/
Also on:    postgresql.org/message-id/608DF51D-AEFB-422B-AD2C-ABA9A114AA27@pgexperts.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jeff@pgexperts.com, tgl@sss.pgh.pa.us, andres@2ndquadrant.com, matioli.matheus@gmail.com, diptatapa@gmail.com, hlinnakangas@vmware.com
  Subject: Re: Postgres Replaying WAL slowly
  In-Reply-To: <608DF51D-AEFB-422B-AD2C-ABA9A114AA27@pgexperts.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox