Re: Bgwriter behavior

A quick $0.02 on how DB2 does this (at least in 7.x).

They used a combination of everything that's been discussed. The first
priority of their background writer was to keep the LRU end of the cache
free so individual backends would never have to wait to get a page.
Then, they would look to pages that had been dirty for 'a long time',
which was user configurable. Pages older than this setting were
candidates to be written out even if they weren't close to LRU. Finally,
I believe there were also settings for how often the writer would fire
up, and how much work it would do at once.

I agree that the first priority should be to keep clean pages near LRU,
but that you also don't want to get hammered at checkpoint time. I think
what might be interesting to consider is keeping a list of dirty pages,
which would remove the need to scan a very large buffer. Of course, in
an environment with a heavy update load, it could be better to just
scan the buffers, especially if you don't do a clock-sweep but instead
look at where the last page you wrote out has ended up in the LRU list
since you last ran, and start scanning from there (by definition
everything after that page would have to be clean). Of course this is
just conjecture on my part and would need testing to verify, and it's
obviously beyond the scope of 8.0.

As for 8.0, I suspect at this point it's probably best to just go with
whatever method has the smallest amount of code impact unless it's
inherenttly broken.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"