Re: Setting shared buffers

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.

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?)

To double check if I understood correctly:

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:

shared_buffers = 32000

I don't touch anything else (max_connections keeps its default
value, but as I understand, that has nothing to do anyway...
right?)

So, what should I do?

Apologies if this is an FAQ -- I tried searching the archives,
but I get a 404 - Not Found error when following the link to
the archives for this list :-(

Thanks in advance for any comments / advice !

Carlos
--

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

Thanks John! This is very helpful...

Just one detail I'd like to double check:

>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 [...]
>

According to that document, I should put the same value for the
SHMMAX and SHMMALL -- however, when I do:

cat /proc/sys/kernel/shmmax
cat /proc/sys/kernel/shmmall

on my Linux system (RedHat 7.3, soon to upgrade to 8.0), I
get different values, shmmall being shmmax divided by 16

Is that normal? What should I do? Should I follow the exact
same instructions from that document and set both to the
exact same value?

Are the default values set that way (i.e., different values)
for some strange reason, or is it that on the 2.4 kernel
the shmmall is indicated in blocks of 16 bytes or something
like that?

Thanks!

Carlos
--