Re: [PERFORM] Commit takes a long time.
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.
Re: [PERFORM] Commit takes a long time.
On Thu, 2008-01-03 at 11:35 -0500, Tom Lane 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. 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Linux/PostgreSQL scalability issue - problem with 8 cores
James Mansion wrote: Jakub Ouhrabka wrote: How can we diagnose what is happening during the peaks? Can you try forcing a core from a bunch of the busy processes? (Hmm - does Linux have an equivalent to the useful Solaris pstacks?) There's a 'pstack' for Linux, shipped at least in Red Hat distributions (and possibly others, I'm not sure). It's a shell script wrapper around gdb, so easily ported to any Linux. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Linux/PostgreSQL scalability issue - problem with 8 cores
Jakub Ouhrabka wrote: How can we diagnose what is happening during the peaks? Can you try forcing a core from a bunch of the busy processes? (Hmm - does Linux have an equivalent to the useful Solaris pstacks?) James ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
