Re: Long Running Commits - Not Checkpoints

On Thu, 13 Sep 2007, Brad Nicholson wrote:

> A sysadmin looked at cache usage on the disk array. The read cache is
> being used heavily, and the write cache is not.

Given that information, you can take the below (which I was just about to
send before the above update came in) as something to think about and test
but perhaps not your primary line of attack. Even if my theory about the
exact mechanism involved isn't correct, the background writer is still
problematic in terms of its impact on the system when run as aggressively
as you're doing it; I'm not sure but I think that's even more true on 8.1
than it is on 8.2 where I did most my testing in this area.

> bgwriter_delay = 50
> bgwriter_lru_percent = 20.0
> bgwriter_lru_maxpages = 300
> bgwriter_all_percent = 20
> bgwriter_all_maxpages = 600

That was what I was expecting. Your all scan has the potential to be
writing 600*8K*(1/50 msec)=98MB/sec worth of data to your disk array.
Since some of this data has a random access component to it, your array
cannot be expected to keep with a real peak load; the only thing saving
you if something starts dirtying buffers as far as possible is that the
array cache is buffering things. And that 4GB worth of cache could be
filling in very little time.

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.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD