Getting the most out of MySQL in the Amazon Cloud

Best Practices for Using MySQL in EC2

Using MySQL in the Amazon cloud is only slightly different from using MySQL on your own hardware. If you use an AMI with MySQL installed and configured, you can quickly connect your data on your EBS volumes and launch a server. Or you can create your own AMI with a specific MySQL configuration to automate some of the minor setup needed to point the datadir to the EBS volumes.

What, then, are the best practices for using MySQL in the cloud? The following lists a few of the more common practices that have been determined to give you the best performance using MySQL in the cloud.

• Run only one MySQL Server per EC2 instance - MySQL will run faster with dedicated computational units and memory resources. Given the ease and relative low cost of creating new instances, you will see greater performance by running a single MySQL instance in a smaller instance type then by attempting to run multiple MySQL instances in a larger instance type. Remember, you can add as many instances as you need any time your need them, allowing you to scale out or scale back at will.

• Leverage larger instance types for heavy usage - You should consider using larger instance types for high transactional or heavy read or write databases. The larger instance types not only have more computational units and memory, they also have higher I/O throughput capabilities. In this case, paying a little extra is worth the cost.

• Use additional EBS volumes for your databases - Databases are typically I/O hogs and can become I/O bound. Use a separate EBS volume to store your InnoDB files from your other MySQL table files (e.g. datadir) for better I/O performance.

• Warm up data partitions - There is one drawback to using disk IO in EC2: a “first write” performance hit when initially writing to new partitions. To avoid this penalty, you can “warm up” the partition by executing a sort of throw-away command that accesses it. For example, you can use the Linux dd command to write to the disk. While the penalty still occurs and cannot be avoided, at least the first write to your databases will not suffer the effects.

• Be sure to configure MySQL properly - Simply running MySQL in EC2 isn't going to make it faster. You cannot skip the configuration and fine tuning of the MySQL system to meet your specific application needs. Doing so will allow you to better utilize EC2's resources and economy of resources.

• Don't forget monitoring - You can and should monitor your MySQL servers running in the cloud, using your choice of the methods discussed in this book. You should also use the Amazon cloud instance monitoring tools to keep tabs on your servers. Just because they're virtualized doesn't make them immune to runaway queries and similar performance problems.

• Use MySQL replication - We have seen in previous chapters how important MySQL replication is for scale out, load balancing, and high availability. The cloud makes these features easier to use because you can create as many MySQL instances as you need to implement almost any MySQL high availability solution.

• Use standard AMIs - It's powerful to have the capacity to build your own AMIs from scratch. Unfortunately, unless you have vast experience in running the host operating system in a virtual environment (or start with a known stable AMI and make only minor changes), the task of building custom AMIs can be time consuming and error prone. Whenever possible, use the existing Amazon AMIs or known stable community AMIs.

• Use good security practices - The cloud is no different from any other Internet-connected device. You should always implement well-documented security protocols for MySQL (e.g., don't leave your root account password blank). However, you should also restrict access to the EC2 instance such that only authorized users (and systems such as replication slaves) can access your virtual systems. EC2 contains a firewall preconfigured to restrict inbound traffic. Some AMIs have certain ports open like 3306 for MySQL clients, but you must explicitly open the additional ports you need. Use custom security groups to manage common rules across a group of EC2 instances.

• Mount partitions with the noatime and nodiratime options - Mounting your partitions with either of these options should yield up to 10% better I/O performance. This is because Linux will not have to perform a write operation after every read access. It should be noted that noatime is a superset of nodiratime.

• Use EBS with MySQL - As we mentioned previously, EBS is a block store device with good performance and sustainability from instance failures, as well as elasticity. Not only do you get persistent storage for your data and log files, you can also fail over to another server in the event that your instance terminates.

• Perform snapshotting using S3 - You can leverage EC2’s ability to make a snapshot of your volumes and store them on S3 as part of your backup strategy. Snapshots are an effective and efficient way to take a backup and provide a mechanism for rapid recovery in the event that data becomes corrupt.

• Use load balancing - Applications with high loads or a high number of concurrent connections can benefit from load balancing. As we described in previous chapters, you can use MySQL replication across multiple MySQL slaves to improve read performance and use sharding to improve write performance. You can use the Amazon Elastic Load Balancing resource or even run your own software load balancer such as HAProxy. For more information about HAProxy, see: http://haproxy.1wt.eu/