Re: Upgrade to dual processor machine? - Mailing list pgsql-general
From | scott.marlowe |
---|---|
Subject | Re: Upgrade to dual processor machine? |
Date | |
Msg-id | Pine.LNX.4.33.0211121320110.24879-100000@css120.ihs.com Whole thread Raw |
In response to | Re: Upgrade to dual processor machine? (Josh Berkus <josh@agliodbs.com>) |
List | pgsql-general |
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.
pgsql-general by date: