From: | "Peter Childs" <peterachilds(at)gmail(dot)com> |
---|---|
To: | |
Cc: | "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Commit takes a long time. |
Date: | 2008-01-04 08:46:37 |
Message-ID: | a2de01dd0801040046y7f23468aueb914f5f7ccb359f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 03/01/2008, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Peter Childs" <peterachilds(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2008-01-04 10:17:17 | Re: Commit takes a long time. |
Previous Message | Jakub Ouhrabka | 2008-01-03 19:26:30 | Re: Linux/PostgreSQL scalability issue - problem with 8 cores |