Re: MySQL 5 comparison - Mailing list pgsql-advocacy

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