Re: Long Running Commits - Not Checkpoints

On 13/09/2007, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
>
>
> Every time the all scan writes a buffer that is frequently used, that
> write has a good chance that it was wasted because the block will be
> modified again before checkpoint time. Your settings are beyond regular
> aggressive and into the hyperactive terrority where I'd expect such
> redundant writes are happening often. I'd suggest you try to move toward
> dropping bgwriter_all_percent dramatically from its current setting and
> see how far down you can go before it starts to introduce blocks at
> checkpoint time. With bgwriter_delay set to 1/4 the default, I would
> expect that even 5% would be a high setting for you. That may be a more
> dramatic change than you want to make at once though, so lowering it in
> that direction more slowly (perhaps drop 5% each day) and seeing whether
> things improve as that happens may make more sense.
>
>
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). I always thought oh just one of those things but if they
can be reduced by changing a few config variables that would be great. I'm
just trying to workout what figures are worth trying to see if I can reduce
them.

From time to time I get commits that take 6 or 7 seconds but not all the
time.

I'm currently working with the defaults.

Peter Childs