TvE 2100

At 2100 feet above Santa Barbara

Why Running Mysql on Amazon EC2 and S3 Is Not That Simple

Jeff posted a blog entry that implies that it’s not that difficult to run mysql on Amazon EC2. Ha! Possible : yes. Easy: noooo! The devil is in the details, many of them!

For example, MyISAM tables do not replicate correctly because the replication (binary) log does not obey transactions. You can have a transaction that rolls back on the master but it’s in the log and happily executed on the slave. Ouch!

Even if you use InnoDB tables, you are not safe. For example, you can write non-deterministic SQL statements that may produce different results on the slave than on the master. An example is creating a table with an auto-increment key using a select from another table. The keys assigned depend on the order in which the select produces results. This may be different on the slave than the master and you end up with different keys, which is not going to match subsequent operations! (A friend ran into this one, one slave matched the master while two others became inconsistent quickly, he spent hours and hours figuring out what went wrong!)

You also assume multiple EC2 instances in your description, but there is no control over their location. They may well be on the same power circuit that goes out. What about the S3 node? I forget the semantic details: when S3 ACKs the store request, does it guarantee that the data is replicated already or does it only guarantee that it is on persistent storage? In other words, could it be on a machine in the same datacenter on the same UPS that goes down with the EC2 mysql box and doesn’t come back up until the UPS is repaired a few hours later? In that case, yes, the data would be safe on S3, but unavailable for a few hours. That wouldn’t help me in quicly restoring from S3 onto a new mysql instance, would it?

Someone else also mentioned restore time on the EC2 forum. How long would it take to restore a 70GB database? (70GB is probably the max you can put on an EC2 instance if you ever want to be able to make a backup copy without jumping through hoops.) My guess is at least 10-15 minutes, and that doesn’t count the time to apply the logs.

Oh, talking about logs, when do you start your logs? You need a clean full backup and then you can start the (incremental) replication logs. After a while, you really should start afresh so you don’t collect endless logs that would take forever to apply. If your app is not 24/7 it’s easy, but if it’s 24x7 you need to do what’s called a hot-backup. Ahhh, no such support in mysql/innodb unless you pay for it.

And performance? Did you know that a mysql slave can easily be slower than the master? The reason is that the slave reads and applies the replication log using a single thread. So while your app server bangs on your master with high concurrency, your slave performs one operation at a time. Usually this is not an issue because the master has the additional load of reads, but the problem does exist.

The bottom line is that in theory everything is available to set-up a nice mysql installation on EC2/S3, but in practice it’s far from easy to actually pull it off in a reliable manner.