Subject: Re: Commit takes a long time.
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Childs <peterachilds@gmail.com>,
	Postgresql Performance <pgsql-performance@postgresql.org>
In-Reply-To: <6663.1199378101@sss.pgh.pa.us>
References: <a2de01dd0801030635v3491f576oabd4b3bb58d8328b@mail.gmail.com>
	<6663.1199378101@sss.pgh.pa.us>
Content-Type: text/plain
Date: Fri, 04 Jan 2008 10:17:17 +0000
Message-Id: <1199441837.18598.18.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2008-01-03 at 11:35 -0500, Tom Lane 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.

I've seen 3 other reasons for this in the field while tuning people's
systems. In 8.3 we've fixed one, reduced the other and the third is
amenable to tuning via wal_buffers even in 8.1

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com