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
RDS is based on EC2, so in this post I will focus on the benefits and disadvantages of using the former, versus migrating MySQL into user-managed EC2 instances.
The value of RDS resides in simplified provisioning and administration. Several of the most common maintenance tasks such as minor version upgrades, backups, and slave creation are automated and can be managed from the AWS console. On the other hand, RDS imposes some limitations to MySQL functionality so it could be offered “as a Service” while delivering a safe and consistent user experience. Within those limitations we can mention:
- 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
- Instance provisioning time is within the range of minutes
- Instance type supporting the database server can be easily scaled up and down
- Storage and IOPS can be scaled up easily
- Replicas can be provisioned/destroyed automatically and within minutes
- Backups, snapshots and restores (including PITR) processes are greatly simplified and are fully automated
- Backups and minor version upgrades are done with minimal impact to production if using Multi-AZ instances.
- Multi-AZ instances implements synchronous standbys replicas on a different AZ for failover and backups
- Read replicas on different AWS regions can be easily deployed for load balance and Disaster Recovery purposes
Limitations
- Once storage is allocated for the instance, it cannot be reclaimed without rebuilding the instance from a logical restore.
- 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.
- 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.
- Storage engines other than InnoDB are permitted but not supported and consistent backups / restores are not guaranteed.
- 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
A quick estimation comparing RDS and EC2 for similar instances and storage shows that RDS is between 33 and 44% more expensive than EC2:
## 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
|
On-demand instances |
USD $3465 per month |
|
Reserved instances (1 year, partial upfront) |
USD $1490 per 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
|
On-demand instances |
USD $4633 per month |
|
Reserved instances (1 year, partial upfront) |
USD $2155 per month |
The above costs were estimated using AWS simple monthly calculator
Conclusion
There is no right or wrong decision when choosing which AWS solution is better for MySQL. RDS is a powerful option if your data is already in InnoDB (and only in InnoDB) and you are looking for a simple, low-maintenance deployment. When other engines and more complex topologies come into the scene, or even the need for MySQL plugins, then going to EC2 is a better option. Any critical process or script running locally on a MySQL instance could also be the decisive factor for choosing EC2, usually because of the time spent in moving the data through the network. Finally, the cost is definitely a key factor, especially when moving several instances into RDS.
10 Comments. Leave new
Hi,
For 100EUR per month you can rent two dedicated servers interconnected via VLAN each with 64GB RAM and configure HA solution. It is 1200 EUR per year. You can run Mysql replication and do whatever you want. Why do you need amazon?
Regards
AWS will help for scaling ( both Rds n ec2) / spin off new set of replicas with multiple clicks (Rds). We can avoid downtime in Rds for instance upgrades .. ec2 required minimal downtime (we can do rollover strategy to minimize the downtime). Dedicated servers will help for static environment for sure.
New replicas are scaling it read-only and your application needs to support splitting reads and writes to different servers.
What if you need to scale it vertically to increase the number of writes?
You need to change instance type and you can’t do it online, if you don’t have the 2nd failover node running it could take 10 minutes easily.
With the EC2 or dedicated server no downtime upgrade can be achieved using for instance mastermaster replication. Something what RDS doesn’t support.
Why do you remove comments?
Hi Todor, no posts were removed but all go through moderation to prevent inappropriate or subjective comments that don’t add value to the discussion. Some posts may take longer to be displayed because of this. Thanks
Masters and read replicas need to use the same instance type ??
No , we can have different instance types for replicas.
But Rds won’t allow the replica higher Mysql version than master. (Master 5.6 , replica also need to be 5.6 not possible as 5.7).
We cannot downgrade MySQL version in Rds for any reason.
Thanks for pointing that out Naveenkumar. Post adjusted
just for the record
it looks like cost calculation in this article is not correct for RDS instance (ondemand pricing)
MySQL RDS (setup and price form the article)
-Instance type: 1 x db.m2.4xlarge
-Multi-AZ: yes
-Storage: 1Tb (provisioned IOPS – 3072 IOPS)
-Backup space: 100Gb/month
-Intra-Region Data Transfer: 40Gb/Month
On-demand instances USD $6557 per month
———–
According to AWS monthly calculator MySQL on demand instance on Amazon RDS 2812$/mont
https://calculator.s3.amazonaws.com/index.html#r=IAD&s=RDS&key=calc-F130959F-D124-4B2A-82B6-23F099CE2F73
Thanks for pointing this out Ofir. My goal was to provide a cost estimation for RDS “similar” to a master-slave three node setup on EC2 so I considered a second RDS instance to offload reads and for failover purposes (the third instance would be covered by the multiAZ offline copy). Of course I failed to clarify this on the my post. Fixed now.
It is totally depended on your team skills, If you have a skilled team who can handle all MySQL operations including backups, replication and all, go for the EC2 and if you don’t have a skilled database team and you want to continue with your existing team, you should go for RDS, who can easily manage all database operation by just click on RDS console than you can actually compare extra pricing of a Managed service and a salary of a Database Administrator.