05.26.07
What doesn’t work well with MySQL
MySQL is a great database. Packed with tons of features. Very easy to set up and get running. Easy to maintain. But it does have some serious drawbacks if you want to set up enterprise databases. I’m presenting my opinions about the MySQL 5.0 release of the database, based on my direct experiences with managing several medium-sized databases
Immature stored procedures
Beginning with MySQL 5, developers and DBAs now have access to triggers and stored procedures. These two key pieces of technology have been in other databases for years where they’ve had time to mature and grow with the product. MySQL’s implementation hasn’t and there will be growing pains to go along with it.
Backup and restore
Obviously, backing up and restoring a database is a critical business continuity function. The mysqldump utility is very flexible when it comes to backing up a MySQL database. You can be very picky when it comes to the database, the tables it backs up, even using a WHERE clause to get more fine-grained with the data. It generates straight SQL statements with some special MySQL extensions enbedded in comments. To restore, you simply pipe the dump file into the mysql client program and it’ll execute the SQL statements in order and rebuild the database/tables/rows. There are a couple problems with this approach:
- If you use the MyISAM storage engine you may encounter what I call “the back up lock up.” The MyISAM storage engine only supports locking at the table level. This means that when you make a backup, you lock all the tables for read access (so the dump is consistent across all tables being backed up) while mysqldump does it’s thing. This means your application could come to somewhat of a halt, depending on how well it handles not being able to write to the database or prolonged pauses. You can, of course, choose to not lock the tables, but then you have no guarantees that the data across tables is consistent. Fortunately, mysqldump is very fast. For small databases (a couple hundred megs or smaller) mysqldump will lock the tables for a minute or less. For a 5 gig database, expect to be locked for 5-10 minutes.
- Now that you have a backup, you may need to restore it from time to time, either on a developer/QA box to test new code, to verify the backup/restore process works (you *are* doing this, aren’t you?) or because a hard drive crashed and you need to restore the data on the production database. Again, the storage engine comes in to play. MyISAM tables can be written to very, very fast and a restore of hundreds of megs worth of data can be done in a matter of minutes. The InnoDB engine, with its transaction and foreign key support, takes quite a bit longer. A 5 gig dump file will take hours to restore.
The commercial database players have more sophisticated means of performing backups and restores that don’t suffer from these issues.
Relatively few who really understand how MySQL works
With the “big boys” in the database arena, there are a good number of highly qualified DBAs out there who know how to run the system and make it purr like a kitten. There are certification programs, conferences, books, people who “grew up” with particular databases, etc. With MySQL, you’ll find a lot of folks like myself - admins or developers who know how to set up and the basics of MySQL, but aren’t real DBAs. We got that title out of necessity (no one else available to do it.) For true enterprise implementations, you need someone who eats, sleeps, breathes MySQL 36 hours a day. MySQL AB does offer training so it’s only a matter of time before there is a large pool of MySQL DBAs out there.
Replication works to a point
MySQL has a simplistic replication feature that allows you to easily maintain copies of data on multiple database systems. In fact, its commonly used to build a “cluster” of read-only databases that an application queries from. The app will need to write to the master database, of course, but this gives you the ability to scale out your capacity to query data. The problem with replication is, when you have a database that gets a lot of INSERT/UPDATE/DELETE queries, you can find yourself in “replication lag,” where the replication slave databases fall behind the master. This happens because replication only uses 2 threads: one to read the data from the master and one to write it to the local data files. On the master, there are several threads writing to the data files but on the slave, there is only one (because replication works at a different level than regular database connections.) More processors won’t help, only faster processors and faster disk drives. You’re only other option is to replicate only a portion of the data but that may defeat the purpose.
Can MySQL be used on the enterprise? Absolutely. Should it be used in the enterprise? Absolutely. You need to examine where and how it will be used and plan accordingly. There are many big name companies out there using MySQL to run business-critical applications (Google uses it to run their company financial systems, Yahoo uses it a lot behind it’s web sites.)
You will get out of MySQL what you put into it. If you spend the money for a quality hardware platform and hire a high quality DBA, you can go far with MySQL. If you put it on generic hardware with little to no redundancy and let your overtaxed IT department (or a developer) manage it, it’s going to be a pain point for you. MySQL AB, the company behind MySQL, offers a commercial license and top notch support. While you’ll never “go it alone” due to the large community around MySQL (there are quite a few forums and web sites dedicated to MySQL out there,) having a phone number to call can be quite a time saver. And MySQL developers typically do the support for the commercial licensees, so it doesn’t get much better than that.
