Skip site navigation (1) Skip section navigation (2)

Re: Opteron/FreeBSD/PostgreSQL performance poor

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "andy rost" <andy(dot)rost(at)noaa(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org, bill(dot)sites(at)noaa(dot)gov
Subject: Re: Opteron/FreeBSD/PostgreSQL performance poor
Date: 2006-07-06 14:44:26
Message-ID: b42b73150607060744u54a6ea93la0e56da3828e0a13@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Eugeny N DzhurinskyDate: 2006-07-06 15:11:26
Subject: Re: getting better performance
Previous:From: Scott MarloweDate: 2006-07-06 14:28:39
Subject: Re: getting better performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group