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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
