Tom, folks,
I'm continuing to see a problem with checkpointing and clock-sweep.
Previously I thought that it was just the long checkpoint intervals on the
standard DBT2 test, but things get worse when you checkpoint more frequently:
60 minute checkpoint:
http://khack.osdl.org/stp/302458/results/0/
(look at the first chart)
Here you can see the huge dive in performance when the checkpoint hits.
Without it, our test scores would average 2000 notpm, better than Oracle on
low-end hardware like this.
Every 5 minutes:
http://khack.osdl.org/stp/302656/results/0/
(again, look at the notpm chart)
First off, note that the average NOTPM is 1320, which is a 20% decrease from
8.0.2. Second, you can see that the checkpoint spikes go just as low as
they do in the 60minute test. But, it appears that under the new buffer
manager, Postgres now needs 10 minutes or more of heavy activity to "recover"
from a checkpoint.
So this is obviously a major performance problem. It could be fixed by
turning off checkpointing completely, but I don't think that's really
feasable. Any clue on why clock-sweep should be so slammed by checkpoints?
--
Josh Berkus
Aglio Database Solutions
San Francisco