Re: Opteron/FreeBSD/PostgreSQL performance poor

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?

> stats_command_string = on

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

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

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

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

merlin