What's That Noise?! [Ian Kallen's Weblog]

All | LAMP | Music | Java | Ruby | The Agilist | Musings | Commute | Ball
Main | Next day (Jun 5, 2004) »

20040604 Friday June 04, 2004

Database Replication and the old Beta versus VHS thing I've been plugging up holes in some application problems relating to MySQL. It's not my favorite database but then life is full of compromises. I really like FreeBSD better but the sheer momentum around Linux development is impossible to ignore. So I use it and make the best of it. However, if there was a native JVM for FreeBSD that was as upto date as Linux' I'd consider switching back.

But seriously folks, there are a lot of things that are just toyish about MySQL. In the same way that programming language features like Object Oriented Programming shouldn't be sad after thoughts as it is in Perl and PHP, basic database functionality (foreign keys, transactions and subqueries) shouldn't be the "new features" for a database. I've always liked PostgreSQL for its more complete SQL implementation but it just doesn't seem to have the momentum behind it that MySQL does.

Anyway, my favorite MySQL bug (this is on MySQL 4.0.18) has got to be the silent failure of replication. There are slave database instances whose replication status I assumed were monitorable by doing "SHOW SLAVE STATUS" and observing "Slave_IO_Running" and "Slave_SQL_Running" (each attribute represents a thread that manages the binlog IO and SQL execution on replicated units, respectively) on each of them. Well guess what? That's not sufficient. Both threads can claim to be running and you might even observe the execution positions changing in sync between master and slave. But lo and behold, the real measure of whether or not the MySQL replication is working correctly is to query your application data! For instance, if the timestamps and/or sequences for some key tables are advancing in the master but not the slave, you're hosed. You might need to myisamchk the slave's tables. You might need to simply restart the database slave instance. You might need to ceremonially sacrifice a chicken. Perhaps a little bitch slappin' and sweet whispers will get it going. I dunno. The bottom line is: MySQL might report that it's replication threads are running and its positions are changing but *SURPRISE* your data isn't really updating on the slave! I need replication support for high availability and read concurrency but bugs like that just suck the big one.

So what is to be done? Does MySQL 4.1 fix this crap? Do InnoDB tables replicate more reliably than MyISAM? Is PostgreSQL even an option here? I don't know anybody using PostgreSQL replication. Sometimes when I read the comparisons between MySQL and PostgreSQL, it just makes me wanna throw my hands up in the air and move over to the The Dark Side (AKA Oracle).

Here are some links:

Perhaps citing these out of date sources amounts to FUD. But I don't think so. For me it indicates how much momentum MySQL has... it's not a better technology but the MySQL peoples have managed to leave old critiques behind them and press ahead with after-thought-ish feature additions. VHS won the videotape format wars because it had momentum, not 'cause it's better. I'd be happy to see newer comparisons, contrasts and benchmarks but this is all I've got handy.

( Jun 04 2004, 01:54:11 AM PDT ) Permalink
Comments [2]