Re: Long Running Commits - Not Checkpoints

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD