Обсуждение: PostgreSQL vs Mysql comparison
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
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
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
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
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
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
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
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
> 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
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
> 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
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
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
> > 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
> > 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
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
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
[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
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
> [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
> [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
> 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
> 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
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
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
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 | +----------------------------------------------------------------+
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
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
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
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
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
> >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
> >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
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
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
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
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
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
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
>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
>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
>>>>> "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
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
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
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
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
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
<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
>>>>> "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
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
> 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
> 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
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
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
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
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
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
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
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 > > ************
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
> 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
>>>>> "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
>>>>> "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
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.