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....
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2006-08-31 19:08:03|
|Subject: Re: performance problems. |
|Previous:||From: Vivek Khera||Date: 2006-08-31 18:15:14|
|Subject: Re: performance problems.|