Re: [HACKERS] Checkpointing problem with new buffer mgr.
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Checkpointing problem with new buffer mgr.
Tom Lane wrote: Josh Berkus 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Checkpointing problem with new buffer mgr.
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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Checkpointing problem with new buffer mgr.
Josh Berkus 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Checkpointing problem with new buffer mgr.
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Checkpointing problem with new buffer mgr.
Josh Berkus 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
