In-Reply-To: <A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
	<BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.com>
	<A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <43D1B021-7BDB-4CC4-BB7A-A6DFDAEF1537@fastcrypt.com>
Cc: "Pgsql-Performance ((((E-mail))))" <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Dave Cramer <pg@fastcrypt.com>
Subject: Re: performance problems.
Date: Thu, 31 Aug 2006 14:28:31 -0400
To: Vivek Khera <vivek@khera.org>


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