DB Administration in MySql is a Cruel Joke

The implementation of ALTER TABLE in Mysql/Innodb is a cruel joke.  Except for RENAMing a table, it's implemented as (1) do a CREATE TABLE to make a new empty table, (2) INSERT all the rows from the existing table into it row-by-row, and then (3) drop the original.

So adding a new index to a table means rebuilding ALL the indexes on the table.  And it's done row-by-row.  That's a super-bogus sorting algorithm.

Want to add a COMMENT to a column?  Sorry have to rebuild the table and all its indexes.

What to add a new column?  Sorry have to rebuild the table and all its indexes.

Any way to parallelize all this rebuilding?  Absolutely not!!!!  No economy of scale whatsoever.  Everything's row-by-row.

There's a reason why folks fork out the big bucks for Oracle Enterprise.  No big bucks available?  Or want to invest them in your business rather than shipping them off to Oracle?  Have a stiff drink and try to relax.

"Scale Out" is not Scalability

MySql, and innodb in particular, don't scale.  Don't plan to run a boatload of concurrent transactions against your MySql db.  I'm hoping things are better in 5.1, but they are dismal in 4.1.

There's just way too much "semaphore" contention.  Too many global mutex locks blocking threads that really aren't stepping on each other.  Too many global mutexes held way too long. Disk i/o done by a thread holding a mutex!

MySql AB claims their product scales.  Their proscribed technique is "scale out".  But their "scale out" scheme is really a workaround for the fact their product doesn't scale.

There's a reason folks pay big bucks for Oracle Enterprise.  Meanwhile, us entrepreneurial types trying to compute on the cheap have to grin and bear it.

Brain Damaged Schema Locking

I have no clue what the motivation is behind mysql's schema level locking algorithm.  It seems to lock things when it doesn't need to but doesn't when it needs to.  These comments apply to versions 5.1 and earlier.

Case in point:  a table can be dropped while a transaction that updated it is still open.

This is a big problem for replication.  It stops replication in its tracks immediately.  Once the open transaction COMMITs, its SQL gets written to the binary log.  The problem is that the DROP TABLE gets written immediately to the binary log.  So the DROP TABLE is in the binary log before INSERT/UPDATE/etc. statements manipulating the table.  Needless to say, replication dies with a nonexistant table error when the latter statements are encountered by the slave.

Why doesn't the schema locking algorithm block the DROP TABLE until the open transaction terminates?  That's what would make sense to me.  (Psst, that's the way Oracle works.)

(This problem with DROP TABLE is a MySql bug reported in 2003!)