Re: MySQL 5 comparison - Mailing list pgsql-advocacy

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
>
>