Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 0F7762E37EE
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  4 Jan 2008 04:46:46 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 97497-01-5
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  4 Jan 2008 04:46:26 -0400 (AST)
Received: from fk-out-0910.google.com (fk-out-0910.google.com
 [209.85.128.185])
	by postgresql.org (Postfix) with ESMTP id 89BD72E3577
	for <pgsql-performance@postgresql.org>;
	Fri,  4 Jan 2008 04:46:39 -0400 (AST)
Received: by fk-out-0910.google.com with SMTP id b27so8124563fka.3
	for <pgsql-performance@postgresql.org>;
	Fri, 04 Jan 2008 00:46:38 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references;
	bh=xGiiyftAFJSM3YY2LZgFJSAvOG9qXN1qzhORuna9fU0=;
	b=LnHdv+pR0nfTIk4xiOMq2aeMCQCeAPhCvyYyrTS0FGDjhZ4DU6L5fE3Der19JT7/+/YdvhnLXt8QQyOl6RzeIJ9feh55Ccsuq1qddPLuLkSgVsjf+TVYIamy4yS3QbFCAgXHVL1NX5PGg67j69wzOSFJBjOCax+XrEX8yNFrhs8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references;
	b=kYDbcm42RzwWc2eoUQRF4/hVH0V/WnrZxvtXfQZMgx6Qjttgujbu2qIKj9MPnHMCPHCom0p4P5jaLkKczFi4LM9tpvQdT8s1obqx6LRBlW5sxXn8OOgrQXenmnKQKYLkRwoDOfBDpWy1lPgygNoxc/5JGnHhvl8D8gg5T+Vu3W8=
Received: by 10.78.200.3 with SMTP id x3mr19181572huf.0.1199436397688;
	Fri, 04 Jan 2008 00:46:37 -0800 (PST)
Received: by 10.78.150.15 with HTTP; Fri, 4 Jan 2008 00:46:37 -0800 (PST)
Message-ID: <a2de01dd0801040046y7f23468aueb914f5f7ccb359f@mail.gmail.com>
Date: Fri, 4 Jan 2008 08:46:37 +0000
From: "Peter Childs" <peterachilds@gmail.com>
Subject: Re: Commit takes a long time.
Cc: "Postgresql Performance" <pgsql-performance@postgresql.org>
In-Reply-To: <6663.1199378101@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_28394_15548379.1199436397677"
References: <a2de01dd0801030635v3491f576oabd4b3bb58d8328b@mail.gmail.com>
	<6663.1199378101@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200801/12
X-Sequence-Number: 28251

------=_Part_28394_15548379.1199436397677
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 03/01/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Peter Childs" <peterachilds@gmail.com> writes:
> > Using Postgresql 8.1.10 every so often I get a transaction that takes a
> > while to commit.
>
> > I log everything that takes over 500ms and quite reguallly it says
> things
> > like
>
> > 707.036 ms statement: COMMIT
>
> AFAIK there are only two likely explanations for that:
>
> 1. You have a lot of deferred triggers that have to run at COMMIT time.
>
> 2. The disk system gets so bottlenecked that fsync'ing the commit record
> takes a long time.
>
> If it's #2 you could probably correlate the problem with spikes in I/O
> activity as seen in iostat or vmstat.
>
> If it is a disk usage spike then I would make the further guess that
> what causes it might be a Postgres checkpoint.  You might be able to
> dampen the spike a bit by playing with the checkpoint parameters, but
> the only real fix will be 8.3's spread-out-checkpoints feature.
>
>                         regards, tom lane
>


2 Seams most likely  as they seam to occur more often when other when large
queries (they are often followed by a record for a very very long query in a
deferent transaction) or at particularly busy period when quite a lots of
other short queries are also taking place.

I planning an upgrade to 8.3 once its out anyway so that might increase
speed anyway.

Peter.

------=_Part_28394_15548379.1199436397677
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br><br><div><span class="gmail_quote">On 03/01/2008, <b class="gmail_sendername">Tom Lane</b> &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
&quot;Peter Childs&quot; &lt;<a href="mailto:peterachilds@gmail.com">peterachilds@gmail.com</a>&gt; writes:<br>&gt; Using Postgresql 8.1.10 every so often I get a transaction that takes a<br>&gt; while to commit.<br><br>&gt; I log everything that takes over 500ms and quite reguallly it says things
<br>&gt; like<br><br>&gt; 707.036 ms statement: COMMIT<br><br>AFAIK there are only two likely explanations for that:<br><br>1. You have a lot of deferred triggers that have to run at COMMIT time.<br><br>2. The disk system gets so bottlenecked that fsync&#39;ing the commit record
<br>takes a long time.<br><br>If it&#39;s #2 you could probably correlate the problem with spikes in I/O<br>activity as seen in iostat or vmstat.<br><br>If it is a disk usage spike then I would make the further guess that
<br>what causes it might be a Postgres checkpoint.&nbsp;&nbsp;You might be able to<br>dampen the spike a bit by playing with the checkpoint parameters, but<br>the only real fix will be 8.3&#39;s spread-out-checkpoints feature.<br><br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;regards, tom lane<br></blockquote></div><br><br>2 Seams most likely&nbsp; as they seam to occur more often when other when large queries (they are often followed by a record for a very very long query in a deferent transaction) or at particularly busy period when quite a lots of other short queries are also taking place. 
<br><br>I planning an upgrade to 8.3 once its out anyway so that might increase speed anyway.<br><br>Peter.<br>

------=_Part_28394_15548379.1199436397677--