Обсуждение: 7.3 vs 7.4 performance
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>
Вложения
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
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.
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
> 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
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
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
> > 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...
Вложения
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
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
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
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.
Вложения
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.
Вложения
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)
Вложения
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
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