Thread: PostgreSQL vs Mysql comparison

PostgreSQL vs Mysql comparison

From
Scott Perkins
Date:
You make a good point about available admin and other facilities etc.

I am interested in some of the forum functionalities that seem to be "off the
shelf" with Mysql but I have not done my homework yet with PostgreSQL.  There
must me some good reasons why PostgreSQL beat out
Mysql for product of the year  or best database or most popular among
NIX/NUX users or something recently.

I am just trying to clearly differientiate the significant differences
and decide which are important relative to specific projects.

I think if I have to do some fancy complex search/ retrieval from heavily
indexed multiple related tables the union and subselect capability of PostgreSQL
might help alot.

If I have to develop my own PHP admin  - that will hurt.  There must
be some available routines floating around for PostGreSQL I just have to find
them.



mcgarry@tig.com.au wrote:
>
> Is there a cool PHPMyAdmin style utility, or failing that a Gnome/X app
> that has similar abilities (creating tables, etc etc) for PostgreSQL?
>
> I'm working on a couple of pet projects in my spare time (hah!), one
> which I think mysql is perfect for, but another that I think
> transactions etc are going to be important as I add features.
>
> So far I've done all the work with mysql, largely because it's so easy
> with phpMyAdmin, but I think I'd be better off switching before I get
> too invested and have to go through all the code and convert it all.
>
> I'm happy to learn all the ins and outs of PostgreSQL in the long run,
> but in the short term I just want to get things moving.
>
> Cheers,
>
> Paul.
>
> --
> PHP 3 Mailing List <http://www.php.net/>
> To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
> To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
> To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
> To contact the list administrators, e-mail: php-list-admin@lists.php.net

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: PostgreSQL vs Mysql comparison

From
Patrick Greenwell
Date:
On Sat, 2 Oct 1999, Scott Perkins wrote:

> You make a good point about available admin and other facilities etc.
>
> I am interested in some of the forum functionalities that seem to be "off the
> shelf" with Mysql but I have not done my homework yet with PostgreSQL.  There
> must me some good reasons why PostgreSQL beat out
> Mysql for product of the year  or best database or most popular among
> NIX/NUX users or something recently.

Because the Postgres mailing list was informed of the vote and apparently
had more members that voted than the MySQL list did. To put awards based on web
polls in perspective, "Hank, the angry drunken dwarf" won the "most
beautiful person in show business" award on People Magazines website
earlier this year. In other words, I wouldn't take the voting or the award
for most popular database very seriously given the voting methodology...

> I am just trying to clearly differientiate the significant differences
> and decide which are important relative to specific projects.

Transaction support, transaction support, transaction support...

Don't forget sub-selects, views, row-level locking, stored procedures,
triggers, foreign keys, cascading updates/deletes, and probably a couple
other things that escape me at the moment.

MySQL is great for what it is: a small, exceedingly fast, easy to
administer, stable SQL-server for enviroments where the majority of access
consist of text reads (like a lot of web sites).

It is not as stellar if you are doing things that are best accomplished by real
transactions at the database level rather than being forced to program
around the lack of them (such as complex financial transactions,) nor is
it the best choice in enviroments where database views, stored
procedures/triggers are of significant necessity/benefit, in high
read/write concurrency enviroments, enviroments with a high percentage of
BLOB data(if anyone has seen any SQL implementation that truly handles
this well let me know,) or enviroments requiring realtime database
replication/redundancy, etc.

Postgres is certainly significantly slower than MySQL according to the
MySQL benchmarks(somewhat tellingly IMO the Postgres site doesn't have any
benchmarks of their own other than some feature comparisons with a couple
of different SQL servers that don't include MySQL.)

The use of Postgres doesn't fix the BLOBs or database replication/redundancy
issue. It may address high concurrency read/write issues to some extent
with row-level locking(as opposed to MySQL, which only supports
potentially much more lock contentious table-level locking.) What Postgres
certainly does have is a richer feature set. While this is at least
partially responsible for the apparent considerable slow down in Postgres
vs. MySQL, it can make it much easier and faster for database
designers/programmers to develop databases/applications or to do ports
from databases/applications  that use a fully(or almost fully) compliant
ANSI SQL/92 server(such as Oracle,) especially if they are accustomed to
the feature set that such servers offer.

The MySQL docs have a list of missing functionality at:
http://mysql.he.net/Manual_chapter/manual_Compatibility.html#Missing_functions
and discuss some work-arounds, planned additions, etc. To summarize:

    o Sub-selects will probably be available in 3.24.0.

    o Atomic operations, a component of true transaction support
      where a series of operations are performed as a single operation
      will be supported however without rollback capabilities.

    o Stored procedures are planned, but without trigger support.

    o Foreign Key support is planned

    o No mention of cascading updates/deletes I could find, although this
      could be part of real foreign key support(?) and the corresponding
      referential integrity it hopefully provides.

    o No mention of row-level locking

Sometimes, features you think you need, or are told you need aren't really
needed at all. In the cases where you really do need specific functionality, it
*usually* isn't too difficult to program around the lack of, or only
partial support for that functionality. That said, doing this will almost
certainly result in longer development cycles as you first have to
determine if you really need the feature in the first place, and how
badly. If you can't live without it, you must then find or create the
appropriate work-around, and probably adding code to the application to
make it do what the SQL server normally would. This could potentially
slow the application. This can also be a much more error-prone process. As
an example, it would be much easier, safer, and faster(development wise)
to use the native database functionality of a reasonably-written server to
achieve proper transactions support w/commit, rollback and contingency
actions for failures than it would be to write your own from scratch.

So the long and short of it is this: if you are *really* certain that you
need native support for specific features that MySQL doesn't offer
and/or know it would take considerably longer to develop your
application with appropriate work-arounds, and/or know that
work-arounds would slow the application significantly, then you are
probably better off with Postgres. If, on the other hand, you either don't
need those features/are able to program around them without much
difficult/without slowing the application down greatly, and don't want to take
the significant performance hit associated with the use of Postgres, then
MySQL is the way to go.


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



[PHP3] Re: PostgreSQL vs Mysql comparison

From
Patrick Greenwell
Date:
On Sat, 2 Oct 1999, Scott Perkins wrote:

> You make a good point about available admin and other facilities etc.
>
> I am interested in some of the forum functionalities that seem to be "off the
> shelf" with Mysql but I have not done my homework yet with PostgreSQL.  There
> must me some good reasons why PostgreSQL beat out
> Mysql for product of the year  or best database or most popular among
> NIX/NUX users or something recently.

Because the Postgres mailing list was informed of the vote and apparently
had more members that voted than the MySQL list did. To put awards based on web
polls in perspective, "Hank, the angry drunken dwarf" won the "most
beautiful person in show business" award on People Magazines website
earlier this year. In other words, I wouldn't take the voting or the award
for most popular database very seriously given the voting methodology...

> I am just trying to clearly differientiate the significant differences
> and decide which are important relative to specific projects.

Transaction support, transaction support, transaction support...

Don't forget sub-selects, views, row-level locking, stored procedures,
triggers, foreign keys, cascading updates/deletes, and probably a couple
other things that escape me at the moment.

MySQL is great for what it is: a small, exceedingly fast, easy to
administer, stable SQL-server for enviroments where the majority of access
consist of text reads (like a lot of web sites).

It is not as stellar if you are doing things that are best accomplished by real
transactions at the database level rather than being forced to program
around the lack of them (such as complex financial transactions,) nor is
it the best choice in enviroments where database views, stored
procedures/triggers are of significant necessity/benefit, in high
read/write concurrency enviroments, enviroments with a high percentage of
BLOB data(if anyone has seen any SQL implementation that truly handles
this well let me know,) or enviroments requiring realtime database
replication/redundancy, etc.

Postgres is certainly significantly slower than MySQL according to the
MySQL benchmarks(somewhat tellingly IMO the Postgres site doesn't have any
benchmarks of their own other than some feature comparisons with a couple
of different SQL servers that don't include MySQL.)

The use of Postgres doesn't fix the BLOBs or database replication/redundancy
issue. It may address high concurrency read/write issues to some extent
with row-level locking(as opposed to MySQL, which only supports
potentially much more lock contentious table-level locking.) What Postgres
certainly does have is a richer feature set. While this is at least
partially responsible for the apparent considerable slow down in Postgres
vs. MySQL, it can make it much easier and faster for database
designers/programmers to develop databases/applications or to do ports
from databases/applications  that use a fully(or almost fully) compliant
ANSI SQL/92 server(such as Oracle,) especially if they are accustomed to
the feature set that such servers offer.

The MySQL docs have a list of missing functionality at:
http://mysql.he.net/Manual_chapter/manual_Compatibility.html#Missing_functions
and discuss some work-arounds, planned additions, etc. To summarize:

    o Sub-selects will probably be available in 3.24.0.

    o Atomic operations, a component of true transaction support
      where a series of operations are performed as a single operation
      will be supported however without rollback capabilities.

    o Stored procedures are planned, but without trigger support.

    o Foreign Key support is planned

    o No mention of cascading updates/deletes I could find, although this
      could be part of real foreign key support(?) and the corresponding
      referential integrity it hopefully provides.

    o No mention of row-level locking

Sometimes, features you think you need, or are told you need aren't really
needed at all. In the cases where you really do need specific functionality, it
*usually* isn't too difficult to program around the lack of, or only
partial support for that functionality. That said, doing this will almost
certainly result in longer development cycles as you first have to
determine if you really need the feature in the first place, and how
badly. If you can't live without it, you must then find or create the
appropriate work-around, and probably adding code to the application to
make it do what the SQL server normally would. This could potentially
slow the application. This can also be a much more error-prone process. As
an example, it would be much easier, safer, and faster(development wise)
to use the native database functionality of a reasonably-written server to
achieve proper transactions support w/commit, rollback and contingency
actions for failures than it would be to write your own from scratch.

So the long and short of it is this: if you are *really* certain that you
need native support for specific features that MySQL doesn't offer
and/or know it would take considerably longer to develop your
application with appropriate work-arounds, and/or know that
work-arounds would slow the application significantly, then you are
probably better off with Postgres. If, on the other hand, you either don't
need those features/are able to program around them without much
difficult/without slowing the application down greatly, and don't want to take
the significant performance hit associated with the use of Postgres, then
MySQL is the way to go.


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Sat, 2 Oct 1999, Patrick Greenwell wrote:

> On Sat, 2 Oct 1999, Scott Perkins wrote:
>
> > You make a good point about available admin and other facilities etc.
> >
> > I am interested in some of the forum functionalities that seem to be "off the
> > shelf" with Mysql but I have not done my homework yet with PostgreSQL.  There
> > must me some good reasons why PostgreSQL beat out
> > Mysql for product of the year  or best database or most popular among
> > NIX/NUX users or something recently.
>
> Because the Postgres mailing list was informed of the vote and apparently
> had more members that voted than the MySQL list did. To put awards based on web
> polls in perspective, "Hank, the angry drunken dwarf" won the "most
> beautiful person in show business" award on People Magazines website
> earlier this year. In other words, I wouldn't take the voting or the award
> for most popular database very seriously given the voting methodology...

Wait...we'er talking about two different things here...if you are
referring to the "LinuxWorld Editors Choice Award" that we recently
won...that had nothing (or, at least as far as any of us knew) to do with
any WWW based poll that we are aware of...

The only WWW based poll that we are aware of was put on by LinuxDev.Net,
viewable at:

    http://linuxdev.net/cgi-bin/pollit/poll_it.cgi?load=lastpoll

Or, does LinuxWorld own LinuxDev.Net?

> Postgres is certainly significantly slower than MySQL according to the
> MySQL benchmarks

    Is that the benchmarks that are still based on v6.4.2? *raised
eyebrow*  We saw a significant performance increase when we moved from the
old 'table locking' to MVCC ...

    Also, please note also that those benchmarks were/are based on
what MySQL is capable of, not what the other SQL servers are capable of.
For instance, if I take an SQL query that has been "optimized for MySQL"
and run it verbatim under PostgreSQL, the difference is a 2sec result for
MySQL vs 19sec for PostgreSQL ... if I re-write that query for
PostgreSQL's featuers (namely, using subselects), i can get the query down
to 1sec *shrug*

    Would be interesting to see us (PostgreSQL) come up with
benchmarks against MySQL...we'd be able to put stuff like "couldn't
test under MySQL, since they don't support this SQL construct"...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Sat, 2 Oct 1999, Patrick Greenwell wrote:

> On Sat, 2 Oct 1999, Scott Perkins wrote:
>
> > You make a good point about available admin and other facilities etc.
> >
> > I am interested in some of the forum functionalities that seem to be "off the
> > shelf" with Mysql but I have not done my homework yet with PostgreSQL.  There
> > must me some good reasons why PostgreSQL beat out
> > Mysql for product of the year  or best database or most popular among
> > NIX/NUX users or something recently.
>
> Because the Postgres mailing list was informed of the vote and apparently
> had more members that voted than the MySQL list did. To put awards based on web
> polls in perspective, "Hank, the angry drunken dwarf" won the "most
> beautiful person in show business" award on People Magazines website
> earlier this year. In other words, I wouldn't take the voting or the award
> for most popular database very seriously given the voting methodology...

Wait...we'er talking about two different things here...if you are
referring to the "LinuxWorld Editors Choice Award" that we recently
won...that had nothing (or, at least as far as any of us knew) to do with
any WWW based poll that we are aware of...

The only WWW based poll that we are aware of was put on by LinuxDev.Net,
viewable at:

    http://linuxdev.net/cgi-bin/pollit/poll_it.cgi?load=lastpoll

Or, does LinuxWorld own LinuxDev.Net?

> Postgres is certainly significantly slower than MySQL according to the
> MySQL benchmarks

    Is that the benchmarks that are still based on v6.4.2? *raised
eyebrow*  We saw a significant performance increase when we moved from the
old 'table locking' to MVCC ...

    Also, please note also that those benchmarks were/are based on
what MySQL is capable of, not what the other SQL servers are capable of.
For instance, if I take an SQL query that has been "optimized for MySQL"
and run it verbatim under PostgreSQL, the difference is a 2sec result for
MySQL vs 19sec for PostgreSQL ... if I re-write that query for
PostgreSQL's featuers (namely, using subselects), i can get the query down
to 1sec *shrug*

    Would be interesting to see us (PostgreSQL) come up with
benchmarks against MySQL...we'd be able to put stuff like "couldn't
test under MySQL, since they don't support this SQL construct"...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Paul DuBois
Date:
At 5:02 PM -0500 10/3/99, The Hermit Hacker wrote:
>> Postgres is certainly significantly slower than MySQL according to the
>> MySQL benchmarks
>
>    Is that the benchmarks that are still based on v6.4.2? *raised
>eyebrow*  We saw a significant performance increase when we moved from the
>old 'table locking' to MVCC ...
>
>    Also, please note also that those benchmarks were/are based on
>what MySQL is capable of, not what the other SQL servers are capable of.
>For instance, if I take an SQL query that has been "optimized for MySQL"
>and run it verbatim under PostgreSQL, the difference is a 2sec result for
>MySQL vs 19sec for PostgreSQL ... if I re-write that query for
>PostgreSQL's featuers (namely, using subselects), i can get the query down
>to 1sec *shrug*
>
>    Would be interesting to see us (PostgreSQL) come up with
>benchmarks against MySQL...we'd be able to put stuff like "couldn't
>test under MySQL, since they don't support this SQL construct"...


Well ... why don't you do that?  While you're at it, if you can supply
more recent benchmarks for Postgres to the MySQL people, I doubt if they'd
refuse them.   The benchmark page is meant to be helpful, not misleading
or a propaganda tool for MySQL.

--
Paul DuBois, paul@snake.net

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Paul DuBois
Date:
At 5:02 PM -0500 10/3/99, The Hermit Hacker wrote:
>> Postgres is certainly significantly slower than MySQL according to the
>> MySQL benchmarks
>
>    Is that the benchmarks that are still based on v6.4.2? *raised
>eyebrow*  We saw a significant performance increase when we moved from the
>old 'table locking' to MVCC ...
>
>    Also, please note also that those benchmarks were/are based on
>what MySQL is capable of, not what the other SQL servers are capable of.
>For instance, if I take an SQL query that has been "optimized for MySQL"
>and run it verbatim under PostgreSQL, the difference is a 2sec result for
>MySQL vs 19sec for PostgreSQL ... if I re-write that query for
>PostgreSQL's featuers (namely, using subselects), i can get the query down
>to 1sec *shrug*
>
>    Would be interesting to see us (PostgreSQL) come up with
>benchmarks against MySQL...we'd be able to put stuff like "couldn't
>test under MySQL, since they don't support this SQL construct"...


Well ... why don't you do that?  While you're at it, if you can supply
more recent benchmarks for Postgres to the MySQL people, I doubt if they'd
refuse them.   The benchmark page is meant to be helpful, not misleading
or a propaganda tool for MySQL.

--
Paul DuBois, paul@snake.net

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Sun, 3 Oct 1999, Paul DuBois wrote:

> Well ... why don't you do that?  While you're at it, if you can supply
> more recent benchmarks for Postgres to the MySQL people, I doubt if they'd
> refuse them.   The benchmark page is meant to be helpful, not misleading
> or a propaganda tool for MySQL.

Actually, if you go through the PostgreSQL archives, there was *at least*
one major attempt at getting the MySQL benchmark page upgraded...the
thread involved the MySQL guys, and several of ours, and went on for a few
days...if I recall it all correctly...

...end result...no changes :(

The problem, as I see it, is how can you do a fair comparison when those
features we'd use to improve performance, subselects to name one, aren't
available in MySQL?

What I'd love to see is a common set of data to work from, and table
structures...then come up with, again, a common set of "results expected",
from the database.  Then, let the MySQL guys provide their query for the
results, and let the PostgreSQL guys come up with theirs, and compare
*that*.  Don't compare how to get the results the fastest through MySQL,
and then how the same query stands up under PostgreSQL...let us give them
how to get it fastest from PostgreSQL...

The problem also comes down to common hardware...

Anyone out there running a machine, and have the time, to do something
like this?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org



--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
randyboy
Date:
> Anyone out there running a machine, and have the time, to do something
> like this?

sure.  celeron 333, 96meg RAM, FreeBSD 3.2.

this past week i've spent an unhealthy number of hours churning through
the docs and mailing lists tryiing to figure out which one i should use
and i'm getting really frustrated.  i'd like to get this over with cuz i
need to pick one soon. i will need some help though since i know neither
db well.

i already have a stock FreeBSD port of mysql-3.22.22 installed.


thoughts?


r.


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Sun, 3 Oct 1999, Paul DuBois wrote:

> Well ... why don't you do that?  While you're at it, if you can supply
> more recent benchmarks for Postgres to the MySQL people, I doubt if they'd
> refuse them.   The benchmark page is meant to be helpful, not misleading
> or a propaganda tool for MySQL.

Actually, if you go through the PostgreSQL archives, there was *at least*
one major attempt at getting the MySQL benchmark page upgraded...the
thread involved the MySQL guys, and several of ours, and went on for a few
days...if I recall it all correctly...

...end result...no changes :(

The problem, as I see it, is how can you do a fair comparison when those
features we'd use to improve performance, subselects to name one, aren't
available in MySQL?

What I'd love to see is a common set of data to work from, and table
structures...then come up with, again, a common set of "results expected",
from the database.  Then, let the MySQL guys provide their query for the
results, and let the PostgreSQL guys come up with theirs, and compare
*that*.  Don't compare how to get the results the fastest through MySQL,
and then how the same query stands up under PostgreSQL...let us give them
how to get it fastest from PostgreSQL...

The problem also comes down to common hardware...

Anyone out there running a machine, and have the time, to do something
like this?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org



--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
randyboy
Date:
> Anyone out there running a machine, and have the time, to do something
> like this?

sure.  celeron 333, 96meg RAM, FreeBSD 3.2.

this past week i've spent an unhealthy number of hours churning through
the docs and mailing lists tryiing to figure out which one i should use
and i'm getting really frustrated.  i'd like to get this over with cuz i
need to pick one soon. i will need some help though since i know neither
db well.

i already have a stock FreeBSD port of mysql-3.22.22 installed.


thoughts?


r.


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
What do/are the MySQL folks using for test data?  maybe start off with
what they are using to time MySQL and then look at how PostgreSQL can do
it, maybe differently?

Is there somewhere where the MySQL benchmarks can be downloaded from,
including teh data?


On Sun, 3 Oct 1999, randyboy wrote:

> > Anyone out there running a machine, and have the time, to do something
> > like this?
>
> sure.  celeron 333, 96meg RAM, FreeBSD 3.2.
>
> this past week i've spent an unhealthy number of hours churning through
> the docs and mailing lists tryiing to figure out which one i should use
> and i'm getting really frustrated.  i'd like to get this over with cuz i
> need to pick one soon. i will need some help though since i know neither
> db well.
>
> i already have a stock FreeBSD port of mysql-3.22.22 installed.
>
>
> thoughts?
>
>
> r.
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
What do/are the MySQL folks using for test data?  maybe start off with
what they are using to time MySQL and then look at how PostgreSQL can do
it, maybe differently?

Is there somewhere where the MySQL benchmarks can be downloaded from,
including teh data?


On Sun, 3 Oct 1999, randyboy wrote:

> > Anyone out there running a machine, and have the time, to do something
> > like this?
>
> sure.  celeron 333, 96meg RAM, FreeBSD 3.2.
>
> this past week i've spent an unhealthy number of hours churning through
> the docs and mailing lists tryiing to figure out which one i should use
> and i'm getting really frustrated.  i'd like to get this over with cuz i
> need to pick one soon. i will need some help though since i know neither
> db well.
>
> i already have a stock FreeBSD port of mysql-3.22.22 installed.
>
>
> thoughts?
>
>
> r.
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
>
> What do/are the MySQL folks using for test data?  maybe start off with
> what they are using to time MySQL and then look at how PostgreSQL can do
> it, maybe differently?
>
> Is there somewhere where the MySQL benchmarks can be downloaded from,
> including teh data?
>

Let me address my recollection of that discussion.  The basic issue is
that MySQL is somewhat faster because they don't have any
transactions/rollback capability, no user-defined types, and are SQL
built on top of an ISAM-type file with the indexes inbedded in the file,
so to add an index requires rebuilding the table.

Second, the MySQL folks don't mention the features they _don't_ have,
except now they mention they don't have transactions.

The bottom line from my perspective is that the benchmark is really
intended not as a fair comparison, but to make MySQL look good.

This upset me, but the increasing growth of PostgreSQL and the awards we
have won make me suspect that people are smarter than we think, and have
figured out where MySQL is strongest, and where PostgreSQL is strongest.

[I see this is going to MySQL mailing lists, so this may start a
discussion all over again.]

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



[PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
>
> What do/are the MySQL folks using for test data?  maybe start off with
> what they are using to time MySQL and then look at how PostgreSQL can do
> it, maybe differently?
>
> Is there somewhere where the MySQL benchmarks can be downloaded from,
> including teh data?
>

Let me address my recollection of that discussion.  The basic issue is
that MySQL is somewhat faster because they don't have any
transactions/rollback capability, no user-defined types, and are SQL
built on top of an ISAM-type file with the indexes inbedded in the file,
so to add an index requires rebuilding the table.

Second, the MySQL folks don't mention the features they _don't_ have,
except now they mention they don't have transactions.

The bottom line from my perspective is that the benchmark is really
intended not as a fair comparison, but to make MySQL look good.

This upset me, but the increasing growth of PostgreSQL and the awards we
have won make me suspect that people are smarter than we think, and have
figured out where MySQL is strongest, and where PostgreSQL is strongest.

[I see this is going to MySQL mailing lists, so this may start a
discussion all over again.]

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



PostgreSQL vs Mysql comparison

From
Scott Perkins
Date:
fm Scott Perkins  Atlanta, GA

Guys I'd just like to say what a "luxury" problem it is to have the choice
between two mature and proven products.  In this world of Gatesville there is
obviously plenty of market share to go around and I don't view PostgreSQL and
Mysql as competitors but complementors.  Nevertheless, it is competitivism sp?
that is sometimes inspiring this discussion.  Let's recognize this as GOOD.

Having said that and I am still interested in learning even more about both.
I think a fair analogy is something like the world of auto racing.
Locally among linux devotees we have preliminarily concluded that Mysql is kind
of the Dragster King and PostgreSQL is sort of the
Lemans-Slalom-Winding-Roadrace-
King.  They each are winners. . . And you can't say which is better.

It's like "is a saw or a hammer better ?"  . .  FOR WHAT? you say . . Exactly!

No one can argue that there are clear cut environments where one will perform
better and that is most likely a reflection of early design objectives.

Clearly each are specialists, just like the cars.  Fortunately for us as
developers, something I have not seen mentioned because I don't think there is a
difference is that both camps are dynamically and aggressively moving forward
such that a big problem as we have seen is keeping up with the improvements.
Things could be worse - huh?

Lets focus on updating the relative comparison info so when we make the choice
our informed decisions are based accurately.

In this dialog, one of the most concise summaries of the two products
was located at :

http://www.toodarkpark.org/computers/dbs.html

Which admittedly contains a "dated comparison between msql, mysql, and
postgresql."

The format I like.  Let's point out the inaccuracies that may now exist and
correct them.

Surely we can find an impartial location to post the updated document if not in
the archives of these discussions.  At least at that point we can apply our own
individual degrees of significance to the agreed upon differences.

I have pasted the full text below relative to MYSQL AND POSTGRESQL
let's try to use this as a working document and update it.
Thanks, Scott



Low-Cost Unix Database Differences   - Author Unknown

MySQL:
-------
Pros:
* Extremely fast.
* Regular expression support.
* Quite a few datatypes available.
* Support staff very knowledgable and helpful.
* Supports identity/auto-increment columns, similar to sequences.
* C-based API very similar to Msql; aids in porting Msql apps to MySQL. A small
shell script is included that does the basic port ( via sed )
* CHAR/VARCHAR/TEXT columns handled canse insensetively.
* Low-cost, often free, for most users. Commercial support available.
* BLOB support stores the BLOBs in the table.
* Supports user-defined functions in C and derivatives.
* Multithreaded

CONS:

* No support for transactions ( begin transaction, rollback, commit ). One must
explicitly lock a table and unlock it when finished.

* No foreign key support.

* No trigger support.

* No subselects.

* No views.

* No sequence support. No, auto_increment columns are not sequences.

* Auto_increment columns will give the next available number upon insert,
possibly messing up what little referential integrity constraints your
application attempts to enforce.

* Only one automatically-updated column per table; one cannot have a timestamp
and auto_increment column in the same table and have both be updated.

* User-defined functions, even the most basic ones, must be in C and
derivatives.

* No on-line recovery; one must bring down the database server and run the
'isamchk' utility on their datafiles.

* Hacked-up SQL functions such as REPLACE INTO.

* Indexes must be created at table-creation time. To add a new index/key, the
table (and its data) must be backed up and reimported. Work around: create a new
table, insert into new_table select * from old_table, drop old_table, alter
table new_table rename old_table.

* Does not handle dates correctly. One can insert '1999-02-31 01:01:01' into a
datetime column, mysql accepts it.

* Does not handle date manipulation properly; select '1999-08-14 11:32:00' - 7
== '1992'





PostgreSQL:
-----------

Pros:

* Object-relational database.
* Free for all uses. Commercial support available.
* Transactions supported ( begin transaction, rollback, commit )
* Sequences supported.
* Triggers supported.
* Subselects supported.
* Unions supported
* Views supported.
* User-defined functions can be in C and derivatives, PL/TCL, PL/PgSQL, or SQL.
* Regular expressions supported.
* Foreign keys supported via an add-on module called "refint".
* Multiversion concurrency control ( MVCC ).
* Handles dates correctly; one CANNOT insert '1999-02-31 01:01:01' into a
datetime column.
* Handles date manipulation correctly through the use of timespan datatypes:
select DATE('1999-08-14'::date - '7 days'::timespan) == 1999-08-07
* Supports the use of querying on 'inherited' datatypes; one can query on only
the date portion of a datetime column.
* Supports online recovery via VACUUM.
* Supports the creation of indexes after table creation.
* Inheritance support; create table a ()... create table b inherits from a.
* Support for large objects/BLOBs.



Cons:

* Due to foreign key support not being fully 'inside' of PostgreSQL, it does not
support the REFERENCES keyword. One must manually create two triggers ( one on
the parent, one on the child ) to get foreign key support.

* A bit slower for INSERTS/UPDATES when compared to MySQL. Then again,
PostgreSQL has to check constraints and triggers and such prior to doing the
insert/update, which slows it down.

* On large tables, VACUUM can take a while to return.

* SQL syntax gets a little odd for certain columns, as you'll notice above:
 SELECT ... '1999-08-14'::date - '7 days'::timespan. When comparing
columns/values of different data types, one column must be 'translated' into
another column via this ( or similar ) syntax.

* On-line backups done via pg_dump don't seem to properly dump views; views come
out as a table, not as a query on 1+ tables.

* Basic installation does a sync after every insert/update, slowing down the
system in general. While this can be disabled via an argument, new users may not
realize this and blame poor performance on Postgres.

* Large-object ( BLOB ) support is space consuming. One must first pull the
large object out of the database and into the filesystem. This is, imho, really
no better than storing the path to the file in question; you'd skip the
retrieval from the database portion of the query and wouldn't have to unlink()
the exported file when finished.

* Single Threaded

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



[PHP3] PostgreSQL vs Mysql comparison

From
Scott Perkins
Date:
fm Scott Perkins  Atlanta, GA

Guys I'd just like to say what a "luxury" problem it is to have the choice
between two mature and proven products.  In this world of Gatesville there is
obviously plenty of market share to go around and I don't view PostgreSQL and
Mysql as competitors but complementors.  Nevertheless, it is competitivism sp?
that is sometimes inspiring this discussion.  Let's recognize this as GOOD.

Having said that and I am still interested in learning even more about both.
I think a fair analogy is something like the world of auto racing.
Locally among linux devotees we have preliminarily concluded that Mysql is kind
of the Dragster King and PostgreSQL is sort of the
Lemans-Slalom-Winding-Roadrace-
King.  They each are winners. . . And you can't say which is better.

It's like "is a saw or a hammer better ?"  . .  FOR WHAT? you say . . Exactly!

No one can argue that there are clear cut environments where one will perform
better and that is most likely a reflection of early design objectives.

Clearly each are specialists, just like the cars.  Fortunately for us as
developers, something I have not seen mentioned because I don't think there is a
difference is that both camps are dynamically and aggressively moving forward
such that a big problem as we have seen is keeping up with the improvements.
Things could be worse - huh?

Lets focus on updating the relative comparison info so when we make the choice
our informed decisions are based accurately.

In this dialog, one of the most concise summaries of the two products
was located at :

http://www.toodarkpark.org/computers/dbs.html

Which admittedly contains a "dated comparison between msql, mysql, and
postgresql."

The format I like.  Let's point out the inaccuracies that may now exist and
correct them.

Surely we can find an impartial location to post the updated document if not in
the archives of these discussions.  At least at that point we can apply our own
individual degrees of significance to the agreed upon differences.

I have pasted the full text below relative to MYSQL AND POSTGRESQL
let's try to use this as a working document and update it.
Thanks, Scott



Low-Cost Unix Database Differences   - Author Unknown

MySQL:
-------
Pros:
* Extremely fast.
* Regular expression support.
* Quite a few datatypes available.
* Support staff very knowledgable and helpful.
* Supports identity/auto-increment columns, similar to sequences.
* C-based API very similar to Msql; aids in porting Msql apps to MySQL. A small
shell script is included that does the basic port ( via sed )
* CHAR/VARCHAR/TEXT columns handled canse insensetively.
* Low-cost, often free, for most users. Commercial support available.
* BLOB support stores the BLOBs in the table.
* Supports user-defined functions in C and derivatives.
* Multithreaded

CONS:

* No support for transactions ( begin transaction, rollback, commit ). One must
explicitly lock a table and unlock it when finished.

* No foreign key support.

* No trigger support.

* No subselects.

* No views.

* No sequence support. No, auto_increment columns are not sequences.

* Auto_increment columns will give the next available number upon insert,
possibly messing up what little referential integrity constraints your
application attempts to enforce.

* Only one automatically-updated column per table; one cannot have a timestamp
and auto_increment column in the same table and have both be updated.

* User-defined functions, even the most basic ones, must be in C and
derivatives.

* No on-line recovery; one must bring down the database server and run the
'isamchk' utility on their datafiles.

* Hacked-up SQL functions such as REPLACE INTO.

* Indexes must be created at table-creation time. To add a new index/key, the
table (and its data) must be backed up and reimported. Work around: create a new
table, insert into new_table select * from old_table, drop old_table, alter
table new_table rename old_table.

* Does not handle dates correctly. One can insert '1999-02-31 01:01:01' into a
datetime column, mysql accepts it.

* Does not handle date manipulation properly; select '1999-08-14 11:32:00' - 7
== '1992'





PostgreSQL:
-----------

Pros:

* Object-relational database.
* Free for all uses. Commercial support available.
* Transactions supported ( begin transaction, rollback, commit )
* Sequences supported.
* Triggers supported.
* Subselects supported.
* Unions supported
* Views supported.
* User-defined functions can be in C and derivatives, PL/TCL, PL/PgSQL, or SQL.
* Regular expressions supported.
* Foreign keys supported via an add-on module called "refint".
* Multiversion concurrency control ( MVCC ).
* Handles dates correctly; one CANNOT insert '1999-02-31 01:01:01' into a
datetime column.
* Handles date manipulation correctly through the use of timespan datatypes:
select DATE('1999-08-14'::date - '7 days'::timespan) == 1999-08-07
* Supports the use of querying on 'inherited' datatypes; one can query on only
the date portion of a datetime column.
* Supports online recovery via VACUUM.
* Supports the creation of indexes after table creation.
* Inheritance support; create table a ()... create table b inherits from a.
* Support for large objects/BLOBs.



Cons:

* Due to foreign key support not being fully 'inside' of PostgreSQL, it does not
support the REFERENCES keyword. One must manually create two triggers ( one on
the parent, one on the child ) to get foreign key support.

* A bit slower for INSERTS/UPDATES when compared to MySQL. Then again,
PostgreSQL has to check constraints and triggers and such prior to doing the
insert/update, which slows it down.

* On large tables, VACUUM can take a while to return.

* SQL syntax gets a little odd for certain columns, as you'll notice above:
 SELECT ... '1999-08-14'::date - '7 days'::timespan. When comparing
columns/values of different data types, one column must be 'translated' into
another column via this ( or similar ) syntax.

* On-line backups done via pg_dump don't seem to properly dump views; views come
out as a table, not as a query on 1+ tables.

* Basic installation does a sync after every insert/update, slowing down the
system in general. While this can be disabled via an argument, new users may not
realize this and blame poor performance on Postgres.

* Large-object ( BLOB ) support is space consuming. One must first pull the
large object out of the database and into the filesystem. This is, imho, really
no better than storing the path to the file in question; you'd skip the
retrieval from the database portion of the query and wouldn't have to unlink()
the exported file when finished.

* Single Threaded

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: PostgreSQL vs Mysql comparison

From
Martin Ramsch
Date:
[I skipped the PHP3 list because there this discussion is even more
 off-topic than here ...]

On Sun 1999-10-03 22:30:44 -0400, Bruce Momjian wrote:
> Second, the MySQL folks don't mention the features they _don't_ have,
> except now they mention they don't have transactions.

Well, maybe nobody told you, but see chapter "5.3 Functionality
missing from MySQL" of the MySQL Reference Manual ...
<URL: http://www.mysql.com/Manual_chapter/manual_Compatibility.html#Missing_functions >

So your statement is plain wrong and I'd really appreciate you not to
make such general statements if you don't have a clue.

But in general, you never can mention every features that exists in
any of the existing DBMSs!  A reasonable compromise is to compare
one's feature set with a know base like a standard.  The MySQL Manual
compares against SQL92 and clearly says what's there and what's
missing.


What I like about the MySQL developers (and the MySQL Manual) is that
they--as far as I know them--never try to devalue other products but
just state what's there, what's not there, for what kind of
applications MySQL fits best, for which not, and how to work around
given limitaions, if possible.  In short:  they are objective.

So it would be nice, if try to be objective, too.


> The bottom line from my perspective is that the benchmark is really
> intended not as a fair comparison, but to make MySQL look good.

Hmm, I really wonder what makes you think so?
What is not fair about this comparison?

> This upset me, but the increasing growth of PostgreSQL and the
> awards we have won make me suspect that people are smarter than we
> think, and have figured out where MySQL is strongest, and where
> PostgreSQL is strongest.

Exactly.  This is, why MySQL is often seen as db backend for web
applications, because the mix of mainly selects with fewer inserts,
combined with simple processing only and the need for great speed
fits exactly MySQL's feature set.
For many other applications MySQL won't be an ideal choice, but
actually (believe it or not) this is exactly what the MySQL developers
say themself, too!  Just use the right tool for the right task.


> [I see this is going to MySQL mailing lists, so this may start a
> discussion all over again.]

Well, if everybody keeps to the facts, then this is good!

There are maybe quite a few people out there looking for 'the best'
DMBS for their application. and those will appreciate an objective
comparison that takes into account as many aspects as possible like:
 - feature set
 - speed
 - compatibility (to whatever standard makes sense)
 - stability
 - support

BTW, I'm just a DBMS user, no developer, and besides MySQL I'm using
Informix, Transbase, and Oracle, too ...

A DBMS is no religion, it's a tool to get your work done.

Regards,
  Martin
--
Martin Ramsch <m.ramsch@computer.org> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7

Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Mon, 4 Oct 1999, Martin Ramsch wrote:

> What I like about the MySQL developers (and the MySQL Manual) is that
> they--as far as I know them--never try to devalue other products but
> just state what's there, what's not there, for what kind of
> applications MySQL fits best, for which not, and how to work around
> given limitaions, if possible.  In short:  they are objective.

Oh ya, they are sooooo objective, just take a look at their benchmarks to
see how objective they are.

> > The bottom line from my perspective is that the benchmark is really
> > intended not as a fair comparison, but to make MySQL look good.
>
> Hmm, I really wonder what makes you think so?
> What is not fair about this comparison?

Well, let's start with the fact that, as far as I'm aware, the benchmark
uses the 'latest and greatest' version of MySQL (as it should), but
doesn't take the time to upgrade the other databases?  Last I checked,
they were still using PostgreSQL v6.4.2, which has been dead for almost a
year now?  By not updating that one thing, its easy to show how bad
PostgreSQL is in comparision...hell, *we* think PostgreSQL v6.4.2 is bad,
that's why we don't run it...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> [I skipped the PHP3 list because there this discussion is even more
>  off-topic than here ...]

Good.

First, let me mention that I was re-playing my recollection of the
discussion from the past.

> On Sun 1999-10-03 22:30:44 -0400, Bruce Momjian wrote:
> > Second, the MySQL folks don't mention the features they _don't_ have,
> > except now they mention they don't have transactions.
>
> Well, maybe nobody told you, but see chapter "5.3 Functionality
> missing from MySQL" of the MySQL Reference Manual ...
> <URL: http://www.mysql.com/Manual_chapter/manual_Compatibility.html#Missing_functions >

I am talking about the database comparison chart.  We said in the past,
"Hey, you don't mention a major feature that every DBMS should have, and
that is transaction/rollback, and they said, "We don't know how to test
for that."  Well, all it takes is "BEGIN WORK; DELETE; ROLLBACK" and see
if the deletion is there.  Not rocket science.

From that reply, I concluded, again in the past, that they didn't really
want to show anything objective.

It seemed to present itself as objective, and that bothered me because
it seemed so slanted to MySQL.

> So your statement is plain wrong and I'd really appreciate you not to
> make such general statements if you don't have a clue.

I have a clue, but I will admit I don't know a lot about what is going
on in MySQL land.

>
> But in general, you never can mention every features that exists in
> any of the existing DBMSs!  A reasonable compromise is to compare
> one's feature set with a know base like a standard.  The MySQL Manual
> compares against SQL92 and clearly says what's there and what's
> missing.

We were not talking about every function, but the comparison chart
seemed to consciously avoid mention of anything that would make MySQL
look bad.

That has been corrected quite a long time ago, I should say.

> What I like about the MySQL developers (and the MySQL Manual) is that
> they--as far as I know them--never try to devalue other products but
> just state what's there, what's not there, for what kind of
> applications MySQL fits best, for which not, and how to work around
> given limitations, if possible.  In short:  they are objective.
>
> So it would be nice, if try to be objective, too.

Let me mention something that is a was very glad to see.  The MySQL
manuals actually recommend PostreSQL for people who need transactions.
Now, that takes humility.  That is clearly a sign of MySQL objectivity.

> > The bottom line from my perspective is that the benchmark is really
> > intended not as a fair comparison, but to make MySQL look good.
>
> Hmm, I really wonder what makes you think so?
> What is not fair about this comparison?

Again, I don't know.  It was more the comparison chart than the
benchmark numbers that bothered me.

Basically, if you looked at the comparison chart, it made anyone wonder
why they would use anything but MySQL it made MySQL look so good.  But
people are use other database.


> > This upset me, but the increasing growth of PostgreSQL and the
> > awards we have won make me suspect that people are smarter than we
> > think, and have figured out where MySQL is strongest, and where
> > PostgreSQL is strongest.
>
> Exactly.  This is, why MySQL is often seen as db backend for web
> applications, because the mix of mainly selects with fewer inserts,
> combined with simple processing only and the need for great speed
> fits exactly MySQL's feature set.
> For many other applications MySQL won't be an ideal choice, but
> actually (believe it or not) this is exactly what the MySQL developers
> say themself, too!  Just use the right tool for the right task.

Agreed.  I have a FAQ item on our web page that talks about comparison
between ourselves and other databases at www.postgresql.org under Info
Centra/Documentation.  I would be interested to hear any comments about
it.  It does not mention any other database system, but generally talks
about our strengths and weaknesses.



--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> [I skipped the PHP3 list because there this discussion is even more
>  off-topic than here ...]
>
> On Sun 1999-10-03 22:30:44 -0400, Bruce Momjian wrote:
> > Second, the MySQL folks don't mention the features they _don't_ have,
> > except now they mention they don't have transactions.
>
> Well, maybe nobody told you, but see chapter "5.3 Functionality
> missing from MySQL" of the MySQL Reference Manual ...
> <URL: http://www.mysql.com/Manual_chapter/manual_Compatibility.html#Missing_functions >

Let me clarify.  I do mention MySQL specifically on the FAQ at:

    http://www.postgresql.org/docs/faq-english.html

and it says:

    In comparison to MySQL or leaner database systems, we are slower on
    inserts/updates because we have transaction overhead. Of course, MySQL
    doesn't have any of the features mentioned in the Features section
    above. We are built for flexibility and features, though we continue to
    improve performance through profiling and source code analysis.

I am glad to listen to any comments about this statement.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [GENERAL] PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> fm Scott Perkins  Atlanta, GA
>
> Guys I'd just like to say what a "luxury" problem it is to have the choice
> between two mature and proven products.  In this world of Gatesville there is
> obviously plenty of market share to go around and I don't view PostgreSQL and
> Mysql as competitors but complementors.  Nevertheless, it is competitivism sp?
> that is sometimes inspiring this discussion.  Let's recognize this as GOOD.
>
> Having said that and I am still interested in learning even more about both.
> I think a fair analogy is something like the world of auto racing.
> Locally among linux devotees we have preliminarily concluded that Mysql is kind
> of the Dragster King and PostgreSQL is sort of the
> Lemans-Slalom-Winding-Roadrace-
> King.  They each are winners. . . And you can't say which is better.
>

Yes, this is basically what I say in the FAQ item mentioning MySQL.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



[PHP3] Re: [GENERAL] PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> fm Scott Perkins  Atlanta, GA
>
> Guys I'd just like to say what a "luxury" problem it is to have the choice
> between two mature and proven products.  In this world of Gatesville there is
> obviously plenty of market share to go around and I don't view PostgreSQL and
> Mysql as competitors but complementors.  Nevertheless, it is competitivism sp?
> that is sometimes inspiring this discussion.  Let's recognize this as GOOD.
>
> Having said that and I am still interested in learning even more about both.
> I think a fair analogy is something like the world of auto racing.
> Locally among linux devotees we have preliminarily concluded that Mysql is kind
> of the Dragster King and PostgreSQL is sort of the
> Lemans-Slalom-Winding-Roadrace-
> King.  They each are winners. . . And you can't say which is better.
>

Yes, this is basically what I say in the FAQ item mentioning MySQL.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Aaron J. Seigo"
Date:
hi...

> What I'd love to see is a common set of data to work from, and table
> structures...then come up with, again, a common set of "results expected",
> from the database.  Then, let the MySQL guys provide their query for the
> results, and let the PostgreSQL guys come up with theirs, and compare
> *that*.  Don't compare how to get the results the fastest through MySQL,
> and then how the same query stands up under PostgreSQL...let us give them
> how to get it fastest from PostgreSQL...
>
> The problem also comes down to common hardware...
>
> Anyone out there running a machine, and have the time, to do something
> like this?

well, i have a single pentiumII 400 box with a RAID 5 array and 256 MB RAM that
often runs at 0 load during the weekends and i'm the sole admin to it. i'd be
willing to offer the computing resources for the task...

additionally, i have another single pentiumII 400 that is the devel system,
only 128 MB RAM, but i can put it to task whenever i want =) my employers would
probably prefer this, but i'm sure i could command both for the test(s).. in
fact, i'd probalby run the production server as a "real world environment"
(several processes running, etc) and the devel system as a "best case scenario"
(i.e. no other processes running except those absolutely necessary, no network
activity, blah blah blah)...

what i would suggest is this:

1) the MySQL and PostgreSQL people create a set of data that both can live
with. something in the 500MB range. (a happy medium between what i'm willing to
cope with file wise and what would constitute a decent enough test bed). it
should include tables with char/text data, tables with numerical data, tables
with floating point data, etc... and tables w/mixes. it should include tables
with primary keys that are referenced in other tables, to reflect subquery
speeds (does MySQL do subqueries yet? i haven't checked in a while). indicies
should also be agreed upon, so that we're comparing apples to apples.

2) a set of selects/inserts/deletes should be agreed upon. i.e. : select all
data from table X where field1 is less than or equal to Z. then the MySQL and
PostgreSQL hackers should be given a set amount of time to come up with queries
for each (this assumes they are the best ones to formulate these). perhaps
these queries can be put out on the mailing lists for general discussion?

3) i will load the data into each database and run the final selects and
present the findings.

this will leave us with a comparison of how the two databases stack up to each
other where the features are comparable. this means we won't be testing
transactions, operations that would require procedural languages, etc.

a word of procedural languages: although the operations would not REQUIRE them,
perhaps it should be allowed that, if the camp decides it would be more
appropriate (read: speedy) to do the operation using a procedural language then
this should be allowed to reflect the real world abilities of the systems.

this should put it to bed, imo.

also, since we're not in a death-battle competing with each other (MySQL vs
PostgreSQL) as occurs in commercial software worlds, perhaps we should also get
together and put out a COOPERATIVE effort to catalog what each database can/can
not do and how it stacks up to the commercial databases.

this is open source, remember? its about choice. its about the right tool for
the right job. its about a FUDless environment where we get the tools to do
what we NEED to do.

in any case. my offer stands. i'll even volunteer to coordinate the effort
(though i'm not against not coordinating it either =), although i would not be
able to devote the time to building the data set / selects, etc... in fact, if
i were to stand in as "coordinator" then it would probably be best that i
didn't, to keep the tester's hands "clean" as it were.

as coordinator, i'd be willing to collect the final parameters, communicate
with representatives from each side (probably the developers?) and post the
results (after running them, of course :-)

as a final thought, it might be interesting to see how responsive each database
is via PHP scripts. though that would probably be a future test.

ok, that's probably way long enough.. haha.. happy hacking...

--
Aaron J. Seigo
Sys Admin

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



[PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Aaron J. Seigo"
Date:
hi...

> What I'd love to see is a common set of data to work from, and table
> structures...then come up with, again, a common set of "results expected",
> from the database.  Then, let the MySQL guys provide their query for the
> results, and let the PostgreSQL guys come up with theirs, and compare
> *that*.  Don't compare how to get the results the fastest through MySQL,
> and then how the same query stands up under PostgreSQL...let us give them
> how to get it fastest from PostgreSQL...
>
> The problem also comes down to common hardware...
>
> Anyone out there running a machine, and have the time, to do something
> like this?

well, i have a single pentiumII 400 box with a RAID 5 array and 256 MB RAM that
often runs at 0 load during the weekends and i'm the sole admin to it. i'd be
willing to offer the computing resources for the task...

additionally, i have another single pentiumII 400 that is the devel system,
only 128 MB RAM, but i can put it to task whenever i want =) my employers would
probably prefer this, but i'm sure i could command both for the test(s).. in
fact, i'd probalby run the production server as a "real world environment"
(several processes running, etc) and the devel system as a "best case scenario"
(i.e. no other processes running except those absolutely necessary, no network
activity, blah blah blah)...

what i would suggest is this:

1) the MySQL and PostgreSQL people create a set of data that both can live
with. something in the 500MB range. (a happy medium between what i'm willing to
cope with file wise and what would constitute a decent enough test bed). it
should include tables with char/text data, tables with numerical data, tables
with floating point data, etc... and tables w/mixes. it should include tables
with primary keys that are referenced in other tables, to reflect subquery
speeds (does MySQL do subqueries yet? i haven't checked in a while). indicies
should also be agreed upon, so that we're comparing apples to apples.

2) a set of selects/inserts/deletes should be agreed upon. i.e. : select all
data from table X where field1 is less than or equal to Z. then the MySQL and
PostgreSQL hackers should be given a set amount of time to come up with queries
for each (this assumes they are the best ones to formulate these). perhaps
these queries can be put out on the mailing lists for general discussion?

3) i will load the data into each database and run the final selects and
present the findings.

this will leave us with a comparison of how the two databases stack up to each
other where the features are comparable. this means we won't be testing
transactions, operations that would require procedural languages, etc.

a word of procedural languages: although the operations would not REQUIRE them,
perhaps it should be allowed that, if the camp decides it would be more
appropriate (read: speedy) to do the operation using a procedural language then
this should be allowed to reflect the real world abilities of the systems.

this should put it to bed, imo.

also, since we're not in a death-battle competing with each other (MySQL vs
PostgreSQL) as occurs in commercial software worlds, perhaps we should also get
together and put out a COOPERATIVE effort to catalog what each database can/can
not do and how it stacks up to the commercial databases.

this is open source, remember? its about choice. its about the right tool for
the right job. its about a FUDless environment where we get the tools to do
what we NEED to do.

in any case. my offer stands. i'll even volunteer to coordinate the effort
(though i'm not against not coordinating it either =), although i would not be
able to devote the time to building the data set / selects, etc... in fact, if
i were to stand in as "coordinator" then it would probably be best that i
didn't, to keep the tester's hands "clean" as it were.

as coordinator, i'd be willing to collect the final parameters, communicate
with representatives from each side (probably the developers?) and post the
results (after running them, of course :-)

as a final thought, it might be interesting to see how responsive each database
is via PHP scripts. though that would probably be a future test.

ok, that's probably way long enough.. haha.. happy hacking...

--
Aaron J. Seigo
Sys Admin

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: PostgreSQL vs Mysql comparison

From
Tonu Samuel
Date:
The Hermit Hacker wrote:

> Oh ya, they are sooooo objective, just take a look at their benchmarks to
> see how objective they are.

If you think that these benchmarks are not good enough, you can anytime
alter them and put new bechmarks on the web. Also you can suggest tips
to increase speed of any of any of the benchmark (including Postgres or
MySQL of course). What we have on the web, is best we can do. Of course
some time offset applies.

> they were still using PostgreSQL v6.4.2, which has been dead for almost a
> year now?  By not updating that one thing, its easy to show how bad
> PostgreSQL is in comparision...hell, *we* think PostgreSQL v6.4.2 is bad,
> that's why we don't run it...

What version is better? Bad thing is that we don't know much about it.
If you interested in setting truth, then please help make these
benchmarks more objective. Download all necessary stuff with MySQL
distribution, investigate these benchmarks and make your suggestions. Do
this in public or private depending how do you prefer. Also please run
these benchmarks in your site and if you have some good results, please
post URL for new benchmarks. If you have any questions about MySQL, feel
free to ask.


--
+----------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___                           |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Tõnu Samuel           |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     tonu@mysql.com        |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Tallinn, Estonia      |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                       |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                      |
|             /*/             \*\                Developers Team |
+----------------------------------------------------------------+

Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Luuk de Boer"
Date:
On 3 Oct 99, at 21:31, The Hermit Hacker wrote:

>
> What do/are the MySQL folks using for test data?  maybe start off with
> what they are using to time MySQL and then look at how PostgreSQL can do
> it, maybe differently?
>
> Is there somewhere where the MySQL benchmarks can be downloaded from,
> including teh data?
>

The latest benchmark run was done on Postgres 5.1 (place last
week) but I saw that 6.5.2 was already released so hopefully I will
find some time to run the benchmark this week on 6.5.2. The full
benchmark can be found in the source distribution of MySQL.If you
would like to test it you can downloaded the newest version of
mysql (3.23.4) and you will find the benchmark in the sql-bench dir
of it.

maybe we can share the information about how to tune postgresql.
I have looked in the manuals of postgresql and tried to do my best
to get the best performance out of it.
the latest benchmark are done on a cel. 463 mhz with 256 mb ram
and EIDE hd for the program and SCSI-2 for data set.

The test data is also included in the source distribution (partly) the
rest is generated by the benchmark program.

Maybe a year ago I believe we discussed almost the same thing
only then it went about the crash-me test (also included in the
source distribution -> sql-bench).

Greetz...

Luuk

Re: PostgreSQL vs Mysql comparison

From
"Luuk de Boer"
Date:
On 3 Oct 99, at 22:55, Scott Perkins wrote:

> fm Scott Perkins  Atlanta, GA
>
> Guys I'd just like to say what a "luxury" problem it is to have the choice
> between two mature and proven products.  In this world of Gatesville there is
> obviously plenty of market share to go around and I don't view PostgreSQL and
> Mysql as competitors but complementors.  Nevertheless, it is competitivism sp?
> that is sometimes inspiring this discussion.  Let's recognize this as GOOD.
>
> Having said that and I am still interested in learning even more about both.
> I think a fair analogy is something like the world of auto racing.
> Locally among linux devotees we have preliminarily concluded that Mysql is kind
> of the Dragster King and PostgreSQL is sort of the
> Lemans-Slalom-Winding-Roadrace-
> King.  They each are winners. . . And you can't say which is better.
>
> It's like "is a saw or a hammer better ?"  . .  FOR WHAT? you say . . Exactly!
>
> No one can argue that there are clear cut environments where one will perform
> better and that is most likely a reflection of early design objectives.
>
> Clearly each are specialists, just like the cars.  Fortunately for us as
> developers, something I have not seen mentioned because I don't think there is a
> difference is that both camps are dynamically and aggressively moving forward
> such that a big problem as we have seen is keeping up with the improvements.
> Things could be worse - huh?
>
> Lets focus on updating the relative comparison info so when we make the choice
> our informed decisions are based accurately.
>
> In this dialog, one of the most concise summaries of the two products
> was located at :
>
> http://www.toodarkpark.org/computers/dbs.html
>
> Which admittedly contains a "dated comparison between msql, mysql, and
> postgresql."
>
> The format I like.  Let's point out the inaccuracies that may now exist and
> correct them.
>
> Surely we can find an impartial location to post the updated document if not in
> the archives of these discussions.  At least at that point we can apply our own
> individual degrees of significance to the agreed upon differences.
>
> I have pasted the full text below relative to MYSQL AND POSTGRESQL
> let's try to use this as a working document and update it.

allmost everything is already in the crash-me test which will show
you what mysql can do and what postgres can do along other db's.
One year ago I believe or maybe longer ago I asked the hackers
mailing list of postgres to help us extending the crash-me test.
MySQL is testing a new version with it to see if everything is still
working like it should be. The crash-me test perl script can easily
extended so it won't be that hard to add some extra tests for some
specific things in postgres.

a summary will then be easily written when you have a good and
complete comparison chart of what every db can do or can't.

Greetz...

Luuk

Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Luuk de Boer"
Date:
On 3 Oct 99, at 19:56, The Hermit Hacker wrote:

> On Sun, 3 Oct 1999, Paul DuBois wrote:
>
> > Well ... why don't you do that?  While you're at it, if you can supply
> > more recent benchmarks for Postgres to the MySQL people, I doubt if they'd
> > refuse them.   The benchmark page is meant to be helpful, not misleading
> > or a propaganda tool for MySQL.
>
> Actually, if you go through the PostgreSQL archives, there was *at least*
> one major attempt at getting the MySQL benchmark page upgraded...the
> thread involved the MySQL guys, and several of ours, and went on for a few
> days...if I recall it all correctly...
>
> ...end result...no changes :(

Yep that's true but I can't remember that we would update the
benchmark page. I thought we asked if you could help us with
updating the crash-me script (the discussion I believe was about
atomic updates). But lets don't start that discussion again. If you
will look now at the benchmark page you will see it will start with a
benchmark run between mysql and postgres (pg). It's done on
6.5.1 (saying 6.5 because it's from the file VERSION and I don't
know another way to get the version of pg) But I will run today the
benchmark on 6.5.2.
>
> The problem, as I see it, is how can you do a fair comparison when those
> features we'd use to improve performance, subselects to name one, aren't
> available in MySQL?
>
> What I'd love to see is a common set of data to work from, and table
> structures...then come up with, again, a common set of "results expected",
> from the database.  Then, let the MySQL guys provide their query for the
> results, and let the PostgreSQL guys come up with theirs, and compare
> *that*.  Don't compare how to get the results the fastest through MySQL,
> and then how the same query stands up under PostgreSQL...let us give them
> how to get it fastest from PostgreSQL...
>
> The problem also comes down to common hardware...
>
> Anyone out there running a machine, and have the time, to do something
> like this?

one year ago I copied the tpc d benchmark test and wrote it in perl.
I tested some things with it but because of the copyrights we can't
use it but maybe it's a good starting point to start developing a
benchmark for us. tpc benchmarks officially can't be done with
other queries then they have defined but we could keep that
restriction away. Allmost all queries are done in subselects / views
so there are a lot of things which can be tuned. For MySQL it will
be done in two or three queries but if the result is the same I think
it won't be a problem. For postgresql it will be possible I think to do
the querie at ones.
The easiest way I think will be to write the benchmark in perl with
the DBI / DBD module so it can be ported easily to maybe other
db's (like msql / solid / oracle / informix / sybase etc ...).
Eventually it could be added to the benchmark of MySQL so the
benchmark will be extended with some subselects and view tests.
By the way the wisconsin benchmark test is from the postgres
distribution only rewritten in perl with DBI/DBD module.


PS. maybe someone can forward this email to the postgres
mailinglist because I am not a member to the list.

Greetz....

Luuk

Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Paul DuBois
Date:
At 10:30 PM -0400 10/3/99, Bruce Momjian wrote:
>>
>>  What do/are the MySQL folks using for test data?  maybe start off with
>>  what they are using to time MySQL and then look at how PostgreSQL can do
>>  it, maybe differently?
>>
>>  Is there somewhere where the MySQL benchmarks can be downloaded from,
>>  including teh data?
>>
>
>Let me address my recollection of that discussion.  The basic issue is
>that MySQL is somewhat faster because they don't have any
>transactions/rollback capability, no user-defined types, and are SQL
>built on top of an ISAM-type file with the indexes inbedded in the file,
>so to add an index requires rebuilding the table.
>
>Second, the MySQL folks don't mention the features they _don't_ have,
>except now they mention they don't have transactions.

Uh ... what?  Chapter 5 of the MySQL manual goes into the missing
functionality in some detail.  I have never gotten the impression that
they're trying to hide what isn't present.

--
Paul DuBois, paul@snake.net

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



[PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Paul DuBois
Date:
At 10:30 PM -0400 10/3/99, Bruce Momjian wrote:
>>
>>  What do/are the MySQL folks using for test data?  maybe start off with
>>  what they are using to time MySQL and then look at how PostgreSQL can do
>>  it, maybe differently?
>>
>>  Is there somewhere where the MySQL benchmarks can be downloaded from,
>>  including teh data?
>>
>
>Let me address my recollection of that discussion.  The basic issue is
>that MySQL is somewhat faster because they don't have any
>transactions/rollback capability, no user-defined types, and are SQL
>built on top of an ISAM-type file with the indexes inbedded in the file,
>so to add an index requires rebuilding the table.
>
>Second, the MySQL folks don't mention the features they _don't_ have,
>except now they mention they don't have transactions.

Uh ... what?  Chapter 5 of the MySQL manual goes into the missing
functionality in some detail.  I have never gotten the impression that
they're trying to hide what isn't present.

--
Paul DuBois, paul@snake.net

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> >Second, the MySQL folks don't mention the features they _don't_ have,
> >except now they mention they don't have transactions.
>
> Uh ... what?  Chapter 5 of the MySQL manual goes into the missing
> functionality in some detail.  I have never gotten the impression that
> they're trying to hide what isn't present.

Again, it was only the comparison chart that I was referring to.  As I
stated in a later posting, they actually recommend PostgreSQL for people
who want transactions, and that is a very gracious gesture on MySQL's
part.

The point I was trying to make was that the chart was promoted as being
an unbiased comparison, and I didn't think it was unbiased.  In fact,
they call it crashme, and in fact seem to avoid tests that crash MySQL,
while leaving in things that crash PostgreSQL.  Makes us look bad.

However, this is an old recollection, and I see on the current page that
this is no longer the case.  The current page looks much better, though
somehow you show PostgreSQL doesn't have HAVING or support -- comments.
However, I realize such a test is a major project, and you are not going
to get everything right.

Still, such comparisons are by nature biased.  If you have ten features,
and we have ten features, and we only have two features in common, a
biased comparison is going to show you has having ten features, and us
only having two features, with no mention of the eight features we have
that you don't have.

I actually find such charts to be fairly useless in most cases when
comparing cars, radios, etc.  I prefer a short paragraph summarizing the
major differences in the radios.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



[PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> >Second, the MySQL folks don't mention the features they _don't_ have,
> >except now they mention they don't have transactions.
>
> Uh ... what?  Chapter 5 of the MySQL manual goes into the missing
> functionality in some detail.  I have never gotten the impression that
> they're trying to hide what isn't present.

Again, it was only the comparison chart that I was referring to.  As I
stated in a later posting, they actually recommend PostgreSQL for people
who want transactions, and that is a very gracious gesture on MySQL's
part.

The point I was trying to make was that the chart was promoted as being
an unbiased comparison, and I didn't think it was unbiased.  In fact,
they call it crashme, and in fact seem to avoid tests that crash MySQL,
while leaving in things that crash PostgreSQL.  Makes us look bad.

However, this is an old recollection, and I see on the current page that
this is no longer the case.  The current page looks much better, though
somehow you show PostgreSQL doesn't have HAVING or support -- comments.
However, I realize such a test is a major project, and you are not going
to get everything right.

Still, such comparisons are by nature biased.  If you have ten features,
and we have ten features, and we only have two features in common, a
biased comparison is going to show you has having ten features, and us
only having two features, with no mention of the eight features we have
that you don't have.

I actually find such charts to be fairly useless in most cases when
comparing cars, radios, etc.  I prefer a short paragraph summarizing the
major differences in the radios.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Duncan Kinder"
Date:
Two problems with Postgres:

1) You have to jump through hoops to post bug reports.

2) Regression tests.  You have regression tests that purport to tell whether
Postgres works or not, but then there is language which states that a
"failure" may not actually be a "failure."  This is very difficult to figure
out - and hardly clear to say the least.


Regards,

Duncan C. Kinder
dckinder@mountain.net



----- Original Message -----
From: Bruce Momjian <maillist@candle.pha.pa.us>
To: Paul DuBois <paul@snake.net>
Cc: The Hermit Hacker <scrappy@hub.org>; randyboy
<randyboy@whispers.blackmist.org>; Patrick Greenwell
<patrick@stealthgeeks.net>; Scott Perkins <2scott@bellsouth.net>;
<mcgarry@tig.com.au>; MySQL mailing list <mysql@lists.mysql.com>;
postgresql-general <pgsql-general@postgreSQL.org>; <php3@lists.php.net>
Sent: Monday, October 04, 1999 8:11 AM
Subject: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison


> > >Second, the MySQL folks don't mention the features they _don't_ have,
> > >except now they mention they don't have transactions.
> >
> > Uh ... what?  Chapter 5 of the MySQL manual goes into the missing
> > functionality in some detail.  I have never gotten the impression that
> > they're trying to hide what isn't present.
>
> Again, it was only the comparison chart that I was referring to.  As I
> stated in a later posting, they actually recommend PostgreSQL for people
> who want transactions, and that is a very gracious gesture on MySQL's
> part.
>
> The point I was trying to make was that the chart was promoted as being
> an unbiased comparison, and I didn't think it was unbiased.  In fact,
> they call it crashme, and in fact seem to avoid tests that crash MySQL,
> while leaving in things that crash PostgreSQL.  Makes us look bad.
>
> However, this is an old recollection, and I see on the current page that
> this is no longer the case.  The current page looks much better, though
> somehow you show PostgreSQL doesn't have HAVING or support -- comments.
> However, I realize such a test is a major project, and you are not going
> to get everything right.
>
> Still, such comparisons are by nature biased.  If you have ten features,
> and we have ten features, and we only have two features in common, a
> biased comparison is going to show you has having ten features, and us
> only having two features, with no mention of the eight features we have
> that you don't have.
>
> I actually find such charts to be fairly useless in most cases when
> comparing cars, radios, etc.  I prefer a short paragraph summarizing the
> major differences in the radios.
>
> --
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
> --
> PHP 3 Mailing List <http://www.php.net/>
> To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
> To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
> To search the mailing list archive, go to:
http://www.php.net/mailsearch.php3
> To contact the list administrators, e-mail: php-list-admin@lists.php.net
>
>


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Duncan Kinder"
Date:
Two problems with Postgres:

1) You have to jump through hoops to post bug reports.

2) Regression tests.  You have regression tests that purport to tell whether
Postgres works or not, but then there is language which states that a
"failure" may not actually be a "failure."  This is very difficult to figure
out - and hardly clear to say the least.


Regards,

Duncan C. Kinder
dckinder@mountain.net



----- Original Message -----
From: Bruce Momjian <maillist@candle.pha.pa.us>
To: Paul DuBois <paul@snake.net>
Cc: The Hermit Hacker <scrappy@hub.org>; randyboy
<randyboy@whispers.blackmist.org>; Patrick Greenwell
<patrick@stealthgeeks.net>; Scott Perkins <2scott@bellsouth.net>;
<mcgarry@tig.com.au>; MySQL mailing list <mysql@lists.mysql.com>;
postgresql-general <pgsql-general@postgreSQL.org>; <php3@lists.php.net>
Sent: Monday, October 04, 1999 8:11 AM
Subject: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison


> > >Second, the MySQL folks don't mention the features they _don't_ have,
> > >except now they mention they don't have transactions.
> >
> > Uh ... what?  Chapter 5 of the MySQL manual goes into the missing
> > functionality in some detail.  I have never gotten the impression that
> > they're trying to hide what isn't present.
>
> Again, it was only the comparison chart that I was referring to.  As I
> stated in a later posting, they actually recommend PostgreSQL for people
> who want transactions, and that is a very gracious gesture on MySQL's
> part.
>
> The point I was trying to make was that the chart was promoted as being
> an unbiased comparison, and I didn't think it was unbiased.  In fact,
> they call it crashme, and in fact seem to avoid tests that crash MySQL,
> while leaving in things that crash PostgreSQL.  Makes us look bad.
>
> However, this is an old recollection, and I see on the current page that
> this is no longer the case.  The current page looks much better, though
> somehow you show PostgreSQL doesn't have HAVING or support -- comments.
> However, I realize such a test is a major project, and you are not going
> to get everything right.
>
> Still, such comparisons are by nature biased.  If you have ten features,
> and we have ten features, and we only have two features in common, a
> biased comparison is going to show you has having ten features, and us
> only having two features, with no mention of the eight features we have
> that you don't have.
>
> I actually find such charts to be fairly useless in most cases when
> comparing cars, radios, etc.  I prefer a short paragraph summarizing the
> major differences in the radios.
>
> --
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@candle.pha.pa.us            |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
> --
> PHP 3 Mailing List <http://www.php.net/>
> To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
> To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
> To search the mailing list archive, go to:
http://www.php.net/mailsearch.php3
> To contact the list administrators, e-mail: php-list-admin@lists.php.net
>
>


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Mon, 4 Oct 1999, Duncan Kinder wrote:

> Two problems with Postgres:
>
> 1) You have to jump through hoops to post bug reports.

Huh?  Just send an email to pgsql-bugs@postgresql.org ... its monitored by
all the developers...

> 2) Regression tests.  You have regression tests that purport to tell
> whether Postgres works or not, but then there is language which states
> that a "failure" may not actually be a "failure."  This is very
> difficult to figure out - and hardly clear to say the least.

Various operating systems have different text in their 'strerror()'
messages ...  we have stuff in the regression tests to compensate for
that, and there has recently been work done on reducing, if not,
eliminating those discrepencies...

Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Mon, 4 Oct 1999, Duncan Kinder wrote:

> Two problems with Postgres:
>
> 1) You have to jump through hoops to post bug reports.

Huh?  Just send an email to pgsql-bugs@postgresql.org ... its monitored by
all the developers...

> 2) Regression tests.  You have regression tests that purport to tell
> whether Postgres works or not, but then there is language which states
> that a "failure" may not actually be a "failure."  This is very
> difficult to figure out - and hardly clear to say the least.

Various operating systems have different text in their 'strerror()'
messages ...  we have stuff in the regression tests to compensate for
that, and there has recently been work done on reducing, if not,
eliminating those discrepencies...

Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Patrick Greenwell
Date:
On Sun, 3 Oct 1999, Paul DuBois wrote:

> >    Is that the benchmarks that are still based on v6.4.2? *raised
> >eyebrow*  We saw a significant performance increase when we moved from the
> >old 'table locking' to MVCC ...
> >
> >    Also, please note also that those benchmarks were/are based on
> >what MySQL is capable of, not what the other SQL servers are capable of.
> >For instance, if I take an SQL query that has been "optimized for MySQL"
> >and run it verbatim under PostgreSQL, the difference is a 2sec result for
> >MySQL vs 19sec for PostgreSQL ... if I re-write that query for
> >PostgreSQL's featuers (namely, using subselects), i can get the query down
> >to 1sec *shrug*
> >
> >    Would be interesting to see us (PostgreSQL) come up with
> >benchmarks against MySQL...we'd be able to put stuff like "couldn't
> >test under MySQL, since they don't support this SQL construct"...
>
>
> Well ... why don't you do that?  While you're at it, if you can supply
> more recent benchmarks for Postgres to the MySQL people, I doubt if they'd
> refuse them.   The benchmark page is meant to be helpful, not misleading
> or a propaganda tool for MySQL.

Amen. I am more interested in objective data than I am in database
religion. :-) If Postgres looks good on some benchmarks, let's see em.


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Patrick Greenwell
Date:
On Sun, 3 Oct 1999, Paul DuBois wrote:

> >    Is that the benchmarks that are still based on v6.4.2? *raised
> >eyebrow*  We saw a significant performance increase when we moved from the
> >old 'table locking' to MVCC ...
> >
> >    Also, please note also that those benchmarks were/are based on
> >what MySQL is capable of, not what the other SQL servers are capable of.
> >For instance, if I take an SQL query that has been "optimized for MySQL"
> >and run it verbatim under PostgreSQL, the difference is a 2sec result for
> >MySQL vs 19sec for PostgreSQL ... if I re-write that query for
> >PostgreSQL's featuers (namely, using subselects), i can get the query down
> >to 1sec *shrug*
> >
> >    Would be interesting to see us (PostgreSQL) come up with
> >benchmarks against MySQL...we'd be able to put stuff like "couldn't
> >test under MySQL, since they don't support this SQL construct"...
>
>
> Well ... why don't you do that?  While you're at it, if you can supply
> more recent benchmarks for Postgres to the MySQL people, I doubt if they'd
> refuse them.   The benchmark page is meant to be helpful, not misleading
> or a propaganda tool for MySQL.

Amen. I am more interested in objective data than I am in database
religion. :-) If Postgres looks good on some benchmarks, let's see em.


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Duncan Kinder"
Date:
>Huh?  Just send an email to pgsql-bugs@postgresql.org ... its monitored by
>all the developers...

Thanks, I just got bounced.  (Yes, I know how to subscribe.  Yes, it is a
pain.)

Regards,

Duncan C. Kinder
dckinder@mountain.net



----- Original Message -----
From: The Hermit Hacker <scrappy@hub.org>
To: Duncan Kinder <dckinder@mountain.net>
Cc: MySQL mailing list <mysql@lists.mysql.com>; postgresql-general
<pgsql-general@postgreSQL.org>; <php3@lists.php.net>
Sent: Monday, October 04, 1999 8:52 AM
Subject: Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql
comparison


> On Mon, 4 Oct 1999, Duncan Kinder wrote:
>
> > Two problems with Postgres:
> >
> > 1) You have to jump through hoops to post bug reports.
>
> Huh?  Just send an email to pgsql-bugs@postgresql.org ... its monitored by
> all the developers...
>
> > 2) Regression tests.  You have regression tests that purport to tell
> > whether Postgres works or not, but then there is language which states
> > that a "failure" may not actually be a "failure."  This is very
> > difficult to figure out - and hardly clear to say the least.
>
> Various operating systems have different text in their 'strerror()'
> messages ...  we have stuff in the regression tests to compensate for
> that, and there has recently been work done on reducing, if not,
> eliminating those discrepencies...
>
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary:
scrappy@{freebsd|postgresql}.org
>
>
> --
> PHP 3 Mailing List <http://www.php.net/>
> To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
> To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
> To search the mailing list archive, go to:
http://www.php.net/mailsearch.php3
> To contact the list administrators, e-mail: php-list-admin@lists.php.net
>
>


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Duncan Kinder"
Date:
>Huh?  Just send an email to pgsql-bugs@postgresql.org ... its monitored by
>all the developers...

Thanks, I just got bounced.  (Yes, I know how to subscribe.  Yes, it is a
pain.)

Regards,

Duncan C. Kinder
dckinder@mountain.net



----- Original Message -----
From: The Hermit Hacker <scrappy@hub.org>
To: Duncan Kinder <dckinder@mountain.net>
Cc: MySQL mailing list <mysql@lists.mysql.com>; postgresql-general
<pgsql-general@postgreSQL.org>; <php3@lists.php.net>
Sent: Monday, October 04, 1999 8:52 AM
Subject: Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql
comparison


> On Mon, 4 Oct 1999, Duncan Kinder wrote:
>
> > Two problems with Postgres:
> >
> > 1) You have to jump through hoops to post bug reports.
>
> Huh?  Just send an email to pgsql-bugs@postgresql.org ... its monitored by
> all the developers...
>
> > 2) Regression tests.  You have regression tests that purport to tell
> > whether Postgres works or not, but then there is language which states
> > that a "failure" may not actually be a "failure."  This is very
> > difficult to figure out - and hardly clear to say the least.
>
> Various operating systems have different text in their 'strerror()'
> messages ...  we have stuff in the regression tests to compensate for
> that, and there has recently been work done on reducing, if not,
> eliminating those discrepencies...
>
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary:
scrappy@{freebsd|postgresql}.org
>
>
> --
> PHP 3 Mailing List <http://www.php.net/>
> To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
> To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
> To search the mailing list archive, go to:
http://www.php.net/mailsearch.php3
> To contact the list administrators, e-mail: php-list-admin@lists.php.net
>
>


--
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe@lists.php.net
To subscribe to the digest, e-mail: php3-digest-subscribe@lists.php.net
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin@lists.php.net



Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
>>>>> "Patrick" == Patrick Greenwell <patrick@stealthgeeks.net> writes:

Patrick> On Sat, 2 Oct 1999, Scott Perkins wrote:
>> You make a good point about available admin and other facilities etc.
>>
>> I am interested in some of the forum functionalities that seem to be "off the
>> shelf" with Mysql but I have not done my homework yet with PostgreSQL.  There
>> must me some good reasons why PostgreSQL beat out
>> Mysql for product of the year  or best database or most popular among
>> NIX/NUX users or something recently.

<cut>


Patrick> Don't forget sub-selects, views, row-level locking, stored procedures,
Patrick> triggers, foreign keys, cascading updates/deletes, and probably a couple
Patrick> other things that escape me at the moment.

Patrick> MySQL is great for what it is: a small, exceedingly fast, easy to
Patrick> administer, stable SQL-server for enviroments where the majority of access
Patrick> consist of text reads (like a lot of web sites).

Patrick is right; MySQL still lacks some 'heavy' features, but he
forgot to mention that PostgreSQL lacks a lot of ANSI SQL and ODBC
features that MySQL supports.  MySQL has also some very useful (even
if they are nonstandard) features like LOAD DATA INFILE, REPLACE ,
CREATE TABLE from a SELECT, in memory tables...) that postgreSQL
doesn't support.

You can find a comparison that should include all major points at:

http://www.mysql.com/crash-me-choose.htmy

Regards,
Monty

Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
Hi!

<cut>

The>     Also, please note also that those benchmarks were/are based on
The> what MySQL is capable of, not what the other SQL servers are capable of.
The> For instance, if I take an SQL query that has been "optimized for MySQL"
The> and run it verbatim under PostgreSQL, the difference is a 2sec result for
The> MySQL vs 19sec for PostgreSQL ... if I re-write that query for
The> PostgreSQL's featuers (namely, using subselects), i can get the query down
The> to 1sec *shrug*

Just a note about the above!  The queries we run in the MySQL
benchmarks are NOT optimized for MySQL.  The whole idea is that we
try to test different SQL construct and give an relatively accurate
and fair timeing of these!

Not a single of the tested queries can be made faster with sub
selects.

Note also that most queries don't need sub selects and most that need
sub selects can be made as fast (and sometimes faster) by using two
queries instead!

I agree that we don't have any sub selects tests yet in the MySQL
benchmarks, but we plan to add such in the near future; We have tried
to get someone else writing this, but as no one seem to be interested
in making the MySQL benchmarks even better, this may have to wait
until we have sub queries in MySQL).  We have also tried to get
postgreSQL developers to add other tests to the MySQL benchmarks (which
are GPL), but we haven't found anyone that has been interested in
this!

The>     Would be interesting to see us (PostgreSQL) come up with
The> benchmarks against MySQL...we'd be able to put stuff like "couldn't
The> test under MySQL, since they don't support this SQL construct"...

For the moment the standard MySQL benchmarks already contains a lot of
stuff that we can't test on PostgreSQL, because they don't support
some very common constructs (like LEFT JOIN, HAVING, ALTER TABLE
change of many columns).  There is also a lot of hard limits in PostgreSQL
(like number of columns, query size...) that makes it much harder to
make a good benchmark when comparing MySQL and PostgreSQL.

A last thing that few postgreSQL users seams to release;  We have
spent about 10 times as much time to optimize the MySQL benchmarks for
postgreSQL than for MySQL, just to get some decent results for postgreSQL.

Regards,
Monty

Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:

Hi!

<cut>

The> What I'd love to see is a common set of data to work from, and table
The> structures...then come up with, again, a common set of "results expected",
The> from the database.  Then, let the MySQL guys provide their query for the
The> results, and let the PostgreSQL guys come up with theirs, and compare
The> *that*.  Don't compare how to get the results the fastest through MySQL,
The> and then how the same query stands up under PostgreSQL...let us give them
The> how to get it fastest from PostgreSQL...

I have posted many messages to Postgresql developers about getting
benchmark that shows where PosgreSQL is good, but I haven't found
anyone that is interested in spending time on this!  I have got the
definite feeling that the postgreSQL developers are not interested in
working on benchmarks!

Regards,
Monty

Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
Hi!

<cut>

Bruce> The point I was trying to make was that the chart was promoted as being
Bruce> an unbiased comparison, and I didn't think it was unbiased.  In fact,
Bruce> they call it crashme, and in fact seem to avoid tests that crash MySQL,
Bruce> while leaving in things that crash PostgreSQL.  Makes us look bad.

I think you have missed the whole idea of crash-me!

It uses standard SQL to test for functionality in a SQL server.  If
any database fails in these tests, we report it.  You are saying that
if postgreSQL has bugs we should not report it?

crash-me is not used to find bugs, but due to the nature of how
crash-me works to find limits, it will find bugs in bad code (in most
cases in code that doesn't check for overruns).  If this makes you
look bad, then you have deserved it!  (Sorry no offense)

We here at TCX also uses crash-me as a tool to test each MySQL
distribution to check for bugs.  This will of course make MySQL much
more safer.  That you don't do this one each postgreSQL versions is
your loss (crash-me is GPL, so I can't understand why you shouldn't do it).

Bruce> However, this is an old recollection, and I see on the current page that
Bruce> this is no longer the case.  The current page looks much better, though
Bruce> somehow you show PostgreSQL doesn't have HAVING or support -- comments.
Bruce> However, I realize such a test is a major project, and you are not going
Bruce> to get everything right.

Luuk, could you please check out why crash-me misses HAVING and
--comments.   (Normally this means that the database in question
doesn't follow ANSI SQL in regards to this).

Bruce> Still, such comparisons are by nature biased.  If you have ten features,
Bruce> and we have ten features, and we only have two features in common, a
Bruce> biased comparison is going to show you has having ten features, and us
Bruce> only having two features, with no mention of the eight features we have
Bruce> that you don't have.

Bruce> I actually find such charts to be fairly useless in most cases when
Bruce> comparing cars, radios, etc.  I prefer a short paragraph summarizing the
Bruce> major differences in the radios.

Note that a comparison, like crash-me, that compares how a database
compares to a standard can never be useless!

I agree that crash-me also tests database specific functions/types, but we
try to restrict these to types/functions that are of major use or are common
to many database.  This will help persons that are trying to port an
application between databases.

Regards,
Monty

PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
Hi!

Scott> I have pasted the full text below relative to MYSQL AND POSTGRESQL
Scott> let's try to use this as a working document and update it.
Scott> Thanks, Scott

Some comments:

Scott> Low-Cost Unix Database Differences   - Author Unknown

Scott> MySQL:
Scott> -------
Scott> Pros:
Scott> * Extremely fast.
Scott> * Regular expression support.
Scott> * Quite a few datatypes available.
Scott> * Support staff very knowledgable and helpful.
Scott> * Supports identity/auto-increment columns, similar to sequences.
Scott> * C-based API very similar to Msql; aids in porting Msql apps to MySQL. A small
Scott> shell script is included that does the basic port ( via sed )
Scott> * CHAR/VARCHAR/TEXT columns handled canse insensetively.
Scott> * Low-cost, often free, for most users. Commercial support available.
Scott> * BLOB support stores the BLOBs in the table.
Scott> * Supports user-defined functions in C and derivatives.
Scott> * Multithreaded

Scott> CONS:

Scott> * No support for transactions ( begin transaction, rollback, commit ). One must
Scott> explicitly lock a table and unlock it when finished.
Scott> * No foreign key support.
Scott> * No trigger support.
Scott> * No subselects.
Scott> * No views.
Scott> * No sequence support. No, auto_increment columns are not sequences.

Note that sequences is not defined by ANSI SQL;  AUTO_INCREMENT colums
are about as much standard than sequences.
Note that you can VERY easily emulate sequences in MySQL

Scott> * Auto_increment columns will give the next available number upon insert,
Scott> possibly messing up what little referential integrity constraints your
Scott> application attempts to enforce.

If you write your application properly, it's note a problem even if one
reuse numbers that is not in use.  In MySQL 3.23 you will also always get a
new auto_increment number on insert.

Scott> * Only one automatically-updated column per table; one cannot have a timestamp
Scott> and auto_increment column in the same table and have both be updated.

The above is not true. There has never been a problem with having a
timestamp and a auto_increment column in the same table.

Scott> * User-defined functions, even the most basic ones, must be in C and
Scott> derivatives.
Scott> * No on-line recovery; one must bring down the database server and run the
Scott> 'isamchk' utility on their datafiles.

OPTIMIZE table should be comparable to VACUM on postgreSQL.

Scott> * Hacked-up SQL functions such as REPLACE INTO.

Scott> * Indexes must be created at table-creation time. To add a new index/key, the
Scott> table (and its data) must be backed up and reimported. Work around: create a new
Scott> table, insert into new_table select * from old_table, drop old_table, alter
Scott> table new_table rename old_table.

The above was fixed 2 1/2 years ago.

Scott> * Does not handle dates correctly. One can insert '1999-02-31 01:01:01' into a
Scott> datetime column, mysql accepts it.

We regard this as a feature, not as a bug.  It's not the databases job
to validate data by default, only to store and retrieve it.

Note that MySQL 3.23 allows you to use 'fuzzy' dates: '1999-02-00'.
This is VERY useful when you store birth-days/death-days!

Scott> * Does not handle date manipulation properly; select '1999-08-14 11:32:00' - 7
Scott> == '1992'

This is because the above means:

select (convert to int) '1999-08-14 11:32:00' - 7

->

select 1999 - 7.

Because '1999-08-14 11:32:00' is not a date object, but a string.

You should instead use:

SELECT DATE_SUB('1999-08-14 11:32:00',INTERVAL 7 DAYS)

or even

SELECT '1999-08-14 11:32:00' - INTERVAL 7 DAYS

Regards,
Monty

Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
Hi!

The> Well, let's start with the fact that, as far as I'm aware, the benchmark
The> uses the 'latest and greatest' version of MySQL (as it should), but
The> doesn't take the time to upgrade the other databases?  Last I checked,
The> they were still using PostgreSQL v6.4.2, which has been dead for almost a
The> year now?  By not updating that one thing, its easy to show how bad
The> PostgreSQL is in comparision...hell, *we* think PostgreSQL v6.4.2 is bad,
The> that's why we don't run it...

Before posting to the MySQL mailing list, please check the MySQL
web pages that your information is up to date.

We updated the benchmarks to use PostgreSQL 6.5 a while ago.  It seams
that the biggest improvement (speed vice) they have made is to improve
the vacum times.  MySQL is still a lot faster for most things.

We did in the newest test-suite add some cases, that has been up on the
MySQL mailing list, where MySQL is not that good, just to check how
other databases can handle these kind of queries...

(It's a pity that postgreSQL doesn't handle LEFT JOIN...  I would
really like to compare our new left-join optimization...)

Regards,
Monty

Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
<cut>

Aaron> what i would suggest is this:

Aaron> 1) the MySQL and PostgreSQL people create a set of data that both can live
Aaron> with. something in the 500MB range. (a happy medium between what i'm willing to
Aaron> cope with file wise and what would constitute a decent enough test bed). it
Aaron> should include tables with char/text data, tables with numerical data, tables
Aaron> with floating point data, etc... and tables w/mixes. it should include tables
Aaron> with primary keys that are referenced in other tables, to reflect subquery
Aaron> speeds (does MySQL do subqueries yet? i haven't checked in a while). indicies
Aaron> should also be agreed upon, so that we're comparing apples to apples.

Note that it would be much better to write a benchmark that can be run
against any SQL server.  As the MySQL benchmarks are designed to do
exactly this, I would suggest that you use the MySQL benchmark suit as
a base for this!

You can already run the MySQL benchmarks with tables of 500M without
any problems by just supplying some startup switches.

I don't think the important things is just to optimize some specific
queries; It's much more important to test a lot of different types of
queries.

Aaron> 2) a set of selects/inserts/deletes should be agreed upon. i.e. : select all
Aaron> data from table X where field1 is less than or equal to Z. then the MySQL and
Aaron> PostgreSQL hackers should be given a set amount of time to come up with queries
Aaron> for each (this assumes they are the best ones to formulate these). perhaps
Aaron> these queries can be put out on the mailing lists for general discussion?

I think it's important to using queries that are common to many
applications.

For normal queries there isn't that much to optimize.  There is of
course also the option to add some test to 'solve some problem'.  In
this case one can use different methods to solve the query for
different databases;  Normally these kind of tests are more
interesting than useful as this isn't normally portable between
database servers.

Aaron> 3) i will load the data into each database and run the final selects and
Aaron> present the findings.

Aaron> this will leave us with a comparison of how the two databases stack up to each
Aaron> other where the features are comparable. this means we won't be testing
Aaron> transactions, operations that would require procedural languages, etc.

Aaron> a word of procedural languages: although the operations would not REQUIRE them,
Aaron> perhaps it should be allowed that, if the camp decides it would be more
Aaron> appropriate (read: speedy) to do the operation using a procedural language then
Aaron> this should be allowed to reflect the real world abilities of the systems.
Aaron> this should put it to bed, imo.

One could do this as in the MySQL benchmarks:  If one run the benchmarks
with the '--fast' switch, one is allowed to do 'anything' to make the
test run faster.

Aaron> also, since we're not in a death-battle competing with each other (MySQL vs
Aaron> PostgreSQL) as occurs in commercial software worlds, perhaps we should also get
Aaron> together and put out a COOPERATIVE effort to catalog what each database can/can
Aaron> not do and how it stacks up to the commercial databases.
Aaron> this is open source, remember? its about choice. its about the right tool for
Aaron> the right job. its about a FUDless environment where we get the tools to do
Aaron> what we NEED to do.

This is exactly the aim of the MySQL benchmarks.  (They are GPL)

Aaron> in any case. my offer stands. i'll even volunteer to coordinate the effort
Aaron> (though i'm not against not coordinating it either =), although i would not be
Aaron> able to devote the time to building the data set / selects, etc... in fact, if
Aaron> i were to stand in as "coordinator" then it would probably be best that i
Aaron> didn't, to keep the tester's hands "clean" as it were.

Aaron> as coordinator, i'd be willing to collect the final parameters, communicate
Aaron> with representatives from each side (probably the developers?) and post the
Aaron> results (after running them, of course :-)

In this case;  Can you take a look at the MySQL benchmark suite and
comment on this?

Aaron> as a final thought, it might be interesting to see how responsive each database
Aaron> is via PHP scripts. though that would probably be a future test.

The current tests are done with perl and DBI (just to keep the tests portable).

Aaron> ok, that's probably way long enough.. haha.. happy hacking...

Regards,
Monty


Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
>>>>> "Bruce" == Bruce Momjian <maillist@candle.pha.pa.us> writes:

>> [I skipped the PHP3 list because there this discussion is even more
>> off-topic than here ...]
>>
>> On Sun 1999-10-03 22:30:44 -0400, Bruce Momjian wrote:
>> > Second, the MySQL folks don't mention the features they _don't_ have,
>> > except now they mention they don't have transactions.
>>
>> Well, maybe nobody told you, but see chapter "5.3 Functionality
>> missing from MySQL" of the MySQL Reference Manual ...
>> <URL: http://www.mysql.com/Manual_chapter/manual_Compatibility.html#Missing_functions >

Bruce> Let me clarify.  I do mention MySQL specifically on the FAQ at:

Bruce>     http://www.postgresql.org/docs/faq-english.html

Bruce> and it says:

Bruce>     In comparison to MySQL or leaner database systems, we are slower on
Bruce>     inserts/updates because we have transaction overhead. Of course, MySQL
Bruce>     doesn't have any of the features mentioned in the Features section
Bruce>     above. We are built for flexibility and features, though we continue to
Bruce>     improve performance through profiling and source code analysis.

Bruce> I am glad to listen to any comments about this statement.

Hi!

It would be very nice you could add to the above the features that
MySQL has that postgreSQL is missing.  PostgreSQL does have some
features that MySQL lacks, but MySQL does support a lot of ODBC and
ANSI SQL things that postgreSQL doesn't support. We have also put a
lot of effort in MySQL to implement a lot of useful functions from a
lot of different SQL servers.  This is why MySQL 'shows off' on
crash-me;  If someone else has some useful functionality/syntax, we also try
to support it!

MySQL is not only faster on inserts/updates, but is also faster on a
lot of things that only concerns retrieval.  This is just because
MySQL do optimize a lot of things better than PostgreSQL.  To just
talk about transactions overhead is missing the whole point with
MySQL!

On the other hand, it's of course true that PostgreSQL does optimize
some things better than MySQL (for example select on different keys).
(This is shown in the newest MySQL benchmark results at
http://www.mysql.com/benchmark.html)

Regards,
Monty

Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
Hi!

>>>>> "Bruce" == Bruce Momjian <maillist@candle.pha.pa.us> writes:

<cut>

Bruce> I am talking about the database comparison chart.  We said in the past,
Bruce> "Hey, you don't mention a major feature that every DBMS should have, and
Bruce> that is transaction/rollback, and they said, "We don't know how to test
Bruce> for that."  Well, all it takes is "BEGIN WORK; DELETE; ROLLBACK" and see
Bruce> if the deletion is there.  Not rocket science.

It seams you are not up to date;  We added a test for transaction to
crash me more than a year ago.

>> From that reply, I concluded, again in the past, that they didn't really
Bruce> want to show anything objective.

Bruce> It seemed to present itself as objective, and that bothered me because
Bruce> it seemed so slanted to MySQL.

Could you describe why?  Is this just because MySQL follows ANSI SQL
and ODBC more than postgreSQL?

The whole idea of crash-me is to show which features a database
supports.  I can't understand how this could be 'slanted'?

We have also repeatedly stated that we accept patches for crash-me to
make it even better.  As we can't know everything about every
database, there is probably some things we have missed, but generally
crash-me should be VERY fair.

A note about this;  We have a lot of database vendors to help us with
making crash-me better, and we have got a lot of positive feedback!
From the postgreSQL developers we have however not got any help at
all!  All postgreSQL test have been done by Luke on his spare time.


>> So your statement is plain wrong and I'd really appreciate you not to
>> make such general statements if you don't have a clue.

Bruce> I have a clue, but I will admit I don't know a lot about what is going
Bruce> on in MySQL land.

>> But in general, you never can mention every features that exists in
>> any of the existing DBMSs!  A reasonable compromise is to compare
>> one's feature set with a know base like a standard.  The MySQL Manual
>> compares against SQL92 and clearly says what's there and what's
>> missing.

Bruce> We were not talking about every function, but the comparison chart
Bruce> seemed to consciously avoid mention of anything that would make MySQL
Bruce> look bad.

It would be nice if you could make a single example of this, instead
of just sayting that you have a clue.

Note that crash-me is not only made by us at TCX; There is other
persons from other SQL vendors involved.   We have not put any effort
to make MySQL 'look good';  crash-me only test and reports what a SQL
server supports.  How can this be 'slanted' ?

Bruce> That has been corrected quite a long time ago, I should say.

>> What I like about the MySQL developers (and the MySQL Manual) is that
>> they--as far as I know them--never try to devalue other products but
>> just state what's there, what's not there, for what kind of
>> applications MySQL fits best, for which not, and how to work around
>> given limitations, if possible.  In short:  they are objective.
>>
>> So it would be nice, if try to be objective, too.

Bruce> Let me mention something that is a was very glad to see.  The MySQL
Bruce> manuals actually recommend PostreSQL for people who need transactions.
Bruce> Now, that takes humility.  That is clearly a sign of MySQL objectivity.

I have no problem recommending PostgreSQL for applications that suits
postgreSQL.  We are targeting different applications.  There is room
for us both on the database market.

The only thing that bothers me is that a lot of postgreSQL
developers/users seems very hostile against MySQL.  What's worse, they
speak 'knowingly' about things that they haven't a clue about.

<cut>

Bruce> Basically, if you looked at the comparison chart, it made anyone wonder
Bruce> why they would use anything but MySQL it made MySQL look so good.  But
Bruce> people are use other database.

Exactly :)

Seriously;  We have tried to make MySQL as compatible as possible with
a lot of major databases.  This will of course show in a comparison
that list what types, functions and functionality a database
supports.


>> > This upset me, but the increasing growth of PostgreSQL and the
>> > awards we have won make me suspect that people are smarter than we
>> > think, and have figured out where MySQL is strongest, and where
>> > PostgreSQL is strongest.
>>
>> Exactly.  This is, why MySQL is often seen as db backend for web
>> applications, because the mix of mainly selects with fewer inserts,
>> combined with simple processing only and the need for great speed
>> fits exactly MySQL's feature set.
>> For many other applications MySQL won't be an ideal choice, but
>> actually (believe it or not) this is exactly what the MySQL developers
>> say themself, too!  Just use the right tool for the right task.

Bruce> Agreed.  I have a FAQ item on our web page that talks about comparison
Bruce> between ourselves and other databases at www.postgresql.org under Info
Bruce> Centra/Documentation.  I would be interested to hear any comments about
Bruce> it.  It does not mention any other database system, but generally talks
Bruce> about our strengths and weaknesses.

Why not add more test to crash-me (if you really think this is
missing), mail us the patches and then put a pointer to point to the
updated crash-me page?

Regards,
Monty

Re: [PHP3] Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Rasmus Lerdorf
Date:
> Aaron> as a final thought, it might be interesting to see how responsive each database
> Aaron> is via PHP scripts. though that would probably be a future test.
>
> The current tests are done with perl and DBI (just to keep the tests portable).

Not to be argumentative, but can you name a platform that supports
Perl/DBI that is not supported by PHP?

-Rasmus


Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> It would be very nice you could add to the above the features that
> MySQL has that postgreSQL is missing.  PostgreSQL does have some
> features that MySQL lacks, but MySQL does support a lot of ODBC and
> ANSI SQL things that postgreSQL doesn't support. We have also put a
> lot of effort in MySQL to implement a lot of useful functions from a
> lot of different SQL servers.  This is why MySQL 'shows off' on
> crash-me;  If someone else has some useful functionality/syntax, we also try
> to support it!

I can't go listing tons of things in this one paragraph.  Can you
suggest some wording, and maybe I can merge it in?  We don't want to say
anything about MySQL in a disparaging way.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
Wow, where do I start with this?

> Bruce> I am talking about the database comparison chart.  We said in the past,
> Bruce> "Hey, you don't mention a major feature that every DBMS should have, and
> Bruce> that is transaction/rollback, and they said, "We don't know how to test
> Bruce> for that."  Well, all it takes is "BEGIN WORK; DELETE; ROLLBACK" and see
> Bruce> if the deletion is there.  Not rocket science.
>
> It seams you are not up to date;  We added a test for transaction to
> crash me more than a year ago.

I stated many times that I was going on old information, because that is
the only information I have.  Maybe that is unfair, maybe not, but that
is the only thing I can base my opinions on.

I can perhaps retrieve the old e-mail stating the crashme maintainer
didn't know how to test for transactions.  At the same time, the test
did an amazingly bizarre UPDATE SET x = x + 1 on a table with unique
indexes, and reported that our transactions were not atomic.  Well, I
figure if the person could come up with that test, they should be able
to test for transactions.

A person reading that would think, "Well MySQL doesn't have
transactions, and PostgreSQL's transactions are not atomic, so they must
have the same limitations."  This is far from true, but when we tried to
have it corrected, we got a shrug, "Gee, how do I test for
transactions."   Come on, that is not believable.

Is that old news, maybe.  Did it make me feel like the crashme test
wasn't interested in the truth, yes.  Did make me avoid more work on
improving crashme, yes.

> A note about this;  We have a lot of database vendors to help us with
> making crash-me better, and we have got a lot of positive feedback!
> >From the postgreSQL developers we have however not got any help at
> all!  All postgreSQL test have been done by Luke on his spare time.

Well, we are all volunteers, and the above experience was enough to make
us shy away from wasting any time trying to work on it.  Also, MySQL is
backed by a company, while we are all volunteers, so there is a certain
suspicion that the crashme test has a certain monetary incentive behind
it.  This suspicion may be false, but we have it none-the-less.


> Bruce> Let me mention something that is a was very glad to see.  The MySQL
> Bruce> manuals actually recommend PostreSQL for people who need transactions.
> Bruce> Now, that takes humility.  That is clearly a sign of MySQL objectivity.
>
> I have no problem recommending PostgreSQL for applications that suits
> postgreSQL.  We are targeting different applications.  There is room
> for us both on the database market.
>
> The only thing that bothers me is that a lot of postgreSQL
> developers/users seems very hostile against MySQL.  What's worse, they
> speak 'knowingly' about things that they haven't a clue about.

I think your impression is correct.  There is a hostile atmosphere about
MySQL.  Part of it was that, in the past, we were worked like crazy, and
no was taking serious interest in PostgreSQL.  They were all talking
about MySQL.  Now, in the past 1 1/2 years, we are getting serious
consideration.  I don't think MySQL had anything to do with that(except
being a good product that people liked), but some also felt that the
crashme test was made to continue that misinformation about PostgreSQL.

No sense in trying to hide our feelings on this.  Keeping quiet and
stewing about it is not going to help.  We might as well say how we feel
so MySQL can know our motivations.  We certainly would rather not feel
the way we do.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
"Aaron J. Seigo"
Date:
hi all...


> Note that it would be much better to write a benchmark that can be run
> against any SQL server.  As the MySQL benchmarks are designed to do
> exactly this, I would suggest that you use the MySQL benchmark suit as
> a base for this!

i disagree heartily. standardized benchmarks such as these are rediculous wastes
of time, especially when done with database engines. why?

because, as has been shown time and time again on both sides of any fence you
look at in our wonderful world of software, benchmarks usually favour one side
or the other due to the fact that they are standard. well, software isn't
standard. MySQL and PostgreSQL use different methods for handling queries,
indecies, data, etc.. should we expect the exact same queries to perform equally
well on both? no! it will most probably favour one or the other, depending how
it is written. i'm not saying this is always intentional, its merely a fact
about software and standardized benchmarking as i see it.

what is better? well, what is software used for? to run arbitrary
queries/functions? NO! to solve a problem, complete a task. SO. give me a task.
allow me to use the software to complete that task. because in the real world,
i don't write the same queries in the same way for two different database
engines. i don't use X the same way i use Windows. i don't use GIMP the same
way i use photoshop.

benchmarks are a waste of time. problem solutions designec for the products at
hand show the REAL potential of the systems and can't be screwed with. after
all, all parties are going to use everything they can to make it fast. same
results. different paths. different software. apples and apples.

its not the method. its the answer to the question.

> I don't think the important things is just to optimize some specific
> queries; It's much more important to test a lot of different types of
> queries.

on this i agree. so, lets optimize all the queries (or not =), but leave it to
the hackers at their discretion.

> I think it's important to using queries that are common to many
> applications.
>
> For normal queries there isn't that much to optimize.  There is of
> course also the option to add some test to 'solve some problem'.  In
> this case one can use different methods to solve the query for
> different databases;  Normally these kind of tests are more
> interesting than useful as this isn't normally portable between
> database servers.

portability is a non issue, in my mind. when i use a database engine, i use
every nook and cranny of it when querying, etc to get performance. example:
would you forgo (sp) using PL/SQL in Oracle because it isn't supported the
exact same way everywhere (anywhere?) else? of course not. portability is a non
issue. problem solution power when given to a skilled user is. or an unskilled
user.

i think such tests are more telling than benchmarks. benchmarks are
interesting, but completely uninterpretable in a meaningful way (see above).
problem solution capability shows the true power of a system, esp. if that
system has unique ways of doing things that REALLY make a difference.

> Aaron> this is open source, remember? its about choice. its about the right tool for
> Aaron> the right job. its about a FUDless environment where we get the tools to do
> Aaron> what we NEED to do.
>
> This is exactly the aim of the MySQL benchmarks.  (They are GPL)

this (GPLing) is indeed applaudable!

> Aaron> as coordinator, i'd be willing to collect the final parameters, communicate
> Aaron> with representatives from each side (probably the developers?) and post the
> Aaron> results (after running them, of course :-)
>
> In this case;  Can you take a look at the MySQL benchmark suite and
> comment on this?

i believe my comments above stand. i do not support benchmarking. it only works
when you have two identical systems. but the point of such exercises is to
differences between DIFFERENT systems. =)

that said (again), i'll go grab the crash_me test suite and look it over, toss
in my 2 cents, if i have that much at the end of the exercise =)

--
Aaron J. Seigo
Sys Admin

Re: [GENERAL] Re: PostgreSQL vs Mysql comparison

From
"Aaron J. Seigo"
Date:
hi...

> Well, we are all volunteers, and the above experience was enough to make
> us shy away from wasting any time trying to work on it.  Also, MySQL is
> backed by a company, while we are all volunteers, so there is a certain
> suspicion that the crashme test has a certain monetary incentive behind
> it.  This suspicion may be false, but we have it none-the-less.

suspicions aside, the motivation of a company is always  different from a
collective of kindred souls. the former is to make a living to eat, the latter
to accomplish common goals.

obviously, the kindred-soul group will not like the approach of a company, even
though the company is doing exactly what a company needs to and should be
doing. its a difference of outlook on coding, life, monetary success...

but no reflection on the products, imo. instead, the final products are. a
company can make awesome software (hmm.... qualcomm's Eudora! =) or crappy
software (MS Exchange Server).. a kindred soul group can.. well, you get the
pic.

> > postgreSQL.  We are targeting different applications.  There is room
> > for us both on the database market.

hear! hear!

> > The only thing that bothers me is that a lot of postgreSQL
> > developers/users seems very hostile against MySQL.  What's worse, they
> > speak 'knowingly' about things that they haven't a clue about.
>
> I think your impression is correct.  There is a hostile atmosphere about
> MySQL.  Part of it was that, in the past, we were worked like crazy, and
> no was taking serious interest in PostgreSQL.  They were all talking
> about MySQL.  Now, in the past 1 1/2 years, we are getting serious

and why? because people were speaking 'knowingly' about things that they
haven't a clue about... when will this behaviour stop to astonish us?

 > consideration.  I don't think MySQL had anything to do with that(except
>being a good product that people liked), but some also felt that the
> crashme test was made to continue that misinformation about PostgreSQL.

the current results seem to reflect this, though i would take it a step further.
crashme seems to be designed to make MySQL look good and everything else look
bad. i don't think its aimed at PostgreSQL. i think its a unilateral "hey,
MySQL rox!" perhaps this is also why Oracle/Informix/DB2 people laugh loudly at
the mention of MySQL for anything (which is wrong, for it has its uses)
but take PostgreSQL at least somewhat seriously.

if one decides to poke the eyes of others, make sure you can back yourself up
with something impressive. otherwise, expect derision. look at
microsoft.

>We certainly would rather not feel the way we do.

so, what can be done? would it be possible to get together and mark out areas
where MySQL is better, PostgreSQL is better and both are equals, then post this
on a NEUTRAL site, linked from each's homepage, as a solid, united OSS RDBMs
front? imagine how powerful THAT would be for OSS. remember, the real 'annoying
guys <SPAT><SPAT>' are the binary-only commercial distributors.

2 cents more. *clink*

--
Aaron J. Seigo
Sys Admin

Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
Hi!

<cut>
Bruce> I stated many times that I was going on old information, because that is
Bruce> the only information I have.  Maybe that is unfair, maybe not, but that
Bruce> is the only thing I can base my opinions on.

Wouldn't it have been better to first check up things before posting ?

Bruce> I can perhaps retrieve the old e-mail stating the crashme maintainer
Bruce> didn't know how to test for transactions.  At the same time, the test
Bruce> did an amazingly bizarre UPDATE SET x = x + 1 on a table with unique
Bruce> indexes, and reported that our transactions were not atomic.  Well, I
Bruce> figure if the person could come up with that test, they should be able
Bruce> to test for transactions.

It was I that asked for the help to check for transactions a long time
ago (and I got help from Luuk to make this in a way that should be
portable to most sql servers).

Note however that the above test must be supported by a transaction
system to be fully transactional.  Almost all other databases (except
postgreSQL) that supports transactions, can handle the UPDATE test.

Bruce> A person reading that would think, "Well MySQL doesn't have
Bruce> transactions, and PostgreSQL's transactions are not atomic, so they must
Bruce> have the same limitations."

Sorry to say, but the above is true.  There was some discussion about
this on the postgreSQL list about fixing this bug, but they concluded
that it was too hard to fix so they decided to let it be.

Bruce> This is far from true, but when we tried to
Bruce> have it corrected, we got a shrug, "Gee, how do I test for
Bruce> transactions."   Come on, that is not believable.

Who did try to get a correction?  No one contacted me above it!

We did add the transaction test very early, but the test you mention is
independent of this test!  This tests if the database server support
atomic transactions, which is VERY important in some cases.

Bruce> Is that old news, maybe.  Did it make me feel like the crashme test
Bruce> wasn't interested in the truth, yes.  Did make me avoid more work on
Bruce> improving crashme, yes.

Your comment reflects one that I have got from many postgreSQL
developers;  You seem to think that everyone is against postgreSQL and
you take it personally when postgreSQL can't handle something.

(I have talked with a couple of resonable postgreSQL developers, so I
know that the above doesn't hold for everyone, but the major part of
the mail I get from postgreSQL developers are not very resonable :()

The simple truth is that postgreSQL can't handle atomic transactions,
as required by ANSI SQL.  One can however argue that this is not that
important, but this is a whole different story!

To take it from the beginning:

The whole idea of crash-me is to find things that a database support
and doesn't support, to make it easier to write applications for it.
This makes it VERY important to find all possible limitations for a
database.  Doesn't this sound reasonable ?

A last note about this topic:  I got the query used in crash-me from a
SQL book that describes how transactions works...

>> A note about this;  We have a lot of database vendors to help us with
>> making crash-me better, and we have got a lot of positive feedback!
>> >From the postgreSQL developers we have however not got any help at
>> all!  All postgreSQL test have been done by Luke on his spare time.

Bruce> Well, we are all volunteers, and the above experience was enough to make
Bruce> us shy away from wasting any time trying to work on it.  Also, MySQL is
Bruce> backed by a company, while we are all volunteers, so there is a certain
Bruce> suspicion that the crashme test has a certain monetary incentive behind
Bruce> it.  This suspicion may be false, but we have it none-the-less.

We at TCX don't get any money from providing crash-me or improving the
benchmarks result for postgreSQL, but have still done this as a free
service.  We know that the postgreSQL developers has used crash-me to
make postgreSQL more bug free and we are very happy about this too.  We
also do offer a lot of free support and help for MySQL and most MySQL
usage is free.  I wouldn't compare us with 'an evil emporium', just
because we charge for a MySQL license if you bundle MySQL with an
application that is not free.

Bruce> Let me mention something that is a was very glad to see.  The MySQL
Bruce> manuals actually recommend PostreSQL for people who need transactions.
Bruce> Now, that takes humility.  That is clearly a sign of MySQL objectivity.
>>
>> I have no problem recommending PostgreSQL for applications that suits
>> postgreSQL.  We are targeting different applications.  There is room
>> for us both on the database market.
>>
>> The only thing that bothers me is that a lot of postgreSQL
>> developers/users seems very hostile against MySQL.  What's worse, they
>> speak 'knowingly' about things that they haven't a clue about.

Bruce> I think your impression is correct.  There is a hostile atmosphere about
Bruce> MySQL.  Part of it was that, in the past, we were worked like crazy, and
Bruce> no was taking serious interest in PostgreSQL.  They were all talking
Bruce> about MySQL.  Now, in the past 1 1/2 years, we are getting serious
Bruce> consideration.  I don't think MySQL had anything to do with that(except
Bruce> being a good product that people liked), but some also felt that the
Bruce> crashme test was made to continue that misinformation about PostgreSQL.

I think it's a pity that you feel hostile to MySQL just because you think you
deserve better.  We on the other hand has tried to help postgreSQL as
much as we can by making it look better than it would otherwise have
done, by putting a lot of extra efforts in providing crash-me
information for postgreSQL and tuning the benchmarks for postgreSQL.

I still don't know about a single thing that crash-me have reported wrong
about postgreSQL that hasn't been fixed as soon as it has been brought
to our attention.

As you may have seen on the postgreSQL list, crash-me was actually
right that postgreSQL didn't support -- comments and HAVING as
required by ANSI SQL.  Isn't it nice that we help you fix your bugs?

Bruce> No sense in trying to hide our feelings on this.  Keeping quiet and
Bruce> stewing about it is not going to help.  We might as well say how we feel
Bruce> so MySQL can know our motivations.  We certainly would rather not feel
Bruce> the way we do.

I really hope you can grow out of this.  There is so much we can do
together to make both products better!

Regards,
Monty

Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Wed, 6 Oct 1999, Michael Widenius wrote:

>
>
> Hi!
>
> <cut>
>
> The> What I'd love to see is a common set of data to work from, and table
> The> structures...then come up with, again, a common set of "results expected",
> The> from the database.  Then, let the MySQL guys provide their query for the
> The> results, and let the PostgreSQL guys come up with theirs, and compare
> The> *that*.  Don't compare how to get the results the fastest through MySQL,
> The> and then how the same query stands up under PostgreSQL...let us give them
> The> how to get it fastest from PostgreSQL...
>
> I have posted many messages to Postgresql developers about getting
> benchmark that shows where PosgreSQL is good, but I haven't found
> anyone that is interested in spending time on this!  I have got the
> definite feeling that the postgreSQL developers are not interested in
> working on benchmarks!

Who have you posted this as, and to what mailing lists?  At this time, I
don't recall ever seeing you post something, but it may have been that I
was so busy that it slip'd me by...

Our experiences *up until this past week* was that any time we tried to
get somethign fixed, it was met with resistence, so we just said to hell
with it...

Obviously, there has been some mis-communications going on here...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
I have been asked to stop this discussion by Marc, our leader.  We will
try to work together in the future, and Marc is working on that now.

Sorry, but I shouldn't comment on the rest of the message.

>
> Hi!
>
> <cut>
> Bruce> I stated many times that I was going on old information, because that is
> Bruce> the only information I have.  Maybe that is unfair, maybe not, but that
> Bruce> is the only thing I can base my opinions on.
>
> Wouldn't it have been better to first check up things before posting ?

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [GENERAL] Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Tom Dryden
Date:
I think that everyone is dancing all around the crux of the problem.  Benchmarks define a
set
of circumstances by which products are compared.  To define a problem space that does
not favor one system or the other is a non  trivial problem in and of itself. Real world
tasks
are complex and have many facets. So,  how do we define a single real world task that
represents a fair distribution and utilization of the features that are important in
multiple
problem spaces? I don't know, and haven't seen one yet.
We should  accept that one solution is better in a situation and another is better in
another but
understand the criteria.

Does this sound too much like ENGINEERING?
An understanding of what a particular benchmark is designed to illustrate is far more
important than
the results of a particular test.  Know YOUR problem space and pick a product that fits.

Tom

"Aaron J. Seigo" wrote:

> hi all...
>
> > Note that it would be much better to write a benchmark that can be run
> > against any SQL server.  As the MySQL benchmarks are designed to do
> > exactly this, I would suggest that you use the MySQL benchmark suit as
> > a base for this!
>
> i disagree heartily. standardized benchmarks such as these are rediculous wastes
> of time, especially when done with database engines. why?
>
> because, as has been shown time and time again on both sides of any fence you
> look at in our wonderful world of software, benchmarks usually favour one side
> or the other due to the fact that they are standard. well, software isn't
> standard. MySQL and PostgreSQL use different methods for handling queries,
> indecies, data, etc.. should we expect the exact same queries to perform equally
> well on both? no! it will most probably favour one or the other, depending how
> it is written. i'm not saying this is always intentional, its merely a fact
> about software and standardized benchmarking as i see it.
>
> what is better? well, what is software used for? to run arbitrary
> queries/functions? NO! to solve a problem, complete a task. SO. give me a task.
> allow me to use the software to complete that task. because in the real world,
> i don't write the same queries in the same way for two different database
> engines. i don't use X the same way i use Windows. i don't use GIMP the same
> way i use photoshop.
>
> benchmarks are a waste of time. problem solutions designec for the products at
> hand show the REAL potential of the systems and can't be screwed with. after
> all, all parties are going to use everything they can to make it fast. same
> results. different paths. different software. apples and apples.
>
> its not the method. its the answer to the question.
>
> > I don't think the important things is just to optimize some specific
> > queries; It's much more important to test a lot of different types of
> > queries.
>
> on this i agree. so, lets optimize all the queries (or not =), but leave it to
> the hackers at their discretion.
>
> > I think it's important to using queries that are common to many
> > applications.
> >
> > For normal queries there isn't that much to optimize.  There is of
> > course also the option to add some test to 'solve some problem'.  In
> > this case one can use different methods to solve the query for
> > different databases;  Normally these kind of tests are more
> > interesting than useful as this isn't normally portable between
> > database servers.
>
> portability is a non issue, in my mind. when i use a database engine, i use
> every nook and cranny of it when querying, etc to get performance. example:
> would you forgo (sp) using PL/SQL in Oracle because it isn't supported the
> exact same way everywhere (anywhere?) else? of course not. portability is a non
> issue. problem solution power when given to a skilled user is. or an unskilled
> user.
>
> i think such tests are more telling than benchmarks. benchmarks are
> interesting, but completely uninterpretable in a meaningful way (see above).
> problem solution capability shows the true power of a system, esp. if that
> system has unique ways of doing things that REALLY make a difference.
>
> > Aaron> this is open source, remember? its about choice. its about the right tool for
> > Aaron> the right job. its about a FUDless environment where we get the tools to do
> > Aaron> what we NEED to do.
> >
> > This is exactly the aim of the MySQL benchmarks.  (They are GPL)
>
> this (GPLing) is indeed applaudable!
>
> > Aaron> as coordinator, i'd be willing to collect the final parameters, communicate
> > Aaron> with representatives from each side (probably the developers?) and post the
> > Aaron> results (after running them, of course :-)
> >
> > In this case;  Can you take a look at the MySQL benchmark suite and
> > comment on this?
>
> i believe my comments above stand. i do not support benchmarking. it only works
> when you have two identical systems. but the point of such exercises is to
> differences between DIFFERENT systems. =)
>
> that said (again), i'll go grab the crash_me test suite and look it over, toss
> in my 2 cents, if i have that much at the end of the exercise =)
>
> --
> Aaron J. Seigo
> Sys Admin
>
> ************


Re: PostgreSQL vs Mysql comparison

From
The Hermit Hacker
Date:
On Thu, 7 Oct 1999, Michael Widenius wrote:

> As you may have seen on the postgreSQL list, crash-me was actually
> right that postgreSQL didn't support -- comments and HAVING as
> required by ANSI SQL.  Isn't it nice that we help you fix your bugs?

Actually, I believe Thomas did research on this point (or was it Tom?) and
determined that HAVING w/o aggregates is *not* required by ANSI SQL ...
could out point out where this requirement is listed? *raised eyebrow*

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org


Re: PostgreSQL vs Mysql comparison

From
Bruce Momjian
Date:
> On Thu, 7 Oct 1999, Michael Widenius wrote:
>
> > As you may have seen on the postgreSQL list, crash-me was actually
> > right that postgreSQL didn't support -- comments and HAVING as
> > required by ANSI SQL.  Isn't it nice that we help you fix your bugs?
>
> Actually, I believe Thomas did research on this point (or was it Tom?) and
> determined that HAVING w/o aggregates is *not* required by ANSI SQL ...
> could out point out where this requirement is listed? *raised eyebrow*

Seems we will downgrade it to a warning.  Too many people misunderstand
HAVING vs. WHERE, so a nice warning would help new users.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [PHP3] Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
>>>>> "The" == The Hermit Hacker <scrappy@hub.org> writes:

The> On Wed, 6 Oct 1999, Michael Widenius wrote:
>>
>>
>> Hi!
>>
>> <cut>
>>
The> What I'd love to see is a common set of data to work from, and table
The> structures...then come up with, again, a common set of "results expected",
The> from the database.  Then, let the MySQL guys provide their query for the
The> results, and let the PostgreSQL guys come up with theirs, and compare
The> *that*.  Don't compare how to get the results the fastest through MySQL,
The> and then how the same query stands up under PostgreSQL...let us give them
The> how to get it fastest from PostgreSQL...
>>
>> I have posted many messages to Postgresql developers about getting
>> benchmark that shows where PosgreSQL is good, but I haven't found
>> anyone that is interested in spending time on this!  I have got the
>> definite feeling that the postgreSQL developers are not interested in
>> working on benchmarks!

The> Who have you posted this as, and to what mailing lists?  At this time, I
The> don't recall ever seeing you post something, but it may have been that I
The> was so busy that it slip'd me by...

Most post has gone through 'luuk@wxs.nl', as I hasn't subscribed to
the postgreSQL mailing list.  Some other discussions have been
directly with some postgreSQL developers.

The> Our experiences *up until this past week* was that any time we tried to
The> get somethign fixed, it was met with resistence, so we just said to hell
The> with it...

I remember some request that said 'this can't be true, FIX IT'.  We
tried repeatedly to get patches, as we don't know why postgreSQL
failed for things where it shouldn't, but we never got anything.

We have tried to fixed most things that has come up (Thanks to luuk
for this!), but we have refused to fix some things where
a SQL server doesn't work according to the ANSI SQL standard.
(MySQL does also a lot of things that is not strictly ANSI SQL and we
report this too!)

The> Obviously, there has been some mis-communications going on here...

I agree.  Hope we have this sorted out now!

Anyway, if you find something wrong, please check this up yourself and
submit a patch!  This will help us fix things happen much more rapidly!

Also, if you think we have missed some tests in crash-me, please add
them!  There must be some interesting limit that we have missed!

Regards,
Monty

Re: PostgreSQL vs Mysql comparison

From
Michael Widenius
Date:
>>>>> "Bruce" == Bruce Momjian <maillist@candle.pha.pa.us> writes:

>> On Thu, 7 Oct 1999, Michael Widenius wrote:
>>
>> > As you may have seen on the postgreSQL list, crash-me was actually
>> > right that postgreSQL didn't support -- comments and HAVING as
>> > required by ANSI SQL.  Isn't it nice that we help you fix your bugs?
>>
>> Actually, I believe Thomas did research on this point (or was it Tom?) and
>> determined that HAVING w/o aggregates is *not* required by ANSI SQL ...
>> could out point out where this requirement is listed? *raised eyebrow*

According to C.J.DATE, A Guide to the SQL standard, forth edition,
page 155, one is allowed in ANSI SQL to use HAVING with any GROUP BY
statement.  One is also allowed to use any scalar functions in the
HAVING part as long as the scalar value is single-value per group.

Where did Thomas/Tom look this up?

Bruce> Seems we will downgrade it to a warning.  Too many people misunderstand
Bruce> HAVING vs. WHERE, so a nice warning would help new users.

Should you really give a warning for something that is correct ANSI SQL ?

Regards,
Monty

Re: PostgreSQL vs Mysql comparison

From
"Matthias Urlichs"
Date:
Hi,

Michael Widenius:
> Bruce> Seems we will downgrade it to a warning.  Too many people misunderstand
> Bruce> HAVING vs. WHERE, so a nice warning would help new users.
>
> Should you really give a warning for something that is correct ANSI SQL ?
>
If people mix up HAVING and WHERE, the problem seems to be mainly that
somebody has told them about the existence of HAVING way too early.

--
Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
The quote was selected randomly. Really.    |      http://www.noris.de/~smurf/
--
JOB INTERVIEW:
       The excruciating process during which personnel officers
       separate the wheat from the chaff -- then hire the chaff.