Re: MySQL 5 comparison - Mailing list pgsql-advocacy

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