Master MySQL in the Amazon cloud

Amazon Web Services offer new challenges and flexibility for database admins -- here's how to avoid the pitfalls and tune for performance

1 2 Page 2
Page 2 of 2

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 tipsAs 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 Follow the latest developments in cloud computing at For the latest developments in business technology news, follow on Twitter.

Read more about cloud computing in InfoWorld's Cloud Computing Channel.

This story, "Master MySQL in the Amazon cloud" was originally published by InfoWorld.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2012 IDG Communications, Inc.

1 2 Page 2
Page 2 of 2