Re: performance problems.

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....