|
|
 | | From: | Ned Lilly | | Subject: | MySQL 5 comparison | | Date: | Wed, 05 Jan 2005 10:42:23 -0500 |
|
|
 | 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | Robert Treat | | Subject: | Re: MySQL 5 comparison | | Date: | 06 Jan 2005 09:42:15 -0500 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
|
| | From: | =?UTF-8?B?SGFucy1Kw7xyZ2VuIFNjaMO2bmln?= | | Subject: | Re: MySQL 5 comparison | | Date: | Fri, 07 Jan 2005 10:57:34 +0100 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
|
| | From: | Mark Kirkwood | | Subject: | Re: MySQL 5 comparison | | Date: | Sat, 08 Jan 2005 12:27:09 +1300 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
|
| | From: | Jeff Davis | | Subject: | Re: MySQL 5 comparison | | Date: | Wed, 05 Jan 2005 17:37:19 -0800 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
|
|
| | From: | David Fetter | | Subject: | Re: MySQL 5 comparison | | Date: | Wed, 5 Jan 2005 18:01:39 -0800 |
|
|
| 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!
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
|
|
| | From: | =?windows-1252?Q?Hans-J=FCrgen_Sch=F6nig?= | | Subject: | Re: MySQL 5 comparison | | Date: | Thu, 06 Jan 2005 11:03:33 +0100 |
|
|
| 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
|
|
| | From: | Christopher Kings-Lynne | | Subject: | Re: MySQL 5 comparison | | Date: | Thu, 06 Jan 2005 13:25:02 +0000 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | Christopher Browne | | Subject: | Re: MySQL 5 comparison | | Date: | 6 Jan 2005 02:53:52 GMT |
|
|
| 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
|
|
| | From: | Christopher Browne | | Subject: | Re: MySQL 5 comparison | | Date: | Wed, 05 Jan 2005 21:20:03 -0500 |
|
|
| 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
|
|
| | From: | David Fetter | | Subject: | Re: MySQL 5 comparison | | Date: | Wed, 5 Jan 2005 15:23:46 -0800 |
|
|
| 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!
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
|
| | From: | Kris Jurka | | Subject: | Re: MySQL 5 comparison | | Date: | Fri, 7 Jan 2005 18:44:11 -0500 (EST) |
|
|
|
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
|
|
| | From: | Hans-Jürgen_Schönig | | Subject: | Re: MySQL 5 comparison | | Date: | Sat, 08 Jan 2005 10:48:55 +0100 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
|
|
