Re: PostgreSQL and memory usage

Hi Dann, I took hackers out of the list as this isn't really a hacking 
issue, but I added in performance as this definitely applies there.

There are generally two areas of a database server you have to reconfigure 
to use that extra memory.  The first is the kernel's shared memory 
settings.

On a linux box that has sysconf installed this is quite easy.  If it isn't 
installed, install it, as it's much easier to manipulate your kernel's 
settings using sysctl than it is with editing rc.local.

First, get root.  Then, use 'sysctl -a|grep shm' to get a list of all the 
shared memory settings handled by sysctl.  

On a default redhat install, we'll get something like this:

kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 33554432

On my bigger box, it's been setup to have this:

kernel.shmmni = 4096
kernel.shmall = 32000000
kernel.shmmax = 256000000

To make changes that stick around, edit the /etc/sysctl.conf file to have 
lines that look kinda like those above.  To make the changes to the 
/etc/sysctl.conf file take effect, use 'sysctl -p'.

Next, as the postgres user, edit $PGDATA/postgresql.conf and increase the 
number of shared buffers.  On most postgresql installations this number is 
multiplied by 8k to get the amount of ram being allocated, since 
postgresql allocates share buffers in blocks the same size as what it uses 
on the dataset.  To allocate 256 Megs of buffers (that's what I use, seems 
like a nice large chunk, but doesn't starve my other processes or system 
file cache) set it to 32768.

Be careful how big you make your sort size.  I haven't seen a great 
increase in speed on anything over 8 or 16 megs, while memory usage can 
skyrocket under heavy parallel load with lots of sorts, since sort memory 
is PER SORT maximum.

Then do the old pg_ctl reload and you should be cooking with gas.