Re: Opteron/FreeBSD/PostgreSQL performance poor

Hi Merlin,

Thanks for the input. Please see below ...

Merlin Moncure wrote:
> On 7/5/06, andy rost <andy(dot)rost(at)noaa(dot)gov> wrote:
>
>> fsync = on # turns forced synchronization
>
>
> have you tried turning this off and measuring performance?

No, not yet. We're trying a couple of outher avenues before manipulating
this parameter.

>
>> stats_command_string = on
>
>
> I would turn this off unless you absoltely require it. It is
> expensive for what it does.

We've turned this off

>
>> a) All 4 CPUs are nearly always 0% idle;
>> b) The system load level is nearly always in excess of 20;
>
>
> I am guessing your system is spending all it's time syncing. If so,
> it's solvable (again, just run fsync=off for a bit and compare).
>

We've reduced the load significantly primarily by moving pg_xlog to its
own drive and by increasing the effective cache size. While we still see
high load levels, they don't last very long. We're trying improve
performance from several angles but are taking it one step at a time.
Eventually we'll experiment with fsynch

>> c) the output from vmstat -w 10 looks like:
>> procs memory page disks faults
>> cpu
>> r b w avm fre flt re pi po fr sr aa0 aa1 in sy cs us
>> sy id
>> 21 0 3 1242976 327936 2766 0 0 0 2264 0 2 2 17397 140332
>> 104846 18 82 1
>
>
> is that 100k context switches over 10 seconds or one second? that
> might be something to check out. pg 8.1 is regarded as the solution
> to any cs problem, though.

According to man top, that's 100K per second. I'm interested in your
recommendation but am not sure what "pg 8.1" references

>
>> NOTE - small user demands and high system demands
>> d) Running top indicates a significant number or sblock states and
>> occasional smwai states;
>> e) ps auxww | grep postgres doesn't show anything abnormal;
>> f) ESQL applications are very slow.
>>
>> We VACUUM ANALYZE user databases every four hours. We VACUUM template1
>> every 4 hours. We make a copy of the current WAL every minute. We create
>> a PIT recovery archive daily daily. None of these, individually seem to
>> place much strain on the server.
>
>
> your server should be able to handle this easily.
>
>> Hopefully I've supplied enough information to start diagnosing the
>> problem. Any ideas, thoughts, suggestions are greatly appreciated ...
>>
>
> can you please approximate roughly how many transactions per second
> your server is handling while you are getting the 20 load condition
> (and, if possible, broken down into read and write transactions)?

Do you have any suggestions on how I might obtain these metrics?

>
> merlin

Thanks again Merlin ...

Andy

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