Date: Thu, 06 Jul 2006 11:49:40 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Opteron/FreeBSD/PostgreSQL performance poor
In-reply-to: <44AC00B5.6030207@noaa.gov>
To: andy rost <andy.rost@noaa.gov>
Cc: Stephen Frost <sfrost@snowman.net>, pgsql-performance@postgresql.org,
	Bill.Sites@noaa.gov
Message-id: <44AC5014.1070605@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=us-ascii
Content-transfer-encoding: 7bit
User-Agent: Thunderbird 1.5.0.4 (X11/20060704)
References: <44ABCFF4.3010007@noaa.gov>
	<20060705154656.GS22057@kenobi.snowman.net> <44AC00B5.6030207@noaa.gov>

andy rost wrote:
> 

>>> effective_cache_size = 27462            # typically 8KB each
>>
>>
>> This seems like it might be a little low...  How much memory do you have
>> in the system?  Then again, with your shared_mem set so high, perhaps
>> it's not that bad, but it might make sense to swap those two settings,
>> or at least that'd be a more common PG setup.
> 
> Oops, forgot to mention that we have 6 Gigs of memory. This value was 
> set based on sysctl -n vfs.hibufspace / 8192
> 

That vfs.hibufspace sysctl is a little deceptive IMHO - e.g on my 
FreeBSD 6.1 system with 2G of ram it says 117276672 (i.e. about 112M), 
but I have a 1G file cached entirely in ram at the moment... In FreeBSD 
file pages are actually kept in the 'Inactive' section of memory, the 
'Buffer' section is used as a 'window' to read 'em. For instance on my 
system I see:

Mem: 4192K Active, 1303M Inact, 205M Wired, 12K Cache, 112M Buf, 491M Free

So my 1G file is cached in the 1303M of 'Inactive', but I have 112M of 
buffer window for accessing this (and other) cached files. Now, I may 
not have explained this that well, and it is quite confusing... but 
hopefully you get the idea!

Now on the basis of the figures provided:
- max_connections=102 , each with work_mem=10000 (approx 1G in total)
- shared buffers=125000 (1G total)

it looks like you are only using about 2G of  your 6G, so there is a lot 
left for caching file pages (lets say 3-4G or so).

I would think you can happily set effective_cache_size=393216 (i.e. 
3G/8192). This will have the side effect of encouraging more index scans 
(probably what you want I think).

Best wishes

Mark