Re: Long Running Commits - Not Checkpoints

[Greg Smith <gsmith(at)gregsmith(dot)com>]

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