Re: [PERFORM] 7.3 vs 7.4 performance
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. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] 7.3 vs 7.4 performance
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)
signature.asc
Description: This is a digitally signed message part
Re: [PERFORM] 7.3 vs 7.4 performance
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] 7.3 vs 7.4 performance
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] 7.3 vs 7.4 performance
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 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 05, 2004 3:32 AM Subject: Re: [PERFORM] 7.3 vs 7.4 performance Oops! [EMAIL PROTECTED] (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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 7.3 vs 7.4 performance
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] 7.3 vs 7.4 performance
Josh Berkus [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
