From: Alex Deucher <alexdeucher@gmail.com>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: strange performance regression between 7.4 and 8.1
Date: Thu, 1 Mar 2007 18:11:21 -0500
Message-ID: <a728f9f90703011511m4a0a64fawd65d443ab92b65a4@mail.gmail.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0703011253040.3892@discord.home.frostconsultingllc.com>
References: <a728f9f90703011244r577d56d7y422ab4bd71bb760e@mail.gmail.com>
<45E73C90.2080206@commandprompt.com>
<Pine.LNX.4.64.0703011253040.3892@discord.home.frostconsultingllc.com>
On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Joshua D. Drake wrote:
>
> > Alex Deucher wrote:
> >> Hello,
> >>
> >> I have noticed a strange performance regression and I'm at a loss as
> >> to what's happening. We have a fairly large database (~16 GB). The
> >> original postgres 7.4 was running on a sun v880 with 4 CPUs and 8 GB
> >> of ram running Solaris on local scsi discs. The new server is a sun
> >> Opteron box with 4 cores, 8 GB of ram running postgres 8.1.4 on Linux
> >> (AMD64) on a 4 Gbps FC SAN volume. When we created the new database
> >> it was created from scratch rather than copying over the old one,
> >> however the table structure is almost identical (UTF8 on the new one
> >> vs. C on the old). The problem is queries are ~10x slower on the new
> >> hardware. I read several places that the SAN might be to blame, but
> >> testing with bonnie and dd indicates that the SAN is actually almost
> >> twice as fast as the scsi discs in the old sun server. I've tried
> >> adjusting just about every option in the postgres config file, but
> >> performance remains the same. Any ideas?
> >
> > Vacuum? Analayze? default_statistics_target? How many shared_buffers?
> > effective_cache_size? work_mem?
>
> Also, an explain analyze from both the 7.4 and 8.1 systems with one of the
> 10x slower queries would probably be handy.
here are some examples. Analyze is still running on the new db, I'll
post results when that is done. Mostly what our apps do is prepared
row selects from different tables:
select c1,c2,c3,c4,c5 from t1 where c1='XXX';
old server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
width=26) (actual time=5.722..5.809 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 5.912 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
width=26) (actual time=12.423..12.475 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 12.538 ms
(3 rows)
new server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
width=26) (actual time=33.461..51.377 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 51.419 ms
(3 rows)
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
width=26) (actual time=45.733..46.271 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 46.325 ms
(3 rows)
Alex
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: alexdeucher@gmail.com, jeff@frostconsultingllc.com, jd@commandprompt.com
Subject: Re: strange performance regression between 7.4 and 8.1
In-Reply-To: <a728f9f90703011511m4a0a64fawd65d443ab92b65a4@mail.gmail.com>
References: <a728f9f90703011244r577d56d7y422ab4bd71bb760e@mail.gmail.com> <45E73C90.2080206@commandprompt.com> <Pine.LNX.4.64.0703011253040.3892@discord.home.frostconsultingllc.com> <a728f9f90703011511m4a0a64fawd65d443ab92b65a4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox