Re: Setting shared buffers

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: Carlos Moreno <moreno(at)mochima(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Setting shared buffers
Date: 2002-10-27 00:14:35
Message-ID: web-1800543@davinci.ethosmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Carlos,

> After checking some docs on performance tuning, I'm trying to
> follow Bruce Momjian (sp??) advice to set the shared_buffers
> at 25% of the amount of physical memory (1GB in our server)
> and 4% for the sort_mem.

I tend to set my shared_buffers somewhat higher, but that's a good
place to start. Be cautious about sort_mem on a server with a lot of
users; sort_mem is not shared, so make sure that you have enough that
your server could handle 1-2 sorts per concurrent user without running
out of RAM.

> When I try that, I get an error message when starting postgres,
> complaining that the amount of shared memory requested exceeds
> the maximum allowed by the kernel (they talk about increasing
> the kernel parameter SHMMAX -- does this mean that I have to
> recompile the kernel? Or is it just a "runtime" configuration
> parameter that I set and on the next reboot will be taken?)

It's easy, on Linux don't even have to reboot. Other OS's are harder.
See this very helpful page:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/kernel-resources.html#SYSVIPC

In fact, I tend to up my SHMMAX and SHMMALL and shared_buffers at night
on some databases, when they are doing automatic updates, and adjust
them back down during the day, when I want to prevent heavy user loads
from using up all system RAM.

> I have 1GB, so I want 256MB as shared buffers memory; each
> shared buffer is 8kbytes, so I take 256M / 8k, which is 32k --
> so, I uncomment the line shared_buffers in the configuration
> file, and put:

See the calculations on the page link above. They are more specific
than that, and I have found the numbers there to be good estimates,
maybe only 10-20% high.

-Josh Berkus

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Lars Maschke 2002-10-28 11:00:52 [pgsql-performance] Performance Problems
Previous Message Carlos Moreno 2002-10-26 22:36:05 Setting shared buffers