Re: Database size Vs performance degradation - Mailing list pgsql-performance

On Wed, 30 Jul 2008, Dave North wrote:

> One observation I've made on the DB system is the disk I/O seems
> dreadfully slow...we're at around 75% I/O wait sometimes and the read
> rates seem quite slow (hdparm says around 2.2MB/sec - 20MB/sec for
> un-cached reads).

This is typically what happens when you are not buffering enough of the
right information in RAM, such that there are lots of small reads and
writes to the disk involve lots of seeking.  You'll only get a couple of
MB/s out of a disk if it has to move all over the place to retreive the
blocks you asked for.

Setting shared_buffers too low makes this more likely to happen, because
PostgreSQL has to constantly read and write out random blocks to make
space to read new ones in its limited work area.  The OS buffers some of
that, but not as well as if the database server has a bit more RAM for
itself because then the blocks it most uses won't leave that area.

> And if so, would I be better having a higher shared_buffers rather than
> relying so much on OS cache?

The main situation where making shared_buffers too high is a problem on
8.1 involves checkpoints writing out too much information at once.  You
didn't mention changing checkpoint_segments on your system; if it's at its
default of 3, your system is likely continuously doing tiny checkpoints,
which might be another reason why you're seeing so much scattered seek
behavior above.  Something >30 would be more appropriate for
checkpoint_segments on your server.

I'd suggest re-tuning as follows:

1) Increase shared_buffers to 10,000, test.  Things should be a bit
faster.

2) Increase checkpoint_segments to 30, test.  What you want to watch for
here whether there are periods where the server seems to freeze for a
couple of seconds.  That's a "checkpoint spike".  If this happens, reduce
checkpoint_segments to some sort of middle ground; some people never get
above 10 before it's a problem.

3) Increase shared_buffers in larger chunks, as long as you don't see any
problematic spikes you might usefully keep going until it's set to at
least 100,000 before improvements level off.

> I spent several hours reading info on this list and other places and
> it's highly inconclusive about having high or low shared buffs Vs
> letting the OS disk cache handle it.

A lot of the material floating around the 'net was written circa
PostgreSQL 8.0 or earlier, and you need to ignore any advice in this area
from those articles.  I think if you rescan everything with that filter in
place you'll find its not so inconclusive that increasing shared_buffers
is a win, so long as it doesn't trigger checkpoint spikes (on your
platform at least, there are still Windows issues).  Check out my "Inside
the PostgreSQL Buffer Cache" presentation at
http://www.westnet.com/~gsmith/content/postgresql for an excess of detail
on this topic.  Unfortunately the usage_count recommendations given there
are impractical for use on 8.1 because pg_buffercache doesn't include that
info, but the general "shared_buffers vs. OS cache" theory and suggestions
apply.

The other parameter I hope you're setting correctly for your system is
effective_cache_size, which should be at least 2GB for your server (exact
sizing depends on how much RAM is leftover after the Tomcat app is
running).

All this is something to consider in parallel with the vacuum
investigation you're doing.  It looks like your autovacuum isn't anywhere
close to aggressive enough for your workload, which is not unusual at all
for 8.1, and that may actually be the majority if your problem.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD