Re: MySQL 5 comparison - Mailing list pgsql-advocacy

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