TvE 2100

At 2100 feet above Santa Barbara

Redundant MySQL Set-up for Amazon EC2

In order to deploy web sites/services onto Amazon EC2 everyone needs the same components, and so we’re building them! One of the most requested and most critical pieces is a good database set-up, and mysql is clearly the highest in demand. Not that a good postgresql or oracle set-up wouldn’t be of interest or would be equally possible, just that more people are asking us (and paying us) for mysql…

What we’ve built is a mysql master/slave set-up with backup to Amazon S3. The set-up consists of one mysql master server which is the primary database server used by the application. We assume it runs on its own EC2 instance but it could probably share the instance with the application. We install LVM (linux volume manager) on the /mnt partition and place the database files there. We use LVM snapshots to back up the database to S3, this means that we get a consistent backup of the database files with only a sub-second hiccup to the database.

MySQL master and slave

Well, the snapshots for backup are actually quite a bit more complicated than that. We have to acquire a read-lock on all tables and this could block things if there is a long running query ahead of us. So there’s a timeout and retry loop which needs to balance off locking up the database and getting the backup done.

Using the snapshot backup we set-up a slave instance which then starts replicating in real-time from the master. This means that all changes to the master are propagated with milliseconds of delay to the slave, so should the master instance fail, there’s an up-to-date backup. On a master failure we promote the slave to master and set-up a fresh slave. Note that in most databases the slave lags extremely little behind the master. The main situation where the slave starts lagging is when there is a lot of write activity going on in the master. Under heavy write load the slave is slower at applying the replication to its copy than the master on the same hardware because the slave uses only a single thread to apply all changes while the master has one thread per client connection, so it can overlap network communication, cpu processing, and disk I/O using multiple threads, which the client can’t.

Periodic backups are taken off both the slave and master instances. There is very little penalty for acquiring a read lock on the slave and performing the snapshot and subsequent back-up, so it can be done every few minutes without any real impact (unless the slave has trouble keeping up as described above, in which case it’s probably time to move to multiple slaves). We also take infrequent backups on the master, say once a day, in order to guard against any problems introduced by replication.

While the mysql replication is well proven and used by many large sites in heavy production, there are failure scenarios. First of all, the application should use Innodb tables exclusively because myisam tables are not transactional and have a number of scenarios where replication fails. Even with innodb tables there are failures possible. For example, it is possible to write non-deterministic queries in SQL and since mysql uses logical replication the slave re-executes the query, and it may end up using a different execution order than the master, resulting in different data in the database. Ouch. One example is a create table with an auto-index key using a select from an existing table. The insertion order and hence the keys in the new table depend on the order in which the select is executed, and if it’s executed in a different order in the slave from the master you will end up with an unusable slave DB! (Been there, done that, it still hurts.) Thus: do back-up your master every now and then to be able to recover from such problems. (If you’re paranoid, fire-up an instance every few hours, load up a back-up, and run a few consistency checks – it’ll cost you less than a buck a day to ensure the DB backup is good, that’s cheap insurance.)

The best of all is that all the goodies described above are controlled through the RightScale web interface. Want a new slave? Just press the “set-up slave” button! Want a back-up, just press “backup” on the master or on the slave. The list of functions we have now are: * launch database instance * restore from S3 backup and configure as master * configure as slave, using DB transfer from master for initial state * promote slave to master * backup to S3 * daily backups to S3 from master * 10-minute backups to S3 from slave

We obviously still have a lot of work ahead of us to improve the flexibility of the set-up. One thing to note is that you are in control of what is executed on the database servers, so they are not opaque virtual appliances. If you need to tweak our database install, slave set-up, backup, or other code, it’s all available in scripts that you can modify. (Of course the more you modify the less we can help when things go wrong.) Also, currently all these functions are “automated” in the sense that you make a decision, push the right button, and things happen. We are adding monitoring and we will add triggers that will cause master-slave failovers automatically.

If you are interested in using our mysql master/slave set-up, please contact us at sales@rightscale.com. This stuff is not available with the free RightScale accounts.