pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL vs MySQL, and FreeBSD
20+ messages / 16 participants
[nested] [flat]
* PostgreSQL vs MySQL, and FreeBSD
@ 2007-11-09 12:06 Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 14:49 ` Re: PostgreSQL vs MySQL, and FreeBSD Jonah H. Harris <jonah.harris@gmail.com>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 15:50 ` Re: PostgreSQL vs MySQL, and FreeBSD Erik Jones <erik@myemma.com>
0 siblings, 3 replies; 20+ messages in thread
From: Ivan Voras @ 2007-11-09 12:06 UTC (permalink / raw)
To: pgsql-performance
Hi,
I just read this document and thought I should share it with this list:
http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf
Among other things (FreeBSD advocacy, mostly :) ), it contains a direct
comparison between MySQL and PostgreSQL on various platforms, with
PostgreSQL winning!
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
@ 2007-11-09 14:49 ` Jonah H. Harris <jonah.harris@gmail.com>
2 siblings, 0 replies; 20+ messages in thread
From: Jonah H. Harris @ 2007-11-09 14:49 UTC (permalink / raw)
To: Ivan Voras <ivoras@geri.cc.fer.hr>; +Cc: pgsql-performance
On Nov 9, 2007 7:06 AM, Ivan Voras <ivoras@geri.cc.fer.hr> wrote:
> I just read this document and thought I should share it with this list:
>
> http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf
Nice presentation. Thanks for posting it on here.
> Among other things (FreeBSD advocacy, mostly :) ), it contains a direct
> comparison between MySQL and PostgreSQL on various platforms, with
> PostgreSQL winning!
:)
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
@ 2007-11-09 15:41 ` Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 15:57 ` Re: PostgreSQL vs MySQL, and FreeBSD Scott Marlowe <scott.marlowe@gmail.com>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-16 09:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2 siblings, 3 replies; 20+ messages in thread
From: Sebastian Hennebrueder @ 2007-11-09 15:41 UTC (permalink / raw)
To: pgsql-performance
>
> Among other things (FreeBSD advocacy, mostly :) ), it contains a direct
> comparison between MySQL and PostgreSQL on various platforms, with
> PostgreSQL winning!
>
Hello,
If the queries are complex, this is understable. I had a performance
review of a Hibernate project (Java Object Relation Mapping) using
MySQL. ORM produces easily "complex" queries with joins and subqueries.
MySQL uses nested loops for subqueries which lead to performance issues
with growing database size.
They state in their documentation that for version 5.2 there are
improvements planned regarding this kind of query.
Best Regards
Sebastian
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
@ 2007-11-09 15:57 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 20+ messages in thread
From: Scott Marlowe @ 2007-11-09 15:57 UTC (permalink / raw)
To: Sebastian Hennebrueder <usenet@laliluna.de>; +Cc: pgsql-performance
On Nov 9, 2007 9:41 AM, Sebastian Hennebrueder <usenet@laliluna.de> wrote:
> If the queries are complex, this is understable. I had a performance
> review of a Hibernate project (Java Object Relation Mapping) using
> MySQL. ORM produces easily "complex" queries with joins and subqueries.
> MySQL uses nested loops for subqueries which lead to performance issues
> with growing database size.
>
> They state in their documentation that for version 5.2 there are
> improvements planned regarding this kind of query.
So, MySQL 5.2 will be catching up to version 7.1 or 7.2 of PostgreSQL
in that regard?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
@ 2007-11-09 16:11 ` Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
2 siblings, 1 reply; 20+ messages in thread
From: Greg Smith @ 2007-11-09 16:11 UTC (permalink / raw)
To: pgsql-performance
On Fri, 9 Nov 2007, Sebastian Hennebrueder wrote:
> If the queries are complex, this is understable.
The queries used for this comparison are trivial. There's only one table
involved and there are no joins. It's testing very low-level aspects of
performance.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
@ 2007-11-09 19:04 ` Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Re: PostgreSQL vs MySQL, and FreeBSD Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 1 reply; 20+ messages in thread
From: Bill Moran @ 2007-11-09 19:04 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
On Fri, 9 Nov 2007 11:11:18 -0500 (EST)
Greg Smith <gsmith@gregsmith.com> wrote:
> On Fri, 9 Nov 2007, Sebastian Hennebrueder wrote:
>
> > If the queries are complex, this is understable.
>
> The queries used for this comparison are trivial. There's only one table
> involved and there are no joins. It's testing very low-level aspects of
> performance.
Actually, what it's really showing is parallelism, and I've always
expected PostgreSQL to come out on top in that arena.
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
@ 2007-11-09 19:44 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-11-11 19:27 ` Re: PostgreSQL vs MySQL, and FreeBSD Dimitri <dimitrik.fr@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Ron Mayer @ 2007-11-09 19:44 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>
Bill Moran wrote:
> On Fri, 9 Nov 2007 11:11:18 -0500 (EST)
> Greg Smith <gsmith@gregsmith.com> wrote:
>> On Fri, 9 Nov 2007, Sebastian Hennebrueder wrote:
>>> If the queries are complex, this is understable.
>> The queries used for this comparison are trivial. There's only one table
>> involved and there are no joins. It's testing very low-level aspects of
>> performance.
>
> Actually, what it's really showing is parallelism, and I've always
> expected PostgreSQL to come out on top in that arena.
Isn't it showing Postgres winning even without parallelism.
At 1 threads, Postgres looks like 800TPS where MysQL comes
in at about 600TPS on their Opteron charts.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Re: PostgreSQL vs MySQL, and FreeBSD Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2007-11-11 19:27 ` Dimitri <dimitrik.fr@gmail.com>
2007-11-11 20:17 ` Re: PostgreSQL vs MySQL, and FreeBSD Joshua D. Drake <jd@commandprompt.com>
2007-11-11 23:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-11-14 19:47 ` Re: PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@freebsd.org>
0 siblings, 3 replies; 20+ messages in thread
From: Dimitri @ 2007-11-11 19:27 UTC (permalink / raw)
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: Bill Moran <wmoran@collaborativefusion.com>; pgsql-performance
Seems to me there is more thread model implementation problem on
FreeBSD, and databases just reflecting it... Most of the test I done
on Solaris show the same performance level on the same short READ-only
queries for MySQL and PostgreSQL.
And to be honest till the end, thread model should be far faster
(context switching between threads is way faster vs processes), but -
as I say usually - even a very good idea may be just wasted by a poor
implementation... And in case of MySQL they have too much locking to
manage concurrency between threads which kills all thread model
benefits... Also, to compare apples to apples, they should run this
test from remote client rather locally on the same host - however in
this case the result for PostgreSQL will mostly depends on client
implementation: if client implements reading via CURSOR (quite often),
reading will generate 4x times more intensive network traffic than
necessary and final PostgreSQL result will be worse...
Reading this article I'm just happy for them to see progress done on FreeBSD :-)
As well to demonstrate OS parallelism it's not so impressive to see
4CPU server results rather 8CPU or 32threaded Niagara... Don't know
why they did not present similar performance graphs for these
platform, strange no?...
Rgds,
-Dimitri
On 11/9/07, Ron Mayer <rm_pg@cheapcomplexdevices.com> wrote:
> Bill Moran wrote:
> > On Fri, 9 Nov 2007 11:11:18 -0500 (EST)
> > Greg Smith <gsmith@gregsmith.com> wrote:
> >> On Fri, 9 Nov 2007, Sebastian Hennebrueder wrote:
> >>> If the queries are complex, this is understable.
> >> The queries used for this comparison are trivial. There's only one table
> >> involved and there are no joins. It's testing very low-level aspects of
> >> performance.
> >
> > Actually, what it's really showing is parallelism, and I've always
> > expected PostgreSQL to come out on top in that arena.
>
> Isn't it showing Postgres winning even without parallelism.
>
> At 1 threads, Postgres looks like 800TPS where MysQL comes
> in at about 600TPS on their Opteron charts.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Re: PostgreSQL vs MySQL, and FreeBSD Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-11-11 19:27 ` Re: PostgreSQL vs MySQL, and FreeBSD Dimitri <dimitrik.fr@gmail.com>
@ 2007-11-11 20:17 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-12 15:22 ` Re: PostgreSQL vs MySQL, and FreeBSD Erik Jones <erik@myemma.com>
2 siblings, 1 reply; 20+ messages in thread
From: Joshua D. Drake @ 2007-11-11 20:17 UTC (permalink / raw)
To: Dimitri <dimitrik.fr@gmail.com>; +Cc: Ron Mayer <rm_pg@cheapcomplexdevices.com>; Bill Moran <wmoran@collaborativefusion.com>; pgsql-performance
Dimitri wrote:
> Seems to me there is more thread model implementation problem on
> FreeBSD, and databases just reflecting it... Most of the test I done
> on Solaris show the same performance level on the same short READ-only
> queries for MySQL and PostgreSQL.
>
> And to be honest till the end, thread model should be far faster
> (context switching between threads is way faster vs processes), but -
> as I say usually - even a very good idea may be just wasted by a poor
> implementation... And in case of MySQL they have too much locking to
> manage concurrency between threads which kills all thread model
> benefits... Also, to compare apples to apples, they should run this
> test from remote client rather locally on the same host - however in
> this case the result for PostgreSQL will mostly depends on client
> implementation: if client implements reading via CURSOR (quite often),
> reading will generate 4x times more intensive network traffic than
> necessary and final PostgreSQL result will be worse...
>
> Reading this article I'm just happy for them to see progress done on FreeBSD :-)
> As well to demonstrate OS parallelism it's not so impressive to see
> 4CPU server results rather 8CPU or 32threaded Niagara... Don't know
> why they did not present similar performance graphs for these
> platform, strange no?...
I don't find it strange. I would rather see benchmarks on what the
majority of people running on the platform are going to run.
Most people don't run 8core machines and they especially don't run
32thread Niagra boxes.
Joshua D. Drake
>
> Rgds,
> -Dimitri
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Re: PostgreSQL vs MySQL, and FreeBSD Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-11-11 19:27 ` Re: PostgreSQL vs MySQL, and FreeBSD Dimitri <dimitrik.fr@gmail.com>
2007-11-11 20:17 ` Re: PostgreSQL vs MySQL, and FreeBSD Joshua D. Drake <jd@commandprompt.com>
@ 2007-11-12 15:22 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 20+ messages in thread
From: Erik Jones @ 2007-11-12 15:22 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Dimitri <dimitrik.fr@gmail.com>; Ron Mayer <rm_pg@cheapcomplexdevices.com>; Bill Moran <wmoran@collaborativefusion.com>; pgsql-performance
On Nov 11, 2007, at 2:17 PM, Joshua D. Drake wrote:
> Dimitri wrote:
>> Seems to me there is more thread model implementation problem on
>> FreeBSD, and databases just reflecting it... Most of the test I done
>> on Solaris show the same performance level on the same short READ-
>> only
>> queries for MySQL and PostgreSQL.
>> And to be honest till the end, thread model should be far faster
>> (context switching between threads is way faster vs processes), but -
>> as I say usually - even a very good idea may be just wasted by a poor
>> implementation... And in case of MySQL they have too much locking to
>> manage concurrency between threads which kills all thread model
>> benefits... Also, to compare apples to apples, they should run this
>> test from remote client rather locally on the same host - however in
>> this case the result for PostgreSQL will mostly depends on client
>> implementation: if client implements reading via CURSOR (quite
>> often),
>> reading will generate 4x times more intensive network traffic than
>> necessary and final PostgreSQL result will be worse...
>> Reading this article I'm just happy for them to see progress done
>> on FreeBSD :-)
>> As well to demonstrate OS parallelism it's not so impressive to see
>> 4CPU server results rather 8CPU or 32threaded Niagara... Don't know
>> why they did not present similar performance graphs for these
>> platform, strange no?...
>
> I don't find it strange. I would rather see benchmarks on what the
> majority of people running on the platform are going to run.
>
> Most people don't run 8core machines and they especially don't run
> 32thread Niagra boxes.
Wait! So, what do you check you're email with? :)
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Re: PostgreSQL vs MySQL, and FreeBSD Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-11-11 19:27 ` Re: PostgreSQL vs MySQL, and FreeBSD Dimitri <dimitrik.fr@gmail.com>
@ 2007-11-11 23:43 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-11-12 10:23 ` Re: PostgreSQL vs MySQL, and FreeBSD Shane Ambler <pgsql@Sheeky.Biz>
2 siblings, 1 reply; 20+ messages in thread
From: Steinar H. Gunderson @ 2007-11-11 23:43 UTC (permalink / raw)
To: pgsql-performance
On Sun, Nov 11, 2007 at 08:27:02PM +0100, Dimitri wrote:
> As well to demonstrate OS parallelism it's not so impressive to see
> 4CPU server results rather 8CPU or 32threaded Niagara... Don't know
> why they did not present similar performance graphs for these
> platform, strange no?...
I guess it's because their Niagara support is still very raw, and besides,
it's not a very common platform.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Re: PostgreSQL vs MySQL, and FreeBSD Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-11-11 19:27 ` Re: PostgreSQL vs MySQL, and FreeBSD Dimitri <dimitrik.fr@gmail.com>
2007-11-11 23:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2007-11-12 10:23 ` Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 0 replies; 20+ messages in thread
From: Shane Ambler @ 2007-11-12 10:23 UTC (permalink / raw)
To: pgsql-performance
Steinar H. Gunderson wrote:
> On Sun, Nov 11, 2007 at 08:27:02PM +0100, Dimitri wrote:
>> As well to demonstrate OS parallelism it's not so impressive to see
>> 4CPU server results rather 8CPU or 32threaded Niagara... Don't know
>> why they did not present similar performance graphs for these
>> platform, strange no?...
>
> I guess it's because their Niagara support is still very raw, and besides,
> it's not a very common platform.
>
> /* Steinar */
Not sure how much coding would need to be done for Niagra chips but I
would think that it is more likely a problem of getting the funds so
they can have one to work on.
--
Shane Ambler
pgSQL@Sheeky.Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 16:11 ` Re: PostgreSQL vs MySQL, and FreeBSD Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Re: PostgreSQL vs MySQL, and FreeBSD Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Re: PostgreSQL vs MySQL, and FreeBSD Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-11-11 19:27 ` Re: PostgreSQL vs MySQL, and FreeBSD Dimitri <dimitrik.fr@gmail.com>
@ 2007-11-14 19:47 ` Ivan Voras <ivoras@freebsd.org>
2 siblings, 0 replies; 20+ messages in thread
From: Ivan Voras @ 2007-11-14 19:47 UTC (permalink / raw)
To: pgsql-performance
Dimitri wrote:
> Reading this article I'm just happy for them to see progress done on FreeBSD :-)
> As well to demonstrate OS parallelism it's not so impressive to see
> 4CPU server results rather 8CPU or 32threaded Niagara... Don't know
> why they did not present similar performance graphs for these
> platform, strange no?...
Well, most of the results in the document
(http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf) are for
8-CPU machines, which is about the most you can get with off the shelf
hardware (2x4-core CPU, the document has both Xeon and Opteron results).
Niagara support is unfinished, so there's nothing to report there. On
the other hand, the document does compare between several versions of
Linux, FreeBSD, NetBSD and DragonflyBSD, with both MySQL and PostgreSQL,
so you can draw your conclusions (if any) from there.
Attachments:
signature.asc (application/pgp-signature, 249B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
@ 2007-11-16 09:43 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-11-16 15:56 ` Re: PostgreSQL vs MySQL, and FreeBSD Dave Dutcher <dave@tridecap.com>
2 siblings, 1 reply; 20+ messages in thread
From: Ow Mun Heng @ 2007-11-16 09:43 UTC (permalink / raw)
To: Sebastian Hennebrueder <usenet@laliluna.de>; +Cc: pgsql-performance
On Fri, 2007-11-09 at 16:41 +0100, Sebastian Hennebrueder wrote:
> If the queries are complex, this is understable. I had a performance
> review of a Hibernate project (Java Object Relation Mapping) using
> MySQL. ORM produces easily "complex" queries with joins and subqueries.
> MySQL uses nested loops for subqueries which lead to performance issues
> with growing database size.
Even for Postgresql, nested loops are still evil and hampers
performance.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-16 09:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Ow Mun Heng <Ow.Mun.Heng@wdc.com>
@ 2007-11-16 15:56 ` Dave Dutcher <dave@tridecap.com>
2007-11-16 16:06 ` Re: PostgreSQL vs MySQL, and FreeBSD Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Dave Dutcher @ 2007-11-16 15:56 UTC (permalink / raw)
To: 'Ow Mun Heng' <Ow.Mun.Heng@wdc.com>; +Cc: pgsql-performance
> -----Original Message-----
> From: Ow Mun Heng
> Subject: Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
>
> Even for Postgresql, nested loops are still evil and hampers
> performance.
I don't know about that. There are times when it is the right plan:
explain analyze select * from table1 t1 inner join table2 t2 on t1.f_id =
t2.id where t1.id = 'xyzzy';
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------
Nested Loop (cost=0.00..17.65 rows=1 width=344) (actual time=0.080..0.096
rows=1 loops=1)
-> Index Scan using table1_pkey on table1 t (cost=0.00..9.18 rows=1
width=238) (actual time=0.044..0.048 rows=1 loops=1)
Index Cond: ((id)::text = 'xyzzy'::text)
-> Index Scan using table2_pkey on table2 i (cost=0.00..8.46 rows=1
width=106) (actual time=0.019..0.023 rows=1 loops=1)
Index Cond: (t.f_id = i.id)
Total runtime: 0.224 ms
set enable_nestloop=off;
SET
explain analyze select * from table1 t1 inner join table2 t2 on t1.f_id =
t2.id where t1.id = 'xyzzy';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------------
Hash Join (cost=9.18..72250.79 rows=1 width=344) (actual
time=13493.572..15583.049 rows=1 loops=1)
Hash Cond: (i.id = t.f_id)
-> Seq Scan on table2 i (cost=0.00..61297.40 rows=2188840 width=106)
(actual time=0.015..8278.347 rows=2188840 loops=1)
-> Hash (cost=9.18..9.18 rows=1 width=238) (actual time=0.056..0.056
rows=1 loops=1)
-> Index Scan using table1_pkey on table1 t (cost=0.00..9.18
rows=1 width=238) (actual time=0.040..0.045 rows=1 loops=1)
Index Cond: ((id)::text = 'xyzzy'::text)
Total runtime: 15583.212 ms
(I changed the table names, but everything else is real.)
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-16 09:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-11-16 15:56 ` Re: PostgreSQL vs MySQL, and FreeBSD Dave Dutcher <dave@tridecap.com>
@ 2007-11-16 16:06 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-11-16 20:36 ` Re: PostgreSQL vs MySQL, and FreeBSD Josh Trutwin <josh@trutwins.homeip.net>
2007-11-19 01:27 ` Re: PostgreSQL vs MySQL, and FreeBSD Ow Mun Heng <Ow.Mun.Heng@wdc.com>
0 siblings, 2 replies; 20+ messages in thread
From: Jonah H. Harris @ 2007-11-16 16:06 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: Ow Mun Heng <Ow.Mun.Heng@wdc.com>; pgsql-performance
On Nov 16, 2007 10:56 AM, Dave Dutcher <dave@tridecap.com> wrote:
> I don't know about that. There are times when it is the right plan:
Agreed. IMHO, there's nothing wrong with nested-loop join as long as
it's being used properly.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-16 09:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-11-16 15:56 ` Re: PostgreSQL vs MySQL, and FreeBSD Dave Dutcher <dave@tridecap.com>
2007-11-16 16:06 ` Re: PostgreSQL vs MySQL, and FreeBSD Jonah H. Harris <jonah.harris@gmail.com>
@ 2007-11-16 20:36 ` Josh Trutwin <josh@trutwins.homeip.net>
2007-11-16 20:53 ` Re: PostgreSQL vs MySQL, and FreeBSD Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Josh Trutwin @ 2007-11-16 20:36 UTC (permalink / raw)
To: pgsql-performance
On Fri, 16 Nov 2007 11:06:11 -0500
"Jonah H. Harris" <jonah.harris@gmail.com> wrote:
> On Nov 16, 2007 10:56 AM, Dave Dutcher <dave@tridecap.com> wrote:
> > I don't know about that. There are times when it is the right
> > plan:
>
> Agreed. IMHO, there's nothing wrong with nested-loop join as long
> as it's being used properly.
Can you explain further please? (I'm not disagreeing with you, just
want to know when nested loops are not used properly - does the
planner make mistakes that you have to watch out for?)
Thx,
Josh
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-16 09:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-11-16 15:56 ` Re: PostgreSQL vs MySQL, and FreeBSD Dave Dutcher <dave@tridecap.com>
2007-11-16 16:06 ` Re: PostgreSQL vs MySQL, and FreeBSD Jonah H. Harris <jonah.harris@gmail.com>
2007-11-16 20:36 ` Re: PostgreSQL vs MySQL, and FreeBSD Josh Trutwin <josh@trutwins.homeip.net>
@ 2007-11-16 20:53 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Jonah H. Harris @ 2007-11-16 20:53 UTC (permalink / raw)
To: Josh Trutwin <josh@trutwins.homeip.net>; +Cc: pgsql-performance
On Nov 16, 2007 3:36 PM, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> > Agreed. IMHO, there's nothing wrong with nested-loop join as long
> > as it's being used properly.
>
> Can you explain further please? (I'm not disagreeing with you, just
> want to know when nested loops are not used properly - does the
> planner make mistakes that you have to watch out for?)
As long as statistics are updated properly, it's generally not an
issue. You just don't want the system using a nested-loop join
incorrectly (like when table sizes are equal, the outer table is
larger than the inner table, or the inner table itself is overly
large).
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 15:41 ` Re: PostgreSQL vs MySQL, and FreeBSD Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-16 09:43 ` Re: PostgreSQL vs MySQL, and FreeBSD Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-11-16 15:56 ` Re: PostgreSQL vs MySQL, and FreeBSD Dave Dutcher <dave@tridecap.com>
2007-11-16 16:06 ` Re: PostgreSQL vs MySQL, and FreeBSD Jonah H. Harris <jonah.harris@gmail.com>
@ 2007-11-19 01:27 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
1 sibling, 0 replies; 20+ messages in thread
From: Ow Mun Heng @ 2007-11-19 01:27 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: Dave Dutcher <dave@tridecap.com>; pgsql-performance
On Fri, 2007-11-16 at 11:06 -0500, Jonah H. Harris wrote:
> On Nov 16, 2007 10:56 AM, Dave Dutcher <dave@tridecap.com> wrote:
> > I don't know about that. There are times when it is the right plan:
>
> Agreed. IMHO, there's nothing wrong with nested-loop join as long as
> it's being used properly.
I do agree also, but in some other cases, the usage of nested loops (esp
when the number of rows estimated to be returned vs the actual number of
rows being returned differs by up to 100x (or more) then it becomes a
major issue.
The example pointed out by Dave D shows the est rows = 1 and actual
rows=1, then good performance of course.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: PostgreSQL vs MySQL, and FreeBSD
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
@ 2007-11-09 15:50 ` Erik Jones <erik@myemma.com>
2 siblings, 0 replies; 20+ messages in thread
From: Erik Jones @ 2007-11-09 15:50 UTC (permalink / raw)
To: Ivan Voras <ivoras@geri.cc.fer.hr>; +Cc: pgsql-performance
On Nov 9, 2007, at 6:06 AM, Ivan Voras wrote:
> Hi,
>
> I just read this document and thought I should share it with this
> list:
>
> http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf
>
> Among other things (FreeBSD advocacy, mostly :) ), it contains a
> direct
> comparison between MySQL and PostgreSQL on various platforms, with
> PostgreSQL winning!
Which is typical for those who aren't in on the FUD :)
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2007-11-19 01:27 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-09 12:06 PostgreSQL vs MySQL, and FreeBSD Ivan Voras <ivoras@geri.cc.fer.hr>
2007-11-09 14:49 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-11-09 15:41 ` Sebastian Hennebrueder <usenet@laliluna.de>
2007-11-09 15:57 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-11-09 16:11 ` Greg Smith <gsmith@gregsmith.com>
2007-11-09 19:04 ` Bill Moran <wmoran@collaborativefusion.com>
2007-11-09 19:44 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2007-11-11 19:27 ` Dimitri <dimitrik.fr@gmail.com>
2007-11-11 20:17 ` Joshua D. Drake <jd@commandprompt.com>
2007-11-12 15:22 ` Erik Jones <erik@myemma.com>
2007-11-11 23:43 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-11-12 10:23 ` Shane Ambler <pgsql@Sheeky.Biz>
2007-11-14 19:47 ` Ivan Voras <ivoras@freebsd.org>
2007-11-16 09:43 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-11-16 15:56 ` Dave Dutcher <dave@tridecap.com>
2007-11-16 16:06 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-11-16 20:36 ` Josh Trutwin <josh@trutwins.homeip.net>
2007-11-16 20:53 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-11-19 01:27 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
2007-11-09 15:50 ` Erik Jones <erik@myemma.com>
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