Re: Sharedbuffers and WorkMem

On Tue, 20 Oct 2009, Waldomiro wrote:

> So, I put in sharedbuffers the same size my two biggest tables, which are the
> most read too. 11 GB + 4 GB. total shared buffers = 15 GB

That seems quite reasonable. The rest of the unused RAM in the server is
going to be used by the operating system cache, which works as a layer on
top of shared_buffers. There are a couple of problems with giving most of
your RAM to the database directly. The three most obvious ones are that
it doesn't leave anything for other applications, the PostgreSQL
shared_buffers design isn't optimized for really large amounts of RAM, and
memory given to shared_buffers has to be involved in the database
checkpoint computations--whereas OS cached buffers are not.

> After that I put 100 MB to each expected user, 300 * 100 MB = 30 GB
> So, I used only 30 GB + 15GB = 45 GB total RAM

Leaving 19GB for the operating system to cache things with. It's not
going to be unused. That's quite reasonable, and more flexible as far as
what the server can accomplish than had you given more memory to
shared_buffers. It might even perform better--there's been some evidence
that shared_buffers starts to degrade going much beyond the 10GB range
anyway.

Your starting configuration seems fine to me, I would suggest getting your
application running and measure actual memory use before tweaking anything
further.

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