Re: PG 8.3 and large shared buffer settings

On Thu, 24 Sep 2009, Dan Sugalski wrote:

> Is there any practical limit to the number of shared buffers PG 8.3.7 can
> handle before more becomes counter-productive?

There are actually two distinct questions here you should consider,
because the popular wisdom here and what makes sense for your case might
be different.

The biggest shared_buffers tests I've seen come from Sun, where Jignesh
there saw around 10GB was the largest amount of RAM you could give to the
database before it stopped improving performance. As you guessed, there
is a certain amount of overhead to managing the buffers involved, and as
the size grows the chance you'll run into locking issues or similar
resource contention grows too.

Another problem spot are checkpoints. If you dirty a very large buffer
cache, that whole thing will have to get dumped to disk eventually, and on
some workloads people have found they have to reduce shared_buffers
specifically to keep this from being too painful.

That's not answering your question though; what it answers is "how large
can shared_buffers get before it's counterproductive compared with giving
the memory to OS to manage?"

The basic design of PostgreSQL presumes that the OS buffer cache exists as
a second-chance source for cached buffers. The OS cache tends to be
optimized to handle large numbers of buffers well, but without very much
memory about what's been used recently to optimize allocations and
evictions. The symmetry there is one reason behind why shared_buffers
shouldn't be most of the RAM on your system; splitting things up so that
PG has a cut and the OS has at least as large of its own space lets the
two cache management schemes complement each other.

> The box runs other things as well as the database, so the OS buffer cache
> tends to get effectively flushed -- permanently pinning more of the database
> in memory would be an overall win for DB performance, assuming bad things
> don't happen because of buffer management.

This means that the question you want an answer to is "if the OS cache
isn't really available, where does giving memory to shared_buffers becomes
less efficient than not caching things at all?" My guess is that this
number is much larger than 10GB, but I don't think anyone has done any
tests to try to quantify exactly where it is. Typically when people are
talking about systems as large as yours, they're dedicated database
servers at that point, so the OS cache gets considered at the same time.
If it's effectively out of the picture, the spot where caching still helps
even when it's somewhat inefficient due to buffer contention isn't well
explored.

It would depend on the app too. If you're heavily balanced toward reads
that don't need locks, you can certainly support a larger shared_buffers
than someone who is writing a lot (just the checkpoint impact alone makes
this true, and there's other sources for locking contention).

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD