Re: Why is checkpoint so costly?

Josh Berkus <josh(at)agliodbs(dot)com> writes:

> Folks,
>
> Going over some performance test results at OSDL, our single greatest
> performance issue seems to be checkpointing. Not matter how I fiddle
> with it, checkpoints seem to cost us 1/2 of our throughput while they're
> taking place. Overally, checkpointing costs us about 25% of our
> performance on OLTP workloads.

I think this is a silly statement. *Of course* checkpointing is a big
performance "issue". Checkpointing basically *is* what the database's job is.
It stores data; checkpointing is the name for the process of storing the data.

Looking at the performance without counting the checkpoint time is cheating,
the database hasn't actually completed processing the data; it's still sitting
in the pipeline of the WAL log.

The question should be why is there any time when a checkpoint *isn't*
happening? For maximum performance the combination of bgwriter (basically
preemptive checkpoint i/o) and the actual checkpoint i/o should be executing
at a more or less even pace throughout the time interval between checkpoints.

I do have one suggestion. Is the WAL log on a separate set of drives from the
data files? If not then the checkpoint (and bgwriter i/o) will hurt WAL log
performance by forcing the drive heads to move away from their sequential
writing of WAL logs.

That said, does checkpointing (and bgwriter i/o) require rereading the WAL
logs? If so then if the buffers aren't found in cache then it'll cause some
increase in seek latency just from that even if it does have a dedicated
set of drives.

--
greg