MySQL on AWS: RDS vs EC2

When moving your MySQL instances to AWS, you would probably have to deal with this decision. This article will highlight some of the key factors that need to be considered in order to help you determine which is the most cost-effective solution for serving your data.

Introduction

• Storage engine support
• Plugin-related features
• Tools or operations requiring SUPER privilege
• Replication (topologies, filters, etc)
• Failover
• Lack of access to the underlying operating system

MySQL RDS pros and cons in bullets

Main advantages

1. Instance provisioning time is within the range of minutes
2. Instance type supporting the database server can be easily scaled up and down
3. Storage and IOPS can be scaled up easily
4. Replicas can be provisioned/destroyed automatically and within minutes
5. Backups, snapshots and restores (including PITR) processes are greatly simplified and are fully automated
6. Backups and minor version upgrades are done with minimal impact to production if using Multi-AZ instances.
7. Multi-AZ instances implements synchronous standbys replicas on a different AZ for failover and backups
8. Read replicas on different AWS regions can be easily deployed for load balance and Disaster Recovery purposes

Limitations

1. Once storage is allocated for the instance, it cannot be reclaimed without rebuilding the instance from a logical restore.
2. Access to the operating system is denied, so any operation that requires OS-level access cannot be performed. Many tasks can be run from an external EC2 instance, but not all.
3. Access to MySQL user accounts with ‘SUPER’ privilege is denied. Tasks such as configuring replication that require SUPER are handled through RDS stored routines. Configuring dynamic and non-dynamic MySQL variables are handled through RDS parameter groups.
4. Storage engines other than InnoDB are permitted but not supported and consistent backups / restores are not guaranteed.
5. Read and standby replicas are implemented separately: standby replicas are synchronous but are not accessible for reading while read replicas are asynchronous and even they can be used for failover, they rely on MySQL asynchronous replication and data may be lost in the process. Due to this separation, it is additional cost to get full DR and read-scaling capabilities.

Costs exercise

## MySQL on EC2

-Instances: 3 x m2.4xlarge -Storage: 3 1Tb EBS volumes (provisioned IOPS - 3000 IOPS) + 100Gb/month of snapshots space -Intra-Region Data Transfer: 40Gb/Month

## MySQL RDS

-Instance type: 1 x db.m2.4xlarge + 1 x db.m2.4xlarge (read replica) -Multi-AZ: yes -Storage: 1Tb (provisioned IOPS - 3072 IOPS, one for each instance) -Backup space: 100Gb/month -Intra-Region Data Transfer: 40Gb/Month

The above costs were estimated using AWS simple monthly calculator

Conclusion