On Tue, 21 Aug 2007, Kevin Grittner wrote:

shared_buffers = 160MB
effective_cache_size = 5GB
bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10.0
bgwriter_all_maxpages = 600
checkpoint_segments = 10

The other thing I realized you haven't mentioned yet is what operating system you're using.

What you've done here is make your shared_buffers smaller than even the write cache on your disk controller (256MB), so that anytime a checkpoint hits it should be able to cache writing even the entire buffer cache out if necessary. You're letting the OS handle caching everything else, which is memory not involved in the checkpoint process and therefore doesn't impact your problem situation.

With checkpoint_segments at 10, you can't do much activity (relative to how fast this powerful of a server can dirty pages) before a checkpoint happens, which also limits how much any one checkpoint has to clean up. And your background writer settings are aggressive enough that the entire pool is getting scanned constantly, which is wasting lots of CPU resources but you have them to spare.

This is reasonable solution for working around checkpoint issues in 8.2.4, but the fact that you're still having checkpoints spaced 30 seconds apart sometimes is one weak spot you already have seen. PostgreSQL operating with a tiny shared_buffers cache is another, and you could expect overall performance to improve if it were possible to increase that without getting killed by checkpoints.

In 8.3, it should be possible for you to increase both shared_buffers and checkpoint_segments significantly and still have checkpoints happen in an orderly fashion. There is no all-scan anymore, instead there's a parameter called checkpoint_completion_target; see

My suggestion for a starting 8.3 configuration for you would be adjusting these settings as follows:

checkpoint_segments = 50

And then try replaying your data with checkpoint_completion_target at 0.5 (default), 0.7, and 0.9 and see how each of those works out for you (0.7 is the least useful of those if you only did two). Hopefully the data replay you have can be setup to invoke the same tightly spaced checkpoint behavior you commented about. Based on the results of those tests, it may be possible to further increase shared_buffers, and checkpoint_segments/checkpoint_timeout may need some adjustment one way or another.

Note that some/all of the bgwriter_lru parameters may be going away before 8.3 is done as well, that's a loose end I'm working on right now.

If your project gets approved, that's what I think would be a useful test to run. That should get some good results for the community as large as well as research how upgrading to the new version might positively impact your application. You're actually in one of the situations I'm a little concerned about. All the tests that have been done by people here have suggested using checkpoint_completion_target and removing the all scan are always net positive compared to the 8.2.4 behavior, but your situation (where you're heavily using bgwriter_all_percent = 10.0 and bgwriter_all_maxpages = 600) is one where it's possible 8.3 may be a step backwards. Not likely, just possible, and it would be great to get another data point on this during the beta.

* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Reply via email to