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

Huge performance problem between 7.4.1 and 8.0.3 - CS issue?

From: Robert Creager <Robert_Creager(at)LogicalChaos(dot)org>
To: PGPerformance <pgsql-performance(at)postgresql(dot)org>,Andy Hewitt <Andy_Hewitt(at)StorageTek(dot)com>
Subject: Huge performance problem between 7.4.1 and 8.0.3 - CS issue?
Date: 2005-07-18 03:34:16
Message-ID: 20050717213416.59dcb52e@thunder.logicalchaos.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

I've attached four files.  741 has the query and explain analyze.  803 has the
query and explain analyze during loaded and unloaded times.  I've also attached
the conf files for the two versions running.  I've gone through them and don't
see any explanation for the problem I'm having.

I'm guessing this is the CS problem that reared it's head last year?  I had an
e-mail exchange in April of last year about this.  Any reason this would be
worse with 8.0.3?

Thanks,
Rob

-- 
 13:33:43 up 3 days, 17:08,  9 users,  load average: 0.16, 0.59, 0.40
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004

Responses

pgsql-performance by date

Next:From: Matthew T. O'ConnorDate: 2005-07-18 03:48:20
Subject: Re: Huge performance problem between 7.4.1 and 8.0.3 -
Previous:From: Tom LaneDate: 2005-07-17 18:15:54
Subject: Re: Impact of checkpoint_segments under continual load conditions

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