Re: Checkpoint cost, looks like it is WAL/CRC

Josh Berkus <josh@agliodbs.com> writes:

> I think this test run http://khack.osdl.org/stp/302903/results/0/, with a 
> 30-min checkpoint  shows pretty clearly that the behavior of the 
> performance drop is consistent with needing to "re-prime" the WAL will 
> full page images.   Each checkpoint drops performance abruptly, and then 
> slowly recovers until the next checkpoint.

A 30-min checkpoint means that fsyncs will be happening on up to 30 minutes of
i/o on each database file. It could be the full page images that's slowing it
down or it could just be that the system is swamped with i/o that's been put
off for the last 30 minutes.

It's not nearly as bad in this case as it has been in the previous samples
since at least this test runs for 5 full checkpoint cycles but it's still
fairly unrealistic. And that last checkpoint, almost 20% of the i/o of the
test is only partially included in the timing data.

For any benchmarking to be meaningful you have to set the checkpoint interval
to something more realistic. Something like 5 minutes. That way when the final
checkpoint cycle isn't completely included in the timing data you'll at least
be missing a statistically insignificant portion of the work.

-- 
greg