Re: Need concrete "Why Postgres not MySQL" bullet

On 20 Aug 2003, Harald Fuchs wrote:

> In article <200308200839.28230.josh@agliodbs.com>,
> Josh Berkus <josh@agliodbs.com> writes:
>
> > PROCEDURES:  Postgres supports stored procedures (as functions) allowing
> > programming in the database for the many tasks which are far more efficient,
> > consistent, and secure done there.   Procedures may be written in any of nine
> > different languages, currently, with two more in development.  MySQL does not
> > support procedures at all.
>
> >From the MySQL manual:
>    * With UDF (user-defined functions) one can extend MySQL Server with
>      both normal SQL functions and aggregates, but this is not yet as
>      easy or as flexible as in PostgreSQL.
>
> > TRANSACTIONS:  blah, blah, blah.   MySQL has just begun offering transactions
> > this year, and their solution is largely untested, slow...
>
> InnoDB transactions in MySQL are pretty robust and fast.  However,
> this affects only INSERT/UPDATE/DELETE - not CREATE TABLE etc.

Well, I wouldn't say that they're that robust.  Try this:

create innodb table
begin transaction;
insert 1,000,000 rows;
rollback;

wait for years for the rollback to finish.

From the MySQL manual:

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#Innodb_tuning

QUOTE

8:  # Beware of big rollbacks of mass inserts: InnoDB uses the insert
buffer to save disk I/O in inserts, but in a corresponding rollback no
such mechanism is used. A disk-bound rollback can take 30 times the time
of the corresponding insert. Killing the database process will not help
because the rollback will start again at the database startup. The only
way to get rid of a runaway rollback is to increase the buffer pool so
that the rollback becomes CPU-bound and runs fast, or delete the whole
InnoDB database.

ENDQUOTE

It's obvious that innodb transactions aren't meant to handle large data
sets and rollback well, and the compromise here, like in all of MySQL,
tends towards "hoping for the best" and benchmarking that particular
aspect.

> > and suffers from
> > complications with the many different "table types".
>
> True.  Transactions break unless all tables used are InnoDB.

And, more importantly, they break silently, or you find out too late (i.e.
oh by the way, some of those rows couldn't be rolled back...)