On Fri, 14 Sep 2007, Peter Childs wrote:
Are you suggesting that reducing bgwriter_delay and bg_writer_percent would reduce the time spent doing commits? I get quite a few commits that take over 500ms (the point when i start logging queries).
One very common cause for transactions blocking for as much as several seconds is hitting a checkpoint, which in current versions causes a large amount of data to be written and synchronized to the physical disk. If you're already tracking long transactions and trying to figure out what's causing them, you should set checkpoint_warning to a high value (the maximum of 3600 is generally fine). That will give you a harmless warning every time a checkpoint occurs. If the slow transactions line up with checkpoints, then you might consider tuning or re-tuning the background writer to elimite the delay.
In this particular case, their system had already been tuned so aggressively that I wondered if the background writer was being a problem rather than a solution. Reducing the percentage would turn it down a bit; so would *increasing* the delay--they had already decreased it considerably, making it 4X as active as the default.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
