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

On Mon, Mar 3, 2008 at 8:25 AM, Douglas J Hunley <doug(at)hunley(dot)homeip(dot)net> wrote:
> Subject about says it all. Should I be more concerned about checkpoints
> happening 'frequently' or lasting 'longer'? In other words, is it ok to
> checkpoint say, every 5 minutes, if it only last a second or three or better
> to have checkpoints every 10 minutes that last half a minute? Stupid examples
> probably, but you get my point I hope :)

The answer is, of course, it depends.

If you do a lot of batch processing where you move a lot of data in a
stream into the database, then less, but larger checkpoints are
probably a win.

Or is this a transactional system that has to run transactions in
under x seconds? Then more, smaller checkpoints might make sense.

And then, you might be better off using the bgwriter. If tuned
properly, it will keep ahead of your checkpoints just enough that they
never have to happen. Comes with a price, some small % of performance
loss peak, in exchange for a smoother behaviour.