Re: lowering impact of checkpoints

On Tue, 25 Sep 2007, Gregory Stark wrote:

> I'm surprised you don't start by suggesting lowering bgwriter_delay for a busy
> dedicated system. Does it cause too much wasted cpu work in the "all" cycle in
> 8.2?

I've just found it easier to sort through this class of problem by getting
the maxpages parameters into at least the 200-500 range before even
thinking about lowering the delay. There may very well be a different way
to approach this problem by making the delay more of a primary tunable.
Certainly there's potentially an advantage to lowering the delay in that
it gets writes trickling out to disk more regularly.

> I also wonder if it doesn't make more sense in 8.2 if your goal is to avoid
> drop-outs to just give up on the lru cycle entirely and set the delay to
> something like 60s and the all_percent to 100.

There are some workloads where flushing the buffers that haven't been used
recently in the lru cycle is more useful than what the all scan does; it's
hard to figure out whether your system is such a case or not in 8.2
though.

In addition, the main problem with using a longer cycle/higher percentage
is that the way some operating systems buffer writes favors writing small
blocks more frequently. In the Linux case there are situations where
writes sit there for a full 30 seconds so getting the physical disk
started earlier is a benefit. I'd be concerned that all_percent=100 would
end up generating something close to a checkpoint I/O spike every cycle,
and that the background writer waiting for that big write to finish might
delay checkpoint requests from processing in a timely fashion.

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