Обсуждение: which is more important? freq of checkpoints or the duration of them?
Subject about says it all. Should I be more concerned about checkpoints happening 'frequently' or lasting 'longer'? In other words, is it ok to checkpoint say, every 5 minutes, if it only last a second or three or better to have checkpoints every 10 minutes that last half a minute? Stupid examples probably, but you get my point I hope :) -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net
On Mon, Mar 3, 2008 at 8:25 AM, Douglas J Hunley <doug@hunley.homeip.net> wrote: > Subject about says it all. Should I be more concerned about checkpoints > happening 'frequently' or lasting 'longer'? In other words, is it ok to > checkpoint say, every 5 minutes, if it only last a second or three or better > to have checkpoints every 10 minutes that last half a minute? Stupid examples > probably, but you get my point I hope :) The answer is, of course, it depends. If you do a lot of batch processing where you move a lot of data in a stream into the database, then less, but larger checkpoints are probably a win. Or is this a transactional system that has to run transactions in under x seconds? Then more, smaller checkpoints might make sense. And then, you might be better off using the bgwriter. If tuned properly, it will keep ahead of your checkpoints just enough that they never have to happen. Comes with a price, some small % of performance loss peak, in exchange for a smoother behaviour.
doug@hunley.homeip.net (Douglas J Hunley) writes: > Subject about says it all. Should I be more concerned about checkpoints > happening 'frequently' or lasting 'longer'? In other words, is it ok to > checkpoint say, every 5 minutes, if it only last a second or three or better > to have checkpoints every 10 minutes that last half a minute? Stupid examples > probably, but you get my point I hope :) Well, with the (new-in-8.1) background writer, you should be able to have whatever combination might appear attractive, as the point of the background writer is to push out dirty pages. Pre-8.1, your choice would be either to: a) Flush frequently, and so have the checkpoints be of short duration, or b) Flush infrequently, so that the checkpoint flushes would have a long duration. Now, if you have reasonable settings (I'm not sure how well its tuning is documented :-(), checkpoint "flushes" should be able to be short, however infrequent they may be. In effect, the "oops, the database got blocked by checkpoint flushing" issue should now be gone... The issue that then remains is whether to checkpoint often, in which case crash recovery will tend to be be quicker, or whether to checkpoint seldom, in which case crash recovery will have fewer checkpoints to choose from, and hence will run somewhat longer. If your systems don't crash much, and recovery time isn't a big deal, then this probably doesn't much matter... -- (reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc")) http://linuxfinances.info/info/sap.html "I don't plan to maintain it, just to install it." -- Richard M. Stallman
On Mon, 3 Mar 2008, Douglas J Hunley wrote: > In other words, is it ok to checkpoint say, every 5 minutes, if it only > last a second or three or better to have checkpoints every 10 minutes > that last half a minute? When checkpoints do too much work at once they will block clients for a significant period of time near the end--anywhere from 2 to 8 seconds isn't unusual. Every client on the system will just hang, then they all start responding again in a batch when the checkpoint is finished. With that as the problematic case, if you can keep the duration of the checkpoint processing minimal by having them happen more frequently, then that's the better approach. You can't push that interval too small though or your system will be continuously checkpointing. In cases where checkpoints hurt no matter how often you do them, there it makes sense to have them as infrequently as possible so at least you minimize the number of times that the disruption happens. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Mon, 3 Mar 2008, Chris Browne wrote: > Now, if you have reasonable settings (I'm not sure how well its tuning > is documented :-(), checkpoint "flushes" should be able to be short, > however infrequent they may be. In effect, the "oops, the database got > blocked by checkpoint flushing" issue should now be gone... Ah, if only it were true. The background writer can be made to work fairly well in circa early 8.1 setups where the shared_buffers cache is small. But on more current systems where there's a lot of memory involved, you can't get a tuning aggressive enough to make checkpoint spikes small without wasting a bunch of I/O writing buffers that will just get dirty again before the checkpoint. Since the kinds of systems that have nasty checkpoint spikes are also I/O bound in general, there is no good way to resolve that struggle using the code in 8.1 and 8.2. The checkpoint_completion_target tunable and related code in 8.3 is the first approach to this issue that has a good foundation even with larger buffer caches. You can band-aid some cases well enough to improve things with the background writer in earlier versions, but it's certainly not guaranteed that it's possible even if you spend lots of time fiddling with the settings. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD