Re: Commit takes a long time.

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

Is there anyway to speed this up?

Peter Childs

Hello

On 03/01/2008, Peter Childs <peterachilds(at)gmail(dot)com> wrote:
> 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
>
> Is there anyway to speed this up?
>

there can be two issues:
a) some trigger activity for DEFERRED constraints
b) slow write to WAL

http://www.westnet.com/~gsmith/content/postgresql/

in normal cases COMMIT is really fast operation.

Regards
Pavel Stehule

> Peter Childs
>
>
>
>

"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

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.

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

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