Actionable advice for databases on AWS
Now that you have a handle on the AWS environment and some of the hurdles you will encounter along the way, here is a targeted list of considerations for your database migration to the cloud, in particular around performance and disaster recovery.
Performance tips
You'll want to manage performance even more aggressively than you already do, to maintain the same level of response your application and users already expect. Here are four tips to help keep your database instances humming:
1. Tune your MySQL server settings. When tuning your server, be sure your tables are InnoDB. Use innodb_file_per_table to avoid contention on a single tablespace datafile. Set innodb_buffer_pool_size to use a large portion of physical memory. Set innodb_log_file_size and innodb_log_buffer_size appropriately. Also set tmp_table_size and max_heap_table_size sufficiently to keep temp tables in memory. In addition, set your per-session controls: sort_buffer_size, join_buffer_size, read_buffer_size, and read_rnd_buffer_size.
2. Tune queries aggressively. This is far and away the most important aspect of keeping your server humming. It also tends to be a challenging one. Start by enabling the slow query log and filtering for queries running under, say, 0.5 second. Lower the threshold as you clean out the biggest culprits and heaviest resource consumers. Use the pt-query-digest tool to summarize and isolate the worst offenders. Add indexes as appropriate, consider multicolumn indexes, covering indexes to avoid additional table lookups.
3. Use xfs and RAID 10 with your EBS volumes. Although ext3 is a very common file system and pretty much the standard for Linux servers, xfs is a much higher-performance file system. It will make a difference. It's a small step when you first set up your volumes and little additional work to maintain after.
In addition you can use software RAID over EBS volumes to mitigate quite a bit against disk I/O throughput problems and variability. As with physical server RAID arrays, EBS volumes will benefit from RAID 10 as much, allowing you to both stripe across a set of virtual disks, as well as swap out disks from the set as necessary.
4. Use Percona server. Percona distributes a distribution of the MySQL server, which is fully drop-in compatible with the Community Edition shipped by Oracle. What's more, the Percona server incorporates additional performance tweaks, bug fixes, and community patches that have often not yet made it to the Oracle shipping version. The server is rock-solid and runs blazingly fast.
Disaster recovery tips
As mentioned, disaster recovery pushes to the foreground when computing in the cloud. You'll want to emphasize automation and take advantage of Amazon's availability zones in your DR plan. Here are three essential tips for keeping your environment online:
1. Automate push-button server rebuilds. As we mentioned previously, virtual instances can and will fail. It's essential that you automate the process of rebuilding those instances. The upside is that this forces best practices around disaster recovery. What's more, you get essentially free disaster recovery, as you don't need to keep those backup servers sitting idle until that fateful day.
2. Replicate to alternate availability zones and regions. Amazon has had real availability zone outages, and customers who've built everything in a single availability zone have been impacted the most seriously by this detail. In the AWS world, you have multiple data centers at your fingertips; use them wisely and you'll build additional resilience into your environment and protect against even the most serious outages.
3. Keep offsite backups of your data. Yes, you want to keep a replicated version of your database, but it's also a good idea to keep regular offsite backups. If you really want to take it to the next level of reliability, engage a second cloud provider, and keep a live copy of your database refreshing there. Then build automation scripts to bring up your entire infrastructure in the second provider's cloud.
This story, "Master MySQL in the Amazon cloud," was originally published at InfoWorld.com. Follow the latest developments in cloud computing at InfoWorld.com. For the latest developments in business technology news, follow InfoWorld.com on Twitter.