Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 -
From: "Jeffrey W. Baker" <jwbaker@acm.org>
To: Robert Creager <Robert_Creager@logicalchaos.org>
Cc: PGPerformance <pgsql-performance@postgresql.org>,
	Andy Hewitt <Andy_Hewitt@StorageTek.com>
In-Reply-To: <20050717213416.59dcb52e@thunder.logicalchaos.org>
References: <20050717213416.59dcb52e@thunder.logicalchaos.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Sun, 17 Jul 2005 22:09:11 -0700
Message-Id: <1121663351.10108.1.camel@noodles>
Mime-Version: 1.0

On Sun, 2005-07-17 at 21:34 -0600, Robert Creager wrote:
> Sigh...
> 
> I recently upgraded from 7.4.1 to 8.0.3.  The application did not change.  I'm
> now running both database concurrently (on different ports, same machine) just
> so I could verify the problem really exists.
> 
> The application is a custom test application for testing mechanical systems. 
> The runs in question (4 at a time) each generate 16 queries at a time of which
> the results are sent to the mechanical system which processes the request, which
> processes them anywhere from 10 to 120 seconds.  The system is capable of
> completing between 4 and 8 jobs at once.  So, once the system is running, at
> most there will be 8 queries per run simultaneously.
> 
> The entire database fits into RAM (2Gb), as evidenced by no disk activity and
> relatively small database size.  pg_xlog is on different disks from the db.
> 
> The problem is that on version 8.0.3, once I get 3 or more concurrent runs
> going, the query times start tanking (>20 seconds).  On 7.4.1, the applications
> hum along with queries typically below .2 seconds on over 5 concurrent runs. 
> Needless to say, 7.4.1 behaves as expected...  The only change between runs is
> the port connecting to.  Bot DB's are up at the same time.
> 
> For 8.03, pg_autovacuum is running.  On 7.4.1, I set up a cron job to vacuum
> analyze every 5 minutes.
> 
> The system is Mandrake Linux running 2.4.22 kernel with dual Intel Xenon CPU
> with HT enabled.  On an 803 run, the context switching is up around 60k.  On
> 7.4.1, it maxes around 23k and averages < 1k.

Did you build 8.0.3 yourself, or install it from packages?  I've seen in
the past where pg would build with the wrong kind of mutexes on some
machines, and that would send the CS through the roof.  If you did build
it yourself, check your ./configure logs.  If not, try strace.

-jwb