Thread: 7.3 vs 7.4 performance

7.3 vs 7.4 performance

From
Orion Henry
Date:
I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
slower than 7.3.4.  Is this common knowledge or am I just unlucky with
my query/data selection?

Things of note that might matter: the machine is a dual Opteron 1.4GHz
running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from the Fedora
distro and the 7.4.1 was the PGDG package.  The database is 3.5 Gigs
with 10 millions rows and the machine had 1 Gig or ram.

Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
more than DOUBLED the speed of all my Postgres queries over the 2.4. =)



--
Orion Henry <orion@trustcommerce.com>

Attachment

Re: 7.3 vs 7.4 performance

From
Josh Berkus
Date:
Orion,

> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4.  Is this common knowledge or am I just unlucky with
> my query/data selection?

No, it's not common knowledge.  It should be the other way around.   Perhaps
it's the queries you picked?   Even so .....  feel free to post individual
EXPLAIN ANALYZEs to the list.

> Things of note that might matter: the machine is a dual Opteron 1.4GHz
> running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from the Fedora
> distro and the 7.4.1 was the PGDG package.  The database is 3.5 Gigs
> with 10 millions rows and the machine had 1 Gig or ram.

I'm wondering if we need specific compile-time switches for Opteron.   I know
we got Opteron code tweaks in the last version, but am not sure if a --with
is required to activate them.

> Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
> more than DOUBLED the speed of all my Postgres queries over the 2.4. =)

Keen.   Waiting for upgrades ....

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: 7.3 vs 7.4 performance

From
Christopher Browne
Date:
Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a wall:
> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4.  Is this common knowledge or am I just unlucky with
> my query/data selection?

That seems unusual; the opposite seems more typical in view of there
being some substantial improvements to the query optimizer.

Have you tried doing EXPLAIN ANALYZE on the queries on both sides?
There would doubtless be interest in figuring out what is breaking
down...

> Things of note that might matter: the machine is a dual Opteron
> 1.4GHz running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from
> the Fedora distro and the 7.4.1 was the PGDG package.  The database
> is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
>
> Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> has more than DOUBLED the speed of all my Postgres queries over the
> 2.4. =)

I did some heavy-transaction-oriented tests recently on somewhat
heftier quad-Xeon hardware, and found little difference between 2.4
and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
Now, I'm quite sure my load was rather different from yours, but I
find the claim of doubling of speed rather surprising.
--
(format nil "~S@~S" "aa454" "freenet.carleton.ca")
http://www.ntlug.org/~cbbrowne/spiritual.html
Failure is not an option. It comes bundled with your Microsoft product.

Re: 7.3 vs 7.4 performance

From
Chris Trawick
Date:
On Thu, 2004-02-05 at 00:32, Christopher Browne wrote:
> > Things of note that might matter: the machine is a dual Opteron
> > 1.4GHz running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from
> > the Fedora distro and the 7.4.1 was the PGDG package.  The database
> > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
> >
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > has more than DOUBLED the speed of all my Postgres queries over the
> > 2.4. =)
>
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.

I don't.  I got a similar boost out of 2.6 when dealing with extreme
concurrency.  Then again, I also got a similar boost out of 7.4.  The
two together tickled my bank account.  ;)

One question though...  It sounds like your 7.3 binaries are 64-bit and
your 7.4 binaries are 32-bit.  Have you tried grabbing the SRPM for 7.4
and recompiling it for X86_64?

chris

Re: 7.3 vs 7.4 performance

From
"Carlos Eduardo Smanioto"
Date:
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.
> --

What's the type of File System you used in the Linux? I am wanting to know
which is the operational system better for PostgreSQL: FreeBSD versus Linux
2.6.

Thanks.

[]'s
Carlos Eduardo Smanioto (Brazil)

----- Original Message -----
From: "Christopher Browne" <cbbrowne@acm.org>
To: <pgsql-performance@postgresql.org>
Sent: Thursday, February 05, 2004 3:32 AM
Subject: Re: [PERFORM] 7.3 vs 7.4 performance


> Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a
wall:
> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> > slower than 7.3.4.  Is this common knowledge or am I just unlucky with
> > my query/data selection?
>
> That seems unusual; the opposite seems more typical in view of there
> being some substantial improvements to the query optimizer.
>
> Have you tried doing EXPLAIN ANALYZE on the queries on both sides?
> There would doubtless be interest in figuring out what is breaking
> down...
>
> > Things of note that might matter: the machine is a dual Opteron
> > 1.4GHz running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from
> > the Fedora distro and the 7.4.1 was the PGDG package.  The database
> > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
> >
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > has more than DOUBLED the speed of all my Postgres queries over the
> > 2.4. =)
>
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.
> --
> (format nil "~S@~S" "aa454" "freenet.carleton.ca")
> http://www.ntlug.org/~cbbrowne/spiritual.html
> Failure is not an option. It comes bundled with your Microsoft product.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html


Re: 7.3 vs 7.4 performance

From
Christopher Browne
Date:
In an attempt to throw the authorities off his trail, csmanioto@uol.com.br ("Carlos Eduardo Smanioto") transmitted:
>> I did some heavy-transaction-oriented tests recently on somewhat
>> heftier quad-Xeon hardware, and found little difference between 2.4
>> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD
>> 4.9.  Now, I'm quite sure my load was rather different from yours,
>> but I find the claim of doubling of speed rather surprising.
>
> What's the type of File System you used in the Linux? I am wanting
> to know which is the operational system better for PostgreSQL:
> FreeBSD versus Linux 2.6.

On the Linux box in question, I was using JFS, which has had the mixed
reviews, lately, that on the one hand, it _appears_ to be a tad faster
than all the others, but that has been, on the other hand, associated
with systems hanging up and crashing, under load.

The latter bit is a _really_ big caveat.  On that particular machine,
I have a nicely repeatable "test case" where I can do a particular set
of "system load" that consistently takes the system down, to the point
of having to hit the "big red button."  If I could point to a clear
reason why it happens, I'd be a much happier camper.  As it stands, it
is a bit nebulous whether the problem is:
 a) Hardware drivers,
 b) Flakey hardware (which Linux 2.6.1 copes with a lot better than
    2.4!),
 c) Flakey 2.4 kernel,
 d) Problem with JFS,
 e) Something else not yet identified as a plausible cause.

If I could say, "Oh, it's an identified bug in the Frobozz RAID
controller drivers, and was fixed in 2.6.0-pre-17", that would help
allay the suspicion that the problem could be any of the above.
--
let name="aa454" and tld="freenet.carleton.ca" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/
"Another result of the tyranny of Pascal is that beginners don't use
function pointers."  --Rob Pike

Re: 7.3 vs 7.4 performance

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> I'm wondering if we need specific compile-time switches for Opteron.   I know
> we got Opteron code tweaks in the last version,

Not in 7.4.  There is some marginal hacking in the spinlock code in CVS
tip for multi-CPU i386 and x86_64 (viz, add a PAUSE instruction inside
the wait loop) but I'm not sure that will have any significance in real
life.

            regards, tom lane

Re: 7.3 vs 7.4 performance

From
Orion Henry
Date:
>
> One question though...  It sounds like your 7.3 binaries are 64-bit and
> your 7.4 binaries are 32-bit.  Have you tried grabbing the SRPM for 7.4
> and recompiling it for X86_64?

No, they were all 64 bit.

I'm going to run explains on all my queries and see if I can find
anything of interest...


Attachment

Re: 7.3 vs 7.4 performance

From
Hannu Krosing
Date:
Orion Henry kirjutas N, 05.02.2004 kell 07:16:
> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4.  Is this common knowledge or am I just unlucky with
> my query/data selection?
>
> Things of note that might matter: the machine is a dual Opteron 1.4GHz
> running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from the Fedora
> distro and the 7.4.1 was the PGDG package.

Are you sure that it is not the case that it is not tha case that 7.3.4
is 64 bit and the PGDG package is 32 ?

> The database is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
>
> Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
> more than DOUBLED the speed of all my Postgres queries over the 2.4. =)

Is this on this same hardware ?

-------------
Hannu


Re: 7.3 vs 7.4 performance

From
Hannu Krosing
Date:
Christopher Browne kirjutas N, 05.02.2004 kell 07:32:
> Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a wall:
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > has more than DOUBLED the speed of all my Postgres queries over the
> > 2.4. =)
>
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.

perhaps you were just IO-bound while he was not ?

or starving on some locks ?

-------------
Hannu


Re: 7.3 vs 7.4 performance

From
markw@osdl.org
Date:
I have some results with our DBT-2 (OLTP) workload on various linux-2.6
filesystems, if you'll find that interesting:
    http://developer.osdl.org/markw/fs/dbt2_project_results.html

I've found JFS to perform similarly to ext2.  Reiserfs isn't far behind.
XFS and ext3 fall off a bit.  These results are also on a 4-way Xeon,
with about 70 drives and a ~ 30GB database.

Mark

On  5 Feb, Carlos Eduardo Smanioto wrote:
>> I did some heavy-transaction-oriented tests recently on somewhat
>> heftier quad-Xeon hardware, and found little difference between 2.4
>> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
>> Now, I'm quite sure my load was rather different from yours, but I
>> find the claim of doubling of speed rather surprising.
>> --
>
> What's the type of File System you used in the Linux? I am wanting to know
> which is the operational system better for PostgreSQL: FreeBSD versus Linux
> 2.6.
>
> Thanks.
>
> []'s
> Carlos Eduardo Smanioto (Brazil)
>
> ----- Original Message -----
> From: "Christopher Browne" <cbbrowne@acm.org>
> To: <pgsql-performance@postgresql.org>
> Sent: Thursday, February 05, 2004 3:32 AM
> Subject: Re: [PERFORM] 7.3 vs 7.4 performance
>
>
>> Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a
> wall:
>> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
>> > slower than 7.3.4.  Is this common knowledge or am I just unlucky with
>> > my query/data selection?
>>
>> That seems unusual; the opposite seems more typical in view of there
>> being some substantial improvements to the query optimizer.
>>
>> Have you tried doing EXPLAIN ANALYZE on the queries on both sides?
>> There would doubtless be interest in figuring out what is breaking
>> down...
>>
>> > Things of note that might matter: the machine is a dual Opteron
>> > 1.4GHz running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from
>> > the Fedora distro and the 7.4.1 was the PGDG package.  The database
>> > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
>> >
>> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
>> > has more than DOUBLED the speed of all my Postgres queries over the
>> > 2.4. =)
>>
>> I did some heavy-transaction-oriented tests recently on somewhat
>> heftier quad-Xeon hardware, and found little difference between 2.4
>> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
>> Now, I'm quite sure my load was rather different from yours, but I
>> find the claim of doubling of speed rather surprising.
>> --
>> (format nil "~S@~S" "aa454" "freenet.carleton.ca")
>> http://www.ntlug.org/~cbbrowne/spiritual.html
>> Failure is not an option. It comes bundled with your Microsoft product.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly


Re: 7.3 vs 7.4 performance

From
Orion Henry
Date:
On Fri, 2004-02-06 at 02:43, Hannu Krosing wrote:
> Orion Henry kirjutas N, 05.02.2004 kell 07:16:
> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> > slower than 7.3.4.  Is this common knowledge or am I just unlucky with
> > my query/data selection?
> >
> > Things of note that might matter: the machine is a dual Opteron 1.4GHz
> > running Fedora Core 1 Test 1 for X86_64.  The 7.3.4 was from the Fedora
> > distro and the 7.4.1 was the PGDG package.
>
> Are you sure that it is not the case that it is not tha case that 7.3.4
> is 64 bit and the PGDG package is 32 ?

Yes sure... I don't know if they were compiled with differing
optimizations or compilers though...

> > The database is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
> >
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
> > more than DOUBLED the speed of all my Postgres queries over the 2.4. =)
>
> Is this on this same hardware ?

No.  I havent gotten the 2.6 kernel working on the Opteron yet.  The 2x speedup
was on a dual Athlon 2GHz.


Attachment

Re: 7.3 vs 7.4 performance

From
Orion Henry
Date:
On Fri, 2004-02-06 at 02:44, Hannu Krosing wrote:
> Christopher Browne kirjutas N, 05.02.2004 kell 07:32:
> > Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a wall:
> > > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > > has more than DOUBLED the speed of all my Postgres queries over the
> > > 2.4. =)
> >
> > I did some heavy-transaction-oriented tests recently on somewhat
> > heftier quad-Xeon hardware, and found little difference between 2.4
> > and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> > Now, I'm quite sure my load was rather different from yours, but I
> > find the claim of doubling of speed rather surprising.
>
> perhaps you were just IO-bound while he was not ?
>
> or starving on some locks ?

The queries were across almost 4 gigs of data on a machine with 512 MB of ram.
I personally was assuming it was the anticipatory disk scheduler... but alas I
don't know why it affected me so much.


Attachment

Re: 7.3 vs 7.4 performance

From
Orion Henry
Date:
On Wed, 2004-02-04 at 21:27, Josh Berkus wrote:
Orion,

> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4.  Is this common knowledge or am I just unlucky with
> my query/data selection?

No, it's not common knowledge.  It should be the other way around.   Perhaps 
it's the queries you picked?   Even so .....  feel free to post individual 
EXPLAIN ANALYZEs to the list.

Thank you...

Here's one good example of 7.3 beating 7.4 soundly:
Again this could me some compile option since I built the 7.4 RPM 
from source and I got the 7.3 from Fedora or something to
do with the Opteron architecture.  (Yes the compiled postgres
is 64 bit)

SELECT cid,media_name,media_type,count(*) as count,sum(a_amount) 
as a,sum(case when b_amount > 0 then b_amount else 0 end) as b,
sum(case when b_amount < 0 then b_amount else 0 end) as c 
FROM transdata JOIN media_info ON (media = media_type) 
WHERE cid = 140100 AND demo is not null 
AND trans_date between date '2004-01-01' 
AND date_trunc('month',date '2004-01-01' + interval '32 days') 
GROUP BY cid,media_name,media_type;

Here's 7.3's time and explain

real    0m34.260s
user    0m0.010s
sys     0m0.000s

---------------------------------------------------------------Aggregate  (cost=7411.88..7415.32 rows=17 width=25)
   ->  Group  (cost=7411.88..7413.60 rows=172 width=25)
         ->  Sort  (cost=7411.88..7412.31 rows=172 width=25)
               Sort Key: transdata.cid, media_info.media_name, transdata.media_type
               ->  Hash Join  (cost=1.22..7405.50 rows=172 width=25)
                     Hash Cond: ("outer".media_type = "inner".media)
                     ->  Index Scan using transdata_date_index on transdata  (cost=0.00..7401.27 rows=172 width=14)
                           Index Cond: ((trans_date >= ('2004-01-01'::date)::timestamp with time zone) AND (trans_date <= ('2004-02-01 00:00:00'::timestamp without time zone)::timestamp with time zone))
                           Filter: ((cid = 140100) AND (demo IS NOT NULL))
                     ->  Hash  (cost=1.18..1.18 rows=18 width=11)
                           ->  Seq Scan on media_info  (cost=0.00..1.18 rows=18 width=11)


Here's 7.4's time and explain

real    0m43.052s
user    0m0.000s
sys     0m0.020s

                                                                                          QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------HashAggregate  (cost=8098.26..8098.29 rows=2 width=23)
   ->  Hash Join  (cost=1.22..8095.48 rows=159 width=23)
         Hash Cond: ("outer".media_type = "inner".media)
         ->  Index Scan using transdata_date_index on transdata  (cost=0.00..8091.87 rows=159 width=14)
               Index Cond: ((trans_date >= ('2004-01-01'::date)::timestamp with time zone) AND (trans_date <= ('2004-02-01 00:00:00'::timestamp without time zone)::timestamp with time zone))
               Filter: ((cid = 140100) AND (demo IS NOT NULL))
         ->  Hash  (cost=1.18..1.18 rows=18 width=11)
               ->  Seq Scan on media_info  (cost=0.00..1.18 rows=18 width=11)



Attachment

Re: 7.3 vs 7.4 performance

From
Josh Berkus
Date:
Orion,

> Here's one good example of 7.3 beating 7.4 soundly:
> Again this could me some compile option since I built the 7.4 RPM
> from source and I got the 7.3 from Fedora or something to
> do with the Opteron architecture.  (Yes the compiled postgres
> is 64 bit)

Need an EXPLAIN ANALYZE, not just an EXPLAIN.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: 7.3 vs 7.4 performance

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Orion,
>> Here's one good example of 7.3 beating 7.4 soundly:

> Need an EXPLAIN ANALYZE, not just an EXPLAIN.

Indeed.  Also, please try 7.4 with enable_hashagg turned off to see
what it does with a 7.3-style plan.

            regards, tom lane