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

By Sean Hull, InfoWorld |  Cloud Computing, Amazon Web Services, MySQL

In AWS, a systemwide solution called ElastiCache is available. This technology is built on memcache but does not require individual configuration, monitoring, or tuning. Simply configure your application for this object cache -- you're off and running. Scalability is built in as a bonus.

Caching queries: Oh those queries, requiring heaps of attention to get them just right and as much attention to keep them working properly! That's why the query cache should be another tool in your arsenal of performance equipment. Be sure to have it enabled in MySQL and configured for sufficient memory so that useful queries are not expiring before their useful life has ended.

Caching in the query cache will keep the query plan and data in one place. If the database finds an exact match of a query it's already run in the past, it'll send it back to the Web server without doing any real work at all.

Caching database data: The MySQL database contains a very important setting called the buffer cache. This caches all index and table data. Since data that is previously cached will incur only logical reads, you'll avoid a ton of I/O to your data files. As we've mentioned, because of the challenges of EBS virtualized storage, this is doubly important in the Amazon AWS environment.

Scaling the database tierThe Web server tier can be set up with autoscaling in AWS because each of the server's data is not changing dynamically. Sure, some files and images are uploaded or new code deployed, but this happens intermittently.

Scaling the database tier, however, typically involves a bit of finesse and careful execution as it poses specific challenges. The most widely used method is to maintain a single master database. This database receives all changes, such as Insert, Update, Delete statements from the application. All Select activity from the application gets sent to one or more slaves. These can be regularly reimaged, so you always have the latest copy of your MySQL slave as an image ready. When you need to add read capacity, simply spin up the latest image, the instance will start, and MySQL will start and connect to the single master just as the others do.

If capacity on the master database is waning, you can vertically scale that server. Set up a new, larger EC2 instance in AWS, then set it to replicate off the master. Set all of your existing slaves to point to the new master, then failover the application to the new, larger instance. Voilà, you've scaled vertically with zero downtime.

Actionable advice for databases on AWSNow 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.

Originally published on InfoWorld |  Click here to read the original story.
Join us:






Answers - Powered by ITworld

ITworld Answers helps you solve problems and share expertise. Ask a question or take a crack at answering the new questions below.

Ask a Question