From: Peter Childs <peterachilds@gmail.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Commit takes a long time.
Date: Fri, 4 Jan 2008 08:46:37 +0000
Message-ID: <a2de01dd0801040046y7f23468aueb914f5f7ccb359f@mail.gmail.com> (raw)
In-Reply-To: <6663.1199378101@sss.pgh.pa.us>
References: <a2de01dd0801030635v3491f576oabd4b3bb58d8328b@mail.gmail.com>
<6663.1199378101@sss.pgh.pa.us>
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.
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: peterachilds@gmail.com
Subject: Re: Commit takes a long time.
In-Reply-To: <a2de01dd0801040046y7f23468aueb914f5f7ccb359f@mail.gmail.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