Jeremy,
I have Postgres 6.4.2 running on RedHat 5.2 with the new 2.2.1 Linux
kernel on a 400 MHz PII with 512 Meg RAM and an 18 Gig SCSI drive. I
have had problems with Postgres being pretty slow because my database is
fairly large (almost 1 Gig). I seemed to have finally solved my speed
problems recently. Here are some of my suggestions after several months
of trial and error:
1. Get Postgres 6.4.2 and re-install it. I had problems running the old
postgres that was included on the RH disk. Plus, I believe that there
were several important bugs that were fixed in the 6.4.1-->6.4.2
transition.
2. Make sure that your Linux recognizes all 256 Meg of RAM. In the
/etc/lilo.conf file, there should be a line called "Mem = XXXMeg" where
XXX is the amount of RAM in your system. The Linux default is 64 Meg of
RAM so it is possible that Linux isn't recognizing your extra RAM (above
64 Meg). Change the line of 256Meg, save the file, exit, and type
lilo.conf as root to update Linux. Also, since RAM is cheap, I would
recommend maxing out your system RAM (I have 512 Meg with 1 Gig of swap
space).
3. Use the option -B to increase the shared memory buffers. In Linux,
the default is about 28 Meg of shared memory. So the postmaster will
crap out if you try to start it up with more than that amount of shared
memory (-B 3500). However, you can change the max shared memory by
altering the file called /usr/src/linux/include/asm-i386/shmparam.h and
change the constant SHMMAX to whatever you want. You will need to then
re-build your kernel; so I would suggest getting the new Linux kernel
(2.2.1) that was just released.
4. Use the -o -F option. This will prevent Postgres from flushing to
disk after every transaction. So your copy commands should go much
faster. (However, if the system crashes, you may lose whatever is within
the buffer that hasn't been written yet to disk). This helped me to
access other programs on my Linux box (e.g. mail, text editor) while a
large copy or insert or vacuum was going on. It also sped up my large
inserts from 2 days to 2 hours. My vaccums have gone from 1 day to 2
minutes. I think that the Intel processor just doesn't handle
multi-tasking well, so you really need to -o -F so that big jobs don't
cripple your system from other jobs.
Well, that's my 2 cents worth.
Hope that helps.
-Tony
