Re: Opteron/FreeBSD/PostgreSQL performance poor

Mark,

Thanks for the insight. I increased the value of effective_cache_size to
3 Gigs and will monitor the performance over the weekend. Prior to this
change we discovered that we are filling up WALs to the tune of 2400 per
day. Moving the pg_xlog subdirectory to its own drive seemed to boost
the performance significantly. We're taking this one step at a time. On
Monday we plan to drop the number of shared memory buffers down to 50000
from its current value of 125000 (per the large number of
recommendations that this value should be held fairly low and
suggestions that vales in excess of 50000 may hamper performance).

Thanks again ...

Andy

Mark Kirkwood wrote:
> 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

--
--------------------------------------------------------------------------------
Andrew Rost
National Operational Hydrologic Remote Sensing Center (NOHRSC)
National Weather Service, NOAA
1735 Lake Dr. West, Chanhassen, MN 55317-8582
Voice: (952)361-6610 x 234
Fax: (952)361-6634
andy(dot)rost(at)noaa(dot)gov
http://www.nohrsc.noaa.gov
--------------------------------------------------------------------------------