Re: Upgrade to dual processor machine?

On Tue, 12 Nov 2002, Josh Berkus wrote:

> Heinrik,
>
> "So, where do i find and change shmmax shmall settings ??
> What should I put there?
>
> What is a recommended value for shared buffers in postgresql.conf ?"
>
> There is no "recommended value."   You have to calculate this relatively:
>
> 1) Figure out how much RAM your server has available for PostgreSQL.  For
> example, I have one server on which I allocate 256 mb for Apache, 128 mb for
> linux, and thus have 512mb available for Postgres.
>
> 2) Calculate out the memory settings to use 70% of that amount of Ram in
> regular usage.   Please beware that sort_mem is *not* shared, meaning that it
> will be multiplied by the number of concurrent requests requiring sorting.
> Thus, your calculation (in K) should be:
>
> 250K  +
> 8.2K * shared_buffers +
> 14.2K * max_connections +
> sort_mem * average number of requests per minute
> =====================================
> memory available to postgresql in K * 0.7
>
> You will also have to set SHMMAX and SHMMALL to accept this memory allocation.
> Since shmmax is set in bytes, then I generally feel safe making it:
> 1024 * 0.5 * memory available to postgresql in K
>
> Setting them is done simply:
> $ echo 134217728 >/proc/sys/kernel/shmall
> $ echo 134217728 >/proc/sys/kernel/shmmax
>
> This is all taken from the postgresql documentation, with some experience:
> http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/runtime.html

Note that on RedHat boxes, you can also use the /etc/sysctl.conf file to
do this.  It is considered the preferred method, and a little less obtuse
for beginners.

As root, run 'sysctl -a' to get a list of all possible system kernel
settings.  'sysctl -a | grep shm' will show you all the shared memory
settings as they are now.  Edit the /etc/sysctl.conf file with the new
settings and use 'sysctl -p' to process the new settings.  This way you
don't have to edit the /etc/rc.d/rc.local file to get the settings you
want.

On the subject of sort_mem, I've found that if your result sets are all
large (say 100+megs each) that as long as your sort mem isn't big enough
to hold the whole result set, the performance difference is negligable.
I.e. going from 4 meg to 16 meg of sort_mem for a 100 Meg result set
doesn't seem to help much at all.  In fact, in some circumstances, it
seems that the smaller number is faster, especially under heavy parallel
load, since larger settings may result in undesired swapping out of other
processes to allocate memory for sorts.

In other words, it's faster to sort 20 results in 4 megs each if you
aren't causing swapping out, than it is to sort 20 results in 32 megs
each if that does cause things to swap out.