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.