Re: 8.3 beta testing suggestions welcome

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD