Checkpointing problem with new buffer mgr.

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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> 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?

Hm, notice that the processor utilization doesn't actually drop all that
much, so it seems it's not fundamentally an "I/O storm" kind of issue.

I'm thinking that the issue may be that just after a checkpoint, each
modification of a page incurs a dump of the whole page into WAL, with
attendant CRC-calculation and other costs. The reason the long
intercheckpoint interval yields such nifty performance is that it lets
you ramp up into a regime where almost none of the pages being touched
need to be dumped to WAL as a whole. Unfortunately that regime hasn't
got a lot to do with reality ...

You could test this theory by disabling the page-dump-out logic to see
what happens to the performance curve. In CVS tip, look at
XLogCheckBuffer() in src/backend/access/transam/xlog.c, and dike out the
whole large if() in it --- just have it set *lsn and return false.

(I assume this *is* CVS tip, or near to it? The recent CRC32 and
omit-the-hole changes should affect the costs of this quite a bit.)

regards, tom lane

Tom,

> (I assume this *is* CVS tip, or near to it?  The recent CRC32 and
> omit-the-hole changes should affect the costs of this quite a bit.)

It was a recent build. When was CRC32 checked in?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> (I assume this *is* CVS tip, or near to it? The recent CRC32 and
>> omit-the-hole changes should affect the costs of this quite a bit.)

> It was a recent build. When was CRC32 checked in?

The latest omit-the-hole change went in 2005-06-06 16:22 (EDT), so
anything older than that is probably not representative.

regards, tom lane

Tom Lane wrote:
>
> Hm, notice that the processor utilization doesn't actually drop all that
> much, so it seems it's not fundamentally an "I/O storm" kind of issue.

If I read the chart on the bottom of Josh's links correctly,
it looks to me like

the fast one is spending >50% CPU in "user" and <30% CPU in "wait"
the slow one seems to spend ~40% in "user" and almost 40% in "wait"

Wouldn't that suggest the ratio of new order tpm (1320/1779) is
pretty well explained by the corresponding less "user" cpu time (40/50)
that in turn was caused by the increased io wait time? Or am I reading
that wrong?

Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
>>>(I assume this *is* CVS tip, or near to it? The recent CRC32 and
>>>omit-the-hole changes should affect the costs of this quite a bit.)
>
>
>>It was a recent build. When was CRC32 checked in?
>
>
> The latest omit-the-hole change went in 2005-06-06 16:22 (EDT), so
> anything older than that is probably not representative.

The run with 60 minutes checkpoint timeout seems to be 06/04/05, the one
with 5 minutes is 06/18/05.

Best Regards,
Michael Paesold

Tom,

> The latest omit-the-hole change went in 2005-06-06 16:22 (EDT), so
> anything older than that is probably not representative.

Looks like this was 5/29. Re-running the tests with current CVS now.

--
Josh Berkus
Aglio Database Solutions
San Francisco