On 03/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Peter Childs" <[EMAIL PROTECTED]> 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.
