pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCommit takes a long time.
5+ messages / 4 participants
[nested] [flat]
* Commit takes a long time.
@ 2008-01-03 14:35 Peter Childs <peterachilds@gmail.com>
2008-01-03 16:20 ` Re: Commit takes a long time. Pavel Stehule <pavel.stehule@gmail.com>
2008-01-03 16:35 ` Re: Commit takes a long time. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Peter Childs @ 2008-01-03 14:35 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Commit takes a long time.
2008-01-03 14:35 Commit takes a long time. Peter Childs <peterachilds@gmail.com>
@ 2008-01-03 16:20 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2008-01-03 16:20 UTC (permalink / raw)
To: Peter Childs <peterachilds@gmail.com>; +Cc: pgsql-performance
Hello
On 03/01/2008, Peter Childs <peterachilds@gmail.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
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Commit takes a long time.
2008-01-03 14:35 Commit takes a long time. Peter Childs <peterachilds@gmail.com>
@ 2008-01-03 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-04 08:46 ` Re: Commit takes a long time. Peter Childs <peterachilds@gmail.com>
2008-01-04 10:17 ` Re: Commit takes a long time. Simon Riggs <simon@2ndquadrant.com>
1 sibling, 2 replies; 5+ messages in thread
From: Tom Lane @ 2008-01-03 16:35 UTC (permalink / raw)
To: Peter Childs <peterachilds@gmail.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Commit takes a long time.
2008-01-03 14:35 Commit takes a long time. Peter Childs <peterachilds@gmail.com>
2008-01-03 16:35 ` Re: Commit takes a long time. Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-01-04 08:46 ` Peter Childs <peterachilds@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Peter Childs @ 2008-01-04 08:46 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Commit takes a long time.
2008-01-03 14:35 Commit takes a long time. Peter Childs <peterachilds@gmail.com>
2008-01-03 16:35 ` Re: Commit takes a long time. Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-01-04 10:17 ` Simon Riggs <simon@2ndquadrant.com>
1 sibling, 0 replies; 5+ messages in thread
From: Simon Riggs @ 2008-01-04 10:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Childs <peterachilds@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-01-04 10:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-03 14:35 Commit takes a long time. Peter Childs <peterachilds@gmail.com>
2008-01-03 16:20 ` Pavel Stehule <pavel.stehule@gmail.com>
2008-01-03 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-04 08:46 ` Peter Childs <peterachilds@gmail.com>
2008-01-04 10:17 ` Simon Riggs <simon@2ndquadrant.com>
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