Re: tuning bgwriter in 8.4.2

Ben Chobot wrote:
>
> Hm, my shared_buffers is already 10GB, but I'm using about 80GB for filesystem cache. Would a larger shared_buffers make sense? I thought I read somewhere that 10GB is on the high end of the useful size for shared_buffers.

Yeah, I figured that out when I was analyzing your figures and thought
I'd missed a decimal place when I first saw it. The problem with huge
increases in shared_buffer is that they can increase the amount of time
it takes to allocate a new buffer. If all you've got in there are lots
of records with high usage counts, it can end up taking multiple "sweeps
of the clock hand" over things to drop usage counts. With >75% of your
buffers already having a usage count of 4 or 5, you've already gone
pretty far in the direction where that could happen. With still around
13% only have a 0 or 1 usage count I don't think it's too bad yet.

You're certainly not in well explored territory though. If you were
seeing large amounts of backend writes or buffers being allocated, maybe
a larger shared_buffers would make sense. From the snapshots of data
you've provided, that doesn't seem to be the case though, so I wouldn't
be too worried about it. The only thing you could really do here is
increase checkpoint_timeout - with this much data, having a checkpoint
every 5 minutes is on the fast side, and I'd bet you could tolerate the
disk space and additional crash recovery time in return for better
average performance the rest of the time.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us