On 31-Aug-06, at 2:15 PM, Vivek Khera wrote:
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.
Yes, I have to admit, the setting I proposed works well for linux,
but may not for bsd.
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...
As long as you are aware of the ramifications....
