Re: which is more important? freq of checkpoints or the duration of them?

On Mon, 3 Mar 2008, Chris Browne wrote:

> Now, if you have reasonable settings (I'm not sure how well its tuning
> is documented :-(), checkpoint "flushes" should be able to be short,
> however infrequent they may be. In effect, the "oops, the database got
> blocked by checkpoint flushing" issue should now be gone...

Ah, if only it were true. The background writer can be made to work
fairly well in circa early 8.1 setups where the shared_buffers cache is
small. But on more current systems where there's a lot of memory
involved, you can't get a tuning aggressive enough to make checkpoint
spikes small without wasting a bunch of I/O writing buffers that will just
get dirty again before the checkpoint. Since the kinds of systems that
have nasty checkpoint spikes are also I/O bound in general, there is no
good way to resolve that struggle using the code in 8.1 and 8.2.

The checkpoint_completion_target tunable and related code in 8.3 is the
first approach to this issue that has a good foundation even with larger
buffer caches. You can band-aid some cases well enough to improve things
with the background writer in earlier versions, but it's certainly not
guaranteed that it's possible even if you spend lots of time fiddling with
the settings.

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