Re: 8.3 beta testing suggestions welcome - Mailing list pgsql-hackers

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
http://developer.postgresql.org/pgdocs/postgres/wal-configuration.html

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

shared_buffers=1GB
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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD