Re: Opteron/FreeBSD/PostgreSQL performance poor

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