Re: Checkpointing problem with new buffer mgr.

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