We have a MySQL database on EC2 and are looking at the disk IO performance there. Currently we have a single EBS volume with XFS and take snapshots for backup. It seems that a lot of people have seen significant performance gains by striping across multiple EBS volumes with software RAID. If this is done, how does one take snapshots and ensure the consistency of the file system? It seems to me that restoring the file system from multiple snapshots could be tricky.
Definitely add RAID. EBS volumes will become a bottleneck very quickly. We've had good luck with 8 drives - nice balance of manageability and performance. I recently did some testing with m1.large instances and found that after 8 drives the performance fell off.
I use ec2-consistent-snapshot to make snapshots: http://alestic.com/2009/09/ec2-consistent-snapshot
This is hands down the best way to do it with multiple volumes. It automates the process outlined by Hugh Perkins. It flushes the tables, locks the filesystem, starts the snapshots and then puts everything right again. I find with our system the DB is usually locked for less than 1 second. We do this on both our master and slave machines.
If you want to snapshot the live system EBS volumes:
- Using xfs lets you freeze the filesystems during snapshotting
- lock tables during snapshotting by opening a mysql session and issuing 'flush tables with read lock', and keeping that session open until the ec2-create-snapshot has returned for each volume.
See Running MySql on Amazon EC2 with Elastic Block Store for lots more details about this process.
Alternatively, you might consider:
- replicating the data to a different machine, and snapshotting that instead, which could be a simple task, and avoids impacting the primary machine.
- dumping the entire database to dump files, and either saving those to s3, or saving those to a different ebs volume