Sharedbuffers and WorkMem

On Tue, Oct 20, 2009 at 4:22 PM, Waldomiro <waldomiro@shx.com.br> wrote:

Hi everybody,

I have some doubts about sharedmemory and work_mem.

I have a server:

64 GB RAM
2 processors 4 cores each one intel xeon 3 Ghz
expecting 300 users in the same time

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

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

My doubt is, can I use more RAM for shared buffers? about 30 GB (50% of
total RAM)?

What is the max shared buffers I can use? How can I calculate that?

what's the db version? what OS ?

have you tried pgtune ? (if db 8.3 or 8.4)

--
GJ

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@gregsmith.com http://www.gregsmith.com Baltimore, MD