Thread: Checkpointing problem with new buffer mgr.

Checkpointing problem with new buffer mgr.

From
Josh Berkus
Date:
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


Re: Checkpointing problem with new buffer mgr.

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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


Re: Checkpointing problem with new buffer mgr.

From
Josh Berkus
Date:
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


Re: Checkpointing problem with new buffer mgr.

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.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


Re: Checkpointing problem with new buffer mgr.

From
Ron Mayer
Date:
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
almost40% 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?


Re: Checkpointing problem with new buffer mgr.

From
Michael Paesold
Date:
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.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


Re: Checkpointing problem with new buffer mgr.

From
Josh Berkus
Date:
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