Re: performance problems.

On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote:

> Actually unless you have a ram disk you should probably leave
> random_page_cost at 4, shared buffers should be 2x what you have
> here, maintenance work mem is pretty high
> effective cache should be much larger 3/4 of 4G or about 360000
>

I've been pondering bumping up SHM settings more, but it is a very
big imposition to have to restart the production server to do so.
This weekend being a long weekend might be a good opportunity to try
it, though...

As for maintenence mem, when you have HUGE tables, you want to give a
lot of memory to vacuum. With 4GB of RAM giving it 512MB is not an
issue.

The effective cache size is the big issue with FreeBSD. There are
opposing claims of how much memory it will use for cache, and throw
in the kern.ipc.shm_use_phys sysctl which causes SHM to bypass the VM
system entirely, and who knows what's going on.

> Setting work _mem this high should be done with caution. From the
> manual "Note that for a complex query, several sort or hash
> operations might be running in parallel; each one will be allowed
> to use as much memory as this value specifies before it starts to
> put data into temporary files. Also, several running sessions could
> be doing such operations concurrently. So the total memory used
> could be many times the value of work_mem"

Again, with boat-loads of RAM why not let the queries use it? We
only have a handful of connections at a time so that's not eating up
much memory...