Thread: MySQL 5 comparison
Has anyone spent any time with the MySQL 5.0 alpha, set to go into beta shortly (http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)? Would be interesting to have a rudimentary comparison checklist - not so much benchmarks, as features, as they seem to haveadded a lot. And any info on how they've implemented these features (e.g. multiple table types in order to use differentfeatures, etc.) would be of interest. Cheers, Ned
On Wed, Jan 05, 2005 at 10:42:23AM -0500, Ned Lilly wrote: > Has anyone spent any time with the MySQL 5.0 alpha, set to go into > beta shortly > (http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)? > > Would be interesting to have a rudimentary comparison checklist - > not so much benchmarks, as features, as they seem to have added a > lot. And any info on how they've implemented these features (e.g. > multiple table types in order to use different features, etc.) would > be of interest. Anybody who's interested may want to check this: http://dev.mysql.com/doc/mysql/en/News-5.0.x.html There are 4 more links at the bottom of that page that will help. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
What caught my eye is the "strict mode". I wonder if they are going to
start promoting the reporting of errors? Right now MySQL seems to have
the philosophy that "if the input is wrong, it is better to do something
than nothing" (e.g. inserting Feb 31st into a date field).
Perhaps they're trying to change that philosophy slowly with this strict
mode?
Regards,
Jeff Davis
On Wed, 2005-01-05 at 15:23 -0800, David Fetter wrote:
> On Wed, Jan 05, 2005 at 10:42:23AM -0500, Ned Lilly wrote:
> > Has anyone spent any time with the MySQL 5.0 alpha, set to go into
> > beta shortly
> > (http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)?
> >
> > Would be interesting to have a rudimentary comparison checklist -
> > not so much benchmarks, as features, as they seem to have added a
> > lot. And any info on how they've implemented these features (e.g.
> > multiple table types in order to use different features, etc.) would
> > be of interest.
>
> Anybody who's interested may want to check this:
>
> http://dev.mysql.com/doc/mysql/en/News-5.0.x.html
>
> There are 4 more links at the bottom of that page that will help.
>
> Cheers,
> D
On Wed, Jan 05, 2005 at 05:37:19PM -0800, Jeff Davis wrote: > What caught my eye is the "strict mode". I wonder if they are going > to start promoting the reporting of errors? Right now MySQL seems > to have the philosophy that "if the input is wrong, it is better to > do something than nothing" (e.g. inserting Feb 31st into a date > field). Right. > Perhaps they're trying to change that philosophy slowly with this > strict mode? If that change is coming, it's coming slowly. "strict mode" is not the default, nor are there plans to make it so :P Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
Clinging to sanity, jdavis-pgsql@empires.org (Jeff Davis) mumbled into her beard: > What caught my eye is the "strict mode". I wonder if they are going > to start promoting the reporting of errors? Right now MySQL seems to > have the philosophy that "if the input is wrong, it is better to do > something than nothing" (e.g. inserting Feb 31st into a date field). > > Perhaps they're trying to change that philosophy slowly with this > strict mode? You're assuming that there will be some incentive to use this new mode. Existing applications that are rife with dependancies on existing functionality will _break_ if they turn on "strict mode." And what value is there in fixing them? That doesn't add new functionality... No, I don't see a great deal of value in this, outside of _new_ commercial users that are paying for licenses... -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://www.ntlug.org/~cbbrowne/spreadsheets.html "A hack is a terrible thing to waste, please give to the implementation of your choice..." -- GJC
You're assuming that there will be some incentive to use this new mode. Existing applications that are rife with dependancies on existing functionality will _break_ if they turn on "strict mode." And what value is there in fixing them? That doesn't add new functionality... No, I don't see a great deal of value in this, outside of _new_ commercial users that are paying for licenses... -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://www.ntlug.org/~cbbrowne/spreadsheets.html "A hack is a terrible thing to waste, please give to the implementation of your choice..." -- GJC
For all those who think of comparing PostgreSQL - maybe this story should be added. I HAD to do benchmark a benchmark comparing MySQL and Slony replication. a. if you create a table as Innodb it MIGHT become ISAM without warning (depending on a nicely hidden config parameter). b. it seems as if BEGIN / COMMIT are silently accepted in ISAM tables ... c. when dumping a master database it will not necessarily restore on the slave database; we got a primary key violation on a unique column a couple of times. d. then we did a replication scenario: lucent@schankserver:~/replication_tests/query$ cat 05.sql BEGIN; UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT'; UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT'; COMMIT; BEGIN; DELETE FROM t_one WHERE id = 'RANDOMINT'; ROLLBACK; myisam -> innodb replication: when doing this script above (30 concurrent users, 50 runs / user) After the run PostgreSQL still had 500.000 records in the table - mysql had only 499.950 (rest was ignored because MyISAM cannot do rollback). But if I do 30 user * 50 runs = 1500 delete statements; why do only 50 records miss? What do you think? Is a database allowed to eat data and issue as WARNING instead of a hyper-fatal error? MySQL benchmark with replication; 2 concurrent users; 10000 repetitions real 2m06.893s MySQL benchmark with replication; 40 concurrent users; 500 repetitions real 6m40.474s In case of just two concurrent users MySQL is truly fast – it is very unlikely that two users will hit the same random data area. However, the situation changes dramatically when the number of concurrent users is risen. Although we execute the same number of statements MySQL will be 2 ½ times slower (with Innodb). In case of MyISAM we have seen MySQL being 5 times slower than PostgreSQL. PostgreSQL with replication; 2 concurrent users; 10000 repetitions real 2m4.317s PostgreSQL with replication; 40 concurrent users; 500 repetitions real 2m53.324s In contrast to MySQL, PostgreSQL will perform really well in case of multiple concurrent users. The time needed is increasing but those changes are not that dramatical. We think that at least 10 seconds could be shaved off by doing further tweaks inside the background writer process. Any more questions? Is it still worth to compare? I think we can agree that MySQL is crap ... Best regards, Hans David Fetter wrote: >On Wed, Jan 05, 2005 at 05:37:19PM -0800, Jeff Davis wrote: > > >>What caught my eye is the "strict mode". I wonder if they are going >>to start promoting the reporting of errors? Right now MySQL seems >>to have the philosophy that "if the input is wrong, it is better to >>do something than nothing" (e.g. inserting Feb 31st into a date >>field). >> >> > >Right. > > > >>Perhaps they're trying to change that philosophy slowly with this >>strict mode? >> >> > >If that change is coming, it's coming slowly. "strict mode" is not >the default, nor are there plans to make it so :P > >Cheers, >D > >
That note about the lost replication rows should be added to the MySQL Gotchas site... Hans-Jürgen Schönig wrote: > For all those who think of comparing PostgreSQL - maybe this story > should be added. > I HAD to do benchmark a benchmark comparing MySQL and Slony replication. > > a. if you create a table as Innodb it MIGHT become ISAM without warning > (depending on a nicely hidden config parameter). > b. it seems as if BEGIN / COMMIT are silently accepted in ISAM tables ... > c. when dumping a master database it will not necessarily restore on the > slave database; we got a primary key violation on a unique > column a couple of times. > d. then we did a replication scenario: > > lucent@schankserver:~/replication_tests/query$ cat 05.sql > BEGIN; > UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT'; > UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT'; > COMMIT; > > BEGIN; > DELETE FROM t_one WHERE id = 'RANDOMINT'; > ROLLBACK; > > myisam -> innodb replication: > when doing this script above (30 concurrent users, 50 runs / user) > > > After the run PostgreSQL still had 500.000 records in the table - mysql > had only 499.950 (rest was ignored because MyISAM cannot do rollback). > But if I do 30 user * 50 runs = 1500 delete statements; why do only 50 > records miss? > > What do you think? Is a database allowed to eat data and issue as > WARNING instead of a hyper-fatal error? > > MySQL benchmark with replication; 2 concurrent users; 10000 repetitions > real 2m06.893s > > MySQL benchmark with replication; 40 concurrent users; 500 repetitions > real 6m40.474s > > In case of just two concurrent users MySQL is truly fast – it is very > unlikely that two users will hit the same random data area. However, the > situation changes dramatically when the number of concurrent users is > risen. Although we execute the same number of statements MySQL will be 2 > ½ times slower (with Innodb). In case of MyISAM we have seen MySQL being > 5 times slower than PostgreSQL. > PostgreSQL with replication; 2 concurrent users; 10000 repetitions > real 2m4.317s > PostgreSQL with replication; 40 concurrent users; 500 repetitions > real 2m53.324s > > In contrast to MySQL, PostgreSQL will perform really well in case of > multiple concurrent users. The time needed is increasing but those > changes are not that dramatical. We think that at least 10 seconds could > be shaved off by doing further tweaks inside the background writer process. > > > > Any more questions? Is it still worth to compare? I think we can agree > that MySQL is crap ... > > Best regards, > > Hans > > > > David Fetter wrote: > >> On Wed, Jan 05, 2005 at 05:37:19PM -0800, Jeff Davis wrote: >> >> >>> What caught my eye is the "strict mode". I wonder if they are going >>> to start promoting the reporting of errors? Right now MySQL seems >>> to have the philosophy that "if the input is wrong, it is better to >>> do something than nothing" (e.g. inserting Feb 31st into a date >>> field). >>> >> >> >> Right. >> >> >> >>> Perhaps they're trying to change that philosophy slowly with this >>> strict mode? >>> >> >> >> If that change is coming, it's coming slowly. "strict mode" is not >> the default, nor are there plans to make it so :P >> >> Cheers, >> D >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
> Has anyone spent any time with the MySQL 5.0 alpha, set to go into beta > shortly (http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)? > > Would be interesting to have a rudimentary comparison checklist - not so > much benchmarks, as features, as they seem to have added a lot. And any > info on how they've implemented these features (e.g. multiple table types > in order to use different features, etc.) would be of interest. > > Cheers, > Ned Putting my advocacy hat on, If you look at their description of the upcoming features, it's saturated with words like 'basic', 'initial', and 'rudimentary'. I don't think mysql 5.0 will be a watershed moment where it will become the database of choice for industrial application development... The new stuff on a point by point feature comparison may look impressive, but they need to work on internal stuff like the locking engine, get some real logging etc. On a more even handed note, it's nice to see them get some real features. Open source success stories are not zero-sum, so what's good for them is not necessarily bad for us. Competition is good, but pg is at least 3 years ahead of them in development! Merlin
Guys, I sent an email to Hans-Jürgen Schönig <postgres@cybertec.at> and asked if he could give me particulars about the bench marking he did. Could I have some comments about the idea of making a more structured presentation in an article, in the next SRA newsletter, http://sraapowergres.com ? On January 6, 2005 08:45 am, you wrote: > > Has anyone spent any time with the MySQL 5.0 alpha, set to go into > > beta > > > shortly > > (http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)? > > > Would be interesting to have a rudimentary comparison checklist - not > > so > > > much benchmarks, as features, as they seem to have added a lot. And > > any > > > info on how they've implemented these features (e.g. multiple table > > types > > > in order to use different features, etc.) would be of interest. > > > > Cheers, > > Ned > > Putting my advocacy hat on, > If you look at their description of the upcoming features, it's > saturated with words like 'basic', 'initial', and 'rudimentary'. I > don't think mysql 5.0 will be a watershed moment where it will become > the database of choice for industrial application development... > > The new stuff on a point by point feature comparison may look > impressive, but they need to work on internal stuff like the locking > engine, get some real logging etc. > > On a more even handed note, it's nice to see them get some real > features. Open source success stories are not zero-sum, so what's good > for them is not necessarily bad for us. Competition is good, but pg is > at least 3 years ahead of them in development! > > Merlin > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Merlin Moncure wrote: > Putting my advocacy hat on, > If you look at their description of the upcoming features, it's > saturated with words like 'basic', 'initial', and 'rudimentary'. I > don't think mysql 5.0 will be a watershed moment where it will become > the database of choice for industrial application development... That reminds me of another, related question ... can anyone comment on the relationship between MySQL 5 and MaxDB (formerlySAP DB)? The talk at one point was that the end result of that alliance would be a total rewrite of MySQL to incorporateconcepts from MaxDB, and that the new product would somehow be backward compatible with both predecessors.
Well, I think those of us in the pg community would be interested in reading it, but IMHO it's a losing proposition for SRA to push articles that highlight the negativities of other database systems. If you're going to go down that road you had better tread carefully; personally I would stick to articles that highlight customer successes. Robert Treat On Thu, 2005-01-06 at 09:11, Robert Bernier wrote: > Guys, > > I sent an email to Hans-Jürgen Schönig <postgres@cybertec.at> and asked if he > could give me particulars about the bench marking he did. Could I have some > comments about the idea of making a more structured presentation in an > article, in the next SRA newsletter, http://sraapowergres.com ? > > > On January 6, 2005 08:45 am, you wrote: > > > Has anyone spent any time with the MySQL 5.0 alpha, set to go into > > > > beta > > > > > shortly > > > > (http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)? > > > > > Would be interesting to have a rudimentary comparison checklist - not > > > > so > > > > > much benchmarks, as features, as they seem to have added a lot. And > > > > any > > > > > info on how they've implemented these features (e.g. multiple table > > > > types > > > > > in order to use different features, etc.) would be of interest. > > > > > > Cheers, > > > Ned > > > > Putting my advocacy hat on, > > If you look at their description of the upcoming features, it's > > saturated with words like 'basic', 'initial', and 'rudimentary'. I > > don't think mysql 5.0 will be a watershed moment where it will become > > the database of choice for industrial application development... > > > > The new stuff on a point by point feature comparison may look > > impressive, but they need to work on internal stuff like the locking > > engine, get some real logging etc. > > > > On a more even handed note, it's nice to see them get some real > > features. Open source success stories are not zero-sum, so what's good > > for them is not necessarily bad for us. Competition is good, but pg is > > at least 3 years ahead of them in development! > > > > Merlin > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Thu, 2005-01-06 at 05:03, Hans-Jürgen Schönig wrote:
> For all those who think of comparing PostgreSQL - maybe this story
> should be added.
> I HAD to do benchmark a benchmark comparing MySQL and Slony replication.
>
> a. if you create a table as Innodb it MIGHT become ISAM without warning
> (depending on a nicely hidden config parameter).
> b. it seems as if BEGIN / COMMIT are silently accepted in ISAM tables ...
> c. when dumping a master database it will not necessarily restore on the
> slave database; we got a primary key violation on a unique
> column a couple of times.
> d. then we did a replication scenario:
>
> lucent@schankserver:~/replication_tests/query$ cat 05.sql
> BEGIN;
> UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT';
> UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT';
> COMMIT;
>
> BEGIN;
> DELETE FROM t_one WHERE id = 'RANDOMINT';
> ROLLBACK;
>
> myisam -> innodb replication:
> when doing this script above (30 concurrent users, 50 runs / user)
>
>
> After the run PostgreSQL still had 500.000 records in the table - mysql
> had only 499.950 (rest was ignored because MyISAM cannot do rollback).
> But if I do 30 user * 50 runs = 1500 delete statements; why do only 50
> records miss?
>
> What do you think? Is a database allowed to eat data and issue as
> WARNING instead of a hyper-fatal error?
>
> MySQL benchmark with replication; 2 concurrent users; 10000 repetitions
> real 2m06.893s
>
> MySQL benchmark with replication; 40 concurrent users; 500 repetitions
> real 6m40.474s
>
> In case of just two concurrent users MySQL is truly fast – it is very
> unlikely that two users will hit the same random data area. However, the
> situation changes dramatically when the number of concurrent users is
> risen. Although we execute the same number of statements MySQL will be 2
> ½ times slower (with Innodb). In case of MyISAM we have seen MySQL being
> 5 times slower than PostgreSQL.
> PostgreSQL with replication; 2 concurrent users; 10000 repetitions
> real 2m4.317s
> PostgreSQL with replication; 40 concurrent users; 500 repetitions
> real 2m53.324s
>
> In contrast to MySQL, PostgreSQL will perform really well in case of
> multiple concurrent users. The time needed is increasing but those
> changes are not that dramatical. We think that at least 10 seconds could
> be shaved off by doing further tweaks inside the background writer process.
>
>
>
> Any more questions? Is it still worth to compare? I think we can agree
> that MySQL is crap ...
>
Mind if i ask which versions of postgresql(&slony)/my$ql these were
tested against and on what OS ?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Good point, but hard, solid, dependable information that is well presented is something you can't just ignore. I would like to go down this road a little farther and then look at the final product before deciding to publish or not. On January 6, 2005 09:40 am, Robert Treat wrote: > Well, I think those of us in the pg community would be interested in > reading it, but IMHO it's a losing proposition for SRA to push articles > that highlight the negativities of other database systems. If you're > going to go down that road you had better tread carefully; personally I > would stick to articles that highlight customer successes. > > Robert Treat > > On Thu, 2005-01-06 at 09:11, Robert Bernier wrote: > > Guys, > > > > I sent an email to Hans-Jürgen Schönig <postgres@cybertec.at> and asked > > if he could give me particulars about the bench marking he did. Could I > > have some comments about the idea of making a more structured > > presentation in an article, in the next SRA newsletter, > > http://sraapowergres.com ? > > > > On January 6, 2005 08:45 am, you wrote: > > > > Has anyone spent any time with the MySQL 5.0 alpha, set to go into > > > > > > beta > > > > > > > shortly > > > > > > (http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)? > > > > > > > Would be interesting to have a rudimentary comparison checklist - not > > > > > > so > > > > > > > much benchmarks, as features, as they seem to have added a lot. And > > > > > > any > > > > > > > info on how they've implemented these features (e.g. multiple table > > > > > > types > > > > > > > in order to use different features, etc.) would be of interest. > > > > > > > > Cheers, > > > > Ned > > > > > > Putting my advocacy hat on, > > > If you look at their description of the upcoming features, it's > > > saturated with words like 'basic', 'initial', and 'rudimentary'. I > > > don't think mysql 5.0 will be a watershed moment where it will become > > > the database of choice for industrial application development... > > > > > > The new stuff on a point by point feature comparison may look > > > impressive, but they need to work on internal stuff like the locking > > > engine, get some real logging etc. > > > > > > On a more even handed note, it's nice to see them get some real > > > features. Open source success stories are not zero-sum, so what's good > > > for them is not necessarily bad for us. Competition is good, but pg is > > > at least 3 years ahead of them in development! > > > > > > Merlin > > > > > > ---------------------------(end of > > > broadcast)--------------------------- TIP 2: you can get off all lists > > > at once with the unregister command (send "unregister > > > YourEmailAddressHere" to majordomo@postgresql.org) > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 7: don't forget to increase your free space map settings
Ned, > That reminds me of another, related question ... can anyone comment on the > relationship between MySQL 5 and MaxDB (formerly SAP DB)? The talk at one > point was that the end result of that alliance would be a total rewrite of > MySQL to incorporate concepts from MaxDB, and that the new product would > somehow be backward compatible with both predecessors. That was the idea, yes. Personally, I don't think it's possible; that's probably the reason why MySQL 5.0 is two years late. I think eventually MySQL AB will have to come to terms with the idea that they have two seperate products, MySQL and MaxDB, and the two can't be made compatible. -- Josh Berkus Aglio Database Solutions San Francisco
Robert, folks,
I have hacked a simple script (quick and dirty) to do some basic testing
via jdbc.
This is nothing special - just something simple to get basic results.
The basic idea to execute entire sets of statements and log their
duration in SQL format so that the data can be analyzed easily.
Robert, I have attached the script so you can use it if you wish.
If I can do something for you just let me know.
Best regards,
Hans
Robert Bernier wrote:
>Guys,
>
>I sent an email to Hans-Jürgen Schönig <postgres@cybertec.at> and asked if he
>could give me particulars about the bench marking he did. Could I have some
>comments about the idea of making a more structured presentation in an
>article, in the next SRA newsletter, http://sraapowergres.com ?
>
>
>On January 6, 2005 08:45 am, you wrote:
>
>
>>>Has anyone spent any time with the MySQL 5.0 alpha, set to go into
>>>
>>>
>>beta
>>
>>
>>
>>>shortly
>>>
>>>
>>(http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)?
>>
>>
>>
>>>Would be interesting to have a rudimentary comparison checklist - not
>>>
>>>
>>so
>>
>>
>>
>>>much benchmarks, as features, as they seem to have added a lot. And
>>>
>>>
>>any
>>
>>
>>
>>>info on how they've implemented these features (e.g. multiple table
>>>
>>>
>>types
>>
>>
>>
>>>in order to use different features, etc.) would be of interest.
>>>
>>>Cheers,
>>>Ned
>>>
>>>
>>Putting my advocacy hat on,
>>If you look at their description of the upcoming features, it's
>>saturated with words like 'basic', 'initial', and 'rudimentary'. I
>>don't think mysql 5.0 will be a watershed moment where it will become
>>the database of choice for industrial application development...
>>
>>The new stuff on a point by point feature comparison may look
>>impressive, but they need to work on internal stuff like the locking
>>engine, get some real logging etc.
>>
>>On a more even handed note, it's nice to see them get some real
>>features. Open source success stories are not zero-sum, so what's good
>>for them is not necessarily bad for us. Competition is good, but pg is
>>at least 3 years ahead of them in development!
>>
>>Merlin
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
Attachment
Thanks Hans, What you've done is what everybody thinks of doing. When it's time, I'll review your posts to advocacy and depending on how it strikes me I can either write it up as part of a FAQ, an editorial or maybe a full blown how-to. cheers Robert On January 6, 2005 02:43 pm, you wrote: > Robert, folks, > > I have hacked a simple script (quick and dirty) to do some basic testing > via jdbc. > This is nothing special - just something simple to get basic results. > > The basic idea to execute entire sets of statements and log their > duration in SQL format so that the data can be analyzed easily. > > Robert, I have attached the script so you can use it if you wish. > If I can do something for you just let me know. > > Best regards, > > Hans > > Robert Bernier wrote: > >Guys, > > > >I sent an email to Hans-Jürgen Schönig <postgres@cybertec.at> and asked if > > he could give me particulars about the bench marking he did. Could I have > > some comments about the idea of making a more structured presentation in > > an article, in the next SRA newsletter, http://sraapowergres.com ? > > > >On January 6, 2005 08:45 am, you wrote: > >>>Has anyone spent any time with the MySQL 5.0 alpha, set to go into > >> > >>beta > >> > >>>shortly > >> > >>(http://www.infoworld.com/article/05/01/04/HNmysql5beta_1.html)? > >> > >>>Would be interesting to have a rudimentary comparison checklist - not > >> > >>so > >> > >>>much benchmarks, as features, as they seem to have added a lot. And > >> > >>any > >> > >>>info on how they've implemented these features (e.g. multiple table > >> > >>types > >> > >>>in order to use different features, etc.) would be of interest. > >>> > >>>Cheers, > >>>Ned > >> > >>Putting my advocacy hat on, > >>If you look at their description of the upcoming features, it's > >>saturated with words like 'basic', 'initial', and 'rudimentary'. I > >>don't think mysql 5.0 will be a watershed moment where it will become > >>the database of choice for industrial application development... > >> > >>The new stuff on a point by point feature comparison may look > >>impressive, but they need to work on internal stuff like the locking > >>engine, get some real logging etc. > >> > >>On a more even handed note, it's nice to see them get some real > >>features. Open source success stories are not zero-sum, so what's good > >>for them is not necessarily bad for us. Competition is good, but pg is > >>at least 3 years ahead of them in development! > >> > >>Merlin > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > >---------------------------(end of broadcast)--------------------------- > >TIP 7: don't forget to increase your free space map settings
On Thu, 2005-01-06 at 11:53, Josh Berkus wrote:
> Ned,
>
> > That reminds me of another, related question ... can anyone comment on the
> > relationship between MySQL 5 and MaxDB (formerly SAP DB)? The talk at one
> > point was that the end result of that alliance would be a total rewrite of
> > MySQL to incorporate concepts from MaxDB, and that the new product would
> > somehow be backward compatible with both predecessors.
>
> That was the idea, yes. Personally, I don't think it's possible; that's
> probably the reason why MySQL 5.0 is two years late. I think eventually
> MySQL AB will have to come to terms with the idea that they have two seperate
> products, MySQL and MaxDB, and the two can't be made compatible.
>
I think the last theory I had heard was that they were going to make a
maxdb table type for my$ql that would be compatible with maxdb. This
much could work on it's surface (like so many things with my$ql, think
using transactions with myisam which doesnt work but doesnt toss errors)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat wrote:
> On Thu, 2005-01-06 at 05:03, Hans-Jürgen Schönig wrote:
>
>>For all those who think of comparing PostgreSQL - maybe this story
>>should be added.
>>I HAD to do benchmark a benchmark comparing MySQL and Slony replication.
>>
>>a. if you create a table as Innodb it MIGHT become ISAM without warning
>>(depending on a nicely hidden config parameter).
>>b. it seems as if BEGIN / COMMIT are silently accepted in ISAM tables ...
>>c. when dumping a master database it will not necessarily restore on the
>>slave database; we got a primary key violation on a unique
>>column a couple of times.
>>d. then we did a replication scenario:
>>
>>lucent@schankserver:~/replication_tests/query$ cat 05.sql
>>BEGIN;
>>UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT';
>>UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT';
>>COMMIT;
>>
>>BEGIN;
>>DELETE FROM t_one WHERE id = 'RANDOMINT';
>>ROLLBACK;
>>
>>myisam -> innodb replication:
>>when doing this script above (30 concurrent users, 50 runs / user)
>>
>>
>>After the run PostgreSQL still had 500.000 records in the table - mysql
>>had only 499.950 (rest was ignored because MyISAM cannot do rollback).
>>But if I do 30 user * 50 runs = 1500 delete statements; why do only 50
>>records miss?
>>
>>What do you think? Is a database allowed to eat data and issue as
>>WARNING instead of a hyper-fatal error?
>>
>>MySQL benchmark with replication; 2 concurrent users; 10000 repetitions
>>real 2m06.893s
>>
>>MySQL benchmark with replication; 40 concurrent users; 500 repetitions
>>real 6m40.474s
>>
>>In case of just two concurrent users MySQL is truly fast – it is very
>>unlikely that two users will hit the same random data area. However, the
>>situation changes dramatically when the number of concurrent users is
>>risen. Although we execute the same number of statements MySQL will be 2
>>½ times slower (with Innodb). In case of MyISAM we have seen MySQL being
>>5 times slower than PostgreSQL.
>>PostgreSQL with replication; 2 concurrent users; 10000 repetitions
>>real 2m4.317s
>>PostgreSQL with replication; 40 concurrent users; 500 repetitions
>>real 2m53.324s
>>
>>In contrast to MySQL, PostgreSQL will perform really well in case of
>>multiple concurrent users. The time needed is increasing but those
>>changes are not that dramatical. We think that at least 10 seconds could
>>be shaved off by doing further tweaks inside the background writer process.
>>
>>
>>
>>Any more questions? Is it still worth to compare? I think we can agree
>>that MySQL is crap ...
>>
>
>
> Mind if i ask which versions of postgresql(&slony)/my$ql these were
> tested against and on what OS ?
>
> Robert Treat
We used PostgreSQL 8.0 RC3 and MySQL Debian packages (MySQL 4.0.22).
OS: Debian Linux on Amd Athlon.
Best regards,
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at
Josh Berkus wrote:
> Ned,
>
>
>>That reminds me of another, related question ... can anyone comment on the
>>relationship between MySQL 5 and MaxDB (formerly SAP DB)? The talk at one
>>point was that the end result of that alliance would be a total rewrite of
>>MySQL to incorporate concepts from MaxDB, and that the new product would
>>somehow be backward compatible with both predecessors.
>
>
> That was the idea, yes. Personally, I don't think it's possible; that's
> probably the reason why MySQL 5.0 is two years late. I think eventually
> MySQL AB will have to come to terms with the idea that they have two seperate
> products, MySQL and MaxDB, and the two can't be made compatible.
>
Absolutely. I personally still hope that adopting MAX DB is their
biggest fault.
Please take some time and look at the MAX DB sources - it is the most
ugly thing I have ever seen in my live.
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at
Hans-Jürgen Schönig wrote: > Robert Treat wrote: > >> >> Mind if i ask which versions of postgresql(&slony)/my$ql these were >> tested against and on what OS ? >> >> Robert Treat > > > > We used PostgreSQL 8.0 RC3 and MySQL Debian packages (MySQL *4.0.22*). > OS: Debian Linux on Amd Athlon. > Might be worth re-doing with Mysql 5.0.2 - just in case they have fixed the issues you found. In addition use of a non production version of Pg and a production version of Mysql in the comparison could be interpreted as a little loaded. regards Mark
On Sat, 8 Jan 2005, Mark Kirkwood wrote: > Hans-Jürgen Schönig wrote: > > > Robert Treat wrote: > > > >> > >> Mind if i ask which versions of postgresql(&slony)/my$ql these were > >> tested against and on what OS ? > >> > >> Robert Treat > > > > > > > > We used PostgreSQL 8.0 RC3 and MySQL Debian packages (MySQL *4.0.22*). > > OS: Debian Linux on Amd Athlon. > > > > Might be worth re-doing with Mysql 5.0.2 - just in case they have fixed > the issues you found. > It might also be worth redoing using PreparedStatements on the JDBC side to see what kind of performance boost that will give. Kris Jurka
Kris Jurka wrote:
>
> On Sat, 8 Jan 2005, Mark Kirkwood wrote:
>
>
>>Hans-Jürgen Schönig wrote:
>>
>>
>>>Robert Treat wrote:
>>>
>>>
>>>>Mind if i ask which versions of postgresql(&slony)/my$ql these were
>>>>tested against and on what OS ?
>>>>
>>>>Robert Treat
>>>
>>>
>>>
>>>We used PostgreSQL 8.0 RC3 and MySQL Debian packages (MySQL *4.0.22*).
>>>OS: Debian Linux on Amd Athlon.
>>>
>>
>>Might be worth re-doing with Mysql 5.0.2 - just in case they have fixed
>>the issues you found.
>>
>
>
> It might also be worth redoing using PreparedStatements on the JDBC side
> to see what kind of performance boost that will give.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
This was just a very brief test to get an impression.
A lot can be improved.
I did not want to use prepared statements because MySQL simply doesn't
provide it. The idea is: If prepared statements are desired they should
be put into the SQL files which are executed - the benchmarking software
itself is a stupid as possible (it is not a JDBC benchmark; all it does
is starting threads and executing any kind of SQL).
We decided to use the RC3 because 8.0 will be out shortly and we don't
expect heavy changes anymore. So you can call this beta test.
While doing the test I got the impression that MySQL has bugs in their
production system PostgreSQL doesn't even have in a random CVS checkout.
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at
Chris Travers wrote:
> One explenation regarding the replication issue....
>
>>
>> lucent@schankserver:~/replication_tests/query$ cat 05.sql
>> BEGIN;
>> UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT';
>> UPDATE t_one SET intvalue = id WHERE id = 'RANDOMINT';
>> COMMIT;
>>
>> BEGIN;
>> DELETE FROM t_one WHERE id = 'RANDOMINT';
>> ROLLBACK;
>>
> Where is Randomint calculated? If it is on the server, I would expect
> the replication to provide unpredictable results with MySQL as it
> replicates statements rather than tuples. Personally, I think
> statement-based replication is a bit of a no-no because if you are doing
> complex non-deterministic queries, this will generate inconsistant results.
It is done by my benchmarking tool.
They do query based replication? Oh man, that really sucks ...
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at