Tuning Postgresql on FreeBSD 5.1

[Sean Chittenden]

[ Please post follow ups oon this topic to just -databases, this is
  off topic for performance@ ]

> 5 large processes were kicked off.  The processes started off ok but
> then just stalled.  They continued to run until completion but VERY
> slowly.  Many times I saw the processes in semwait states.  However,
> the CPU load never went over 0.80.  Everything just kind of stalled
> and waited.  They consumed huge amounts of memory but never swapped.

I don't think semaphores are swap backed, but I could be wrong.  That
said, if I'm right (I think I am), your backends are contending for a
finite amount of shared memory/semaphores.  Did you turn down values
to the levels I suggested?  It looks like you've over stated the
amount of ram available in your system in your various configs (over
use of md, overly large values in postgresql.conf, and an under sized
effective_cache_size, etc).  Is your effective_cache_size set
correctly, btw?  Run the following in /bin/sh and you'll get the
correct value.

echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"

> I disabled HTT in the BIOS, but the OS still saw 4 cpus...  I'm
> going to again try to disable HTT and see what occurs in the kernel
> boot.  For right now, I've enable and modified all the parameters
> according to what Sean suggested.  I'm hoping they've turned up
> something here.  In the event that somehow I can't disable, I'll use
> the cpu sysctl parameter that was suggested.

The kernel manually walks the table.  To really disable HTT, you have
to recompile your kernel.

-sc

-- 
Sean Chittenden