pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Emre Hasegeli <emre.hasegeli@tart.com.tr>
To: Jeff Janes <jeff.janes@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: dba@tart.com.tr <dba@tart.com.tr>
Subject: Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks
Date: Thu, 14 Mar 2013 19:07:28 +0200
Message-ID: <op.wtx4iqq3k2xoe5@hasegeli.local> (raw)
In-Reply-To: <CAMkU=1y=VjJ+fAGHk9EL5N2YkZTuQyWxAydYaZkKobXTE_49ow@mail.gmail.com>
References: <op.wtmkrq0nk2xoe5@hasegeli.local>
	<CAMkU=1y=VjJ+fAGHk9EL5N2YkZTuQyWxAydYaZkKobXTE_49ow@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

On Thu, 14 Mar 2013 06:53:55 +0200, Jeff Janes <jeff.janes@gmail.com>  
wrote:

> On Friday, March 8, 2013, Emre Hasegeli wrote:
>
> PostgreSQL writes several following logs during the problem which I never
>> saw before 9.2.3:
>>
>> LOG:  process 4793 acquired ExclusiveLock on extension of relation  
>> 305605
>> of database 16396 after 2348.675 ms
>>
>
> The key here is not that it is an ExclusiveLock, but rather than it is  
> the
> relation extension lock.  I don't think the extension lock is ever held
> across user-code, or transaction boundaries, or anything like that.  It  
> is
> held over some small IOs.  So if it blocked on that for over 2 seconds,  
> you
> almost surely have some serious IO congestion.
>
> And this particular message is probably more a symptom of that congestion
> than anything else.
>
> You said you rolled back to 9.2.2 and the stalling is still there.  Are  
> you
> still seeing the log message, or are you now seeing silently stalls?  Did
> you roll back all other changes that were made at the same time as the
> upgrade to 9.2.3 (kernel versions, filesystem changes/versions, etc.)?

I did not try with different kernel or file system. It was not because of
9.2.3, same problem occurred in both 9.2.2 and 9.2.3. Increasing max
connections make it worse. It lasts almost 15 minutes in the last time.

There were not much disk utilization while it is happening, "top" was
pointing out most of the CPU usage on the %sy column, there were no IO  
wait.
I saw "allocstalls" increasing on "atop". There were a lot of slow insert
statements in the logs except ExclusiveLock waits.

We were using 64 GiB of shared buffers. RhodiumToad suggested to reduce it
on the IRC channel. It did not happen since then.

It was a real problem for us. I could not find anything related to it. I
cannot let it happen again on the production environment but I would be
happy to share more experience, if it would help you fix it.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



view thread (7+ messages)  latest in thread

Message-ID: <op.wtx4iqq3k2xoe5@hasegeli.local>
Permalink:  ../op.wtx4iqq3k2xoe5@hasegeli.local/
Also on:    postgresql.org/message-id/op.wtx4iqq3k2xoe5@hasegeli.local

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: emre.hasegeli@tart.com.tr, jeff.janes@gmail.com, dba@tart.com.tr
  Subject: Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks
  In-Reply-To: <op.wtx4iqq3k2xoe5@hasegeli.local>
  References: <op.wtmkrq0nk2xoe5@hasegeli.local> <CAMkU=1y=VjJ+fAGHk9EL5N2YkZTuQyWxAydYaZkKobXTE_49ow@mail.gmail.com> <op.wtx4iqq3k2xoe5@hasegeli.local>

* 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