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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
