MySQL Database Migration: Amazon EC2-Hosted vs. Amazon RDS

[Cloud Volumes ONTAP, High Availability, AWS, Database, 11 minute read]
Database-as-a-service (DBaaS) is a cloud service model that enables users to use databases without configuring physical hardware and infrastructure, and without installing software. The service provider is in charge of administering tasks and maintenance—this means the user has less control over specific configurations than when using a self-hosted database. 

Whether you create a new system or migrate your environment and data to the cloud, data residency is always a critical element, but especially when implementing a long-term infrastructure plan. However, deciding between re-hosting your AWS MySQL database on an Amazon EC2 instance or migrating your AWS MySQL database contents to an Amazon RDS instance might not be an easy decision to make.

So which model comes out on top in Amazon RDS vs. EC2-hosted databases? We’ve run some performance tests and cost calculations to provide information we hope will help you in the decision-making process. In this article we’ll take a look at how performing an AWS MySQL database migration hosted on Amazon EC2 compares with AWS MySQL data migration on Amazon RDS, and see how NetApp’s Cloud Volumes ONTAP (formerly ONTAP Cloud) can add value in AWS MySQL database migration.

About Amazon RDS

Amazon RDS enables you to create your database in less than a minute using the AWS Management Console, Command Line Interface, or API calls. Amazon RDS makes database maintenance very easy due to the many possibilities it offers, including automatic AWS RDS MySQL backups and point-in-time recovery, whereas in our own data center we’d have to spend much more time on database maintenance and setup.

Amazon RDS currently supports MySQL versions 5.5, 5.6, and 5.7, as well as other databases, such as MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, and the MySQL-compatible Amazon Aurora DB engine. This makes it ideal for most MySQL database migrations but expect AWS to continue to extend their support to additional versions as they mature their service to serve more and more MySQL database migration use cases.

Amazon RDS Limitations

As Amazon takes care of infrastructure, backups, and updates on the Amazon RDS DB instance, the shell access to the underlying operating system is disabled. Keep in mind that access to MySQL user accounts with the “SUPER” privilege isn’t allowed, and the configuration of dynamic and non-dynamic MySQL variables are handled through Amazon RDS parameter groups.

Unlike a hosted MySQL database, inside the MySQL Amazon RDS instance you cannot use Global Transaction IDs, Transportable Tablespace, Replication Filters, and Semi-Synchronous Replication. Using Authentication and Password Strength plugins are also unavailable. This is totally managed by AWS. These are all important to keep in mind if you’re considering an AWS RDS migration.

Amazon RDS vs. Amazon EC2-Hosted MySQL

If you’re determined to have full control over your AWS MySQL database, AWS provides a wide range of Amazon EC2 instances—with different hardware characteristics—on which you can run your database. Once you perform an MySQL database migration, you will have all the advantages of the AWS Cloud platform and services, as well as the ability to use MySQL plugins and other MySQL features that are unavailable on Amazon RDS.

Performance: Amazon EC2 vs RDS Performance

Which performs better: MySQL database migrations to Amazon RDS or MySQL database migrations to Amazon EC2 instances? A comparison of the performance of self-hosted MySQL databases on Amazon EC2 instances and Amazon RDS MySQL instances can be conducted using the Sysbench tool.

We chose a single Amazon EC2 m4.xlarge instance and an Amazon RDS type db.m4.xlarge instance for the testing environment.
Table 1: Amazon EC2 vs. RDS

We used two scenarios to test the performance:

  • 25 threads an 1 million rows 
  • 50 threads and 2 million rows

Table 2: Amazon EC2 vs. RDS
Table 3: Amazon EC2 vs. RDS

As shown in the tables above, the performance of MySQL on Amazon RDS DB instance is significantly higher compared to the self-hosted MySQL on the same instance type. When it comes to performance in the Amazon RDS vs EC2-hosting matchup, AWS RDS comes out on top.

It’s worth pointing out that we didn’t change the settings inside the my.cnf file on the Amazon EC2 instance.

Costs: Amazon RDS and Amazon EC2 Cost Calculator

Expenses for your Amazon RDS DB instance depend on several parameters. The first and largest expense is the running time instance, which is charged per hour. The price of instance running time per hour is proportional to the Amazon EC2 instance size. Storage space used for a database and its backups, number of I/O requests, and "in and out" internet data transfer for your DB instance are also part of the final expense calculation.

Along with the standard “On-Demand Instances” pricing model, there’s also the “Reserved Instances“ payment model. Reserved instances provide you with a significant discount compared to On-Demand instance pricing. 

The following table shows the cost calculation for m4.xlarge and db.m4.xlarge instances with 1 TB io1 storage and 3000 IOPS.

Table 4: Amazon EC2 vs. RDS

As you can see, it is not surprising that the managed database will cost you 30% more (On-Demand cost model); and this is ignoring backup and additional replicas required.

High Availability: Amazon RDS vs EC2 High Availability

Amazon RDS provides high availability and failover support for DB instances with Multi-AZ deployments. Multi-AZ deployments for MySQL use Amazon technology, while a hosted Amazon EC2 MySQL database you can use partial replication, Global Transaction Identifier replication, or traditional statement-based replication.

If your AWS MySQL database is located on an Amazon EC2 instance for high availability, you can still use AZs and regions, but you'll have to configure it by yourself. On self-hosted MySQL database, replication is asynchronous by default, and in cases where synchronous replication is required you should use a MySQL Cluster (Galera, Tungsten). As you have access to the Amazon EC2 instance, you can install plugins and configure semi-synchronous replication; while for an automatic failover you will need to configure MySQL (MHA Master High Availability Manager).

In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different AZ. To prevent any negative impact of the backup process on performance, Amazon RDS creates a backup from the standby replica. In cases of planned or unplanned outages of your DB instance, Amazon RDS automatically performs a failover and that mechanism automatically changes the DNS record of the DB instance to point to the standby DB instance.

It’s important to note that the high-availability feature on Amazon RDS is not a scalable solution, and you cannot use a standby replica to serve read traffic.
Master Amazon RDS

Image 1. Master Amazon RDS instance and standby replica are in two different AZs

Amazon RDS makes it possible for you to create MySQL read replicas in an AWS region other than the one containing our master MySQL Amazon RDS instance. This enables you to relocate read operations to the read replica located in the region nearest to your users and enhance your disaster recovery capacities, as well as migrate your database from one region to another.

Amazon EC2 vs RDS: Backups and Restore

Unlike the on-premises infrastructure for which you need to perform backup plans manually, Amazon RDS makes it possible to perform automatic backups and manual snapshots of the DB instance. 

If your AWS MySQL database is located on an Amazon EC2 instance then you have to take care of managing Amazon EBS, specifically, automating your MySQL Amazon EBS volume snapshots. However, due to data consistency, you need to perform snapshots when there are no ongoing read operations, otherwise, you might lose some of the data. One method is to use FLUSH TABLES WITH READ LOCK, which will prevent writing while still allowing data to be read from the database. You don’t have to wait until the snapshot is completed; you can release the locks as soon as you start creating the snapshot. You can also automate the process of creating snapshots using Amazon CloudWatch events or the AWS Command Line Interface.

Amazon EC2 vs. RDS: Scalability

DB instance scaling is another functionality that is made far more simple on Amazon RDS. You can vertically scale to address the growing demands of an application that uses a roughly equal number of reads and writes. In an on-premises data center, you must manually upgrade your infrastructure, as well as manually add new components; whereas with Amazon RDS or MySQL hosted on Amazon EC2 instance, vertical scaling is accomplished with a click of a button. 

When your database is on an Amazon EC2 instance, in addition to the fact that you can optimize its performance by altering MySQL configurations such as innodb_buffer_pool_size, innodb_flush_neighbors, innodb_log_file_size, you can also scale and enhance performance with the great capabilities of the Amazon EBS volume. 

For database workloads, we will focus on solid state drives (SSDs), Amazon EBS General Purpose SSD (gp2), and Amazon EBS Provisioned IOPS SSD (io1). As for gp2 volumes, volume size determines how many IOPS we’ll have in the 3:1 ratio. For example, a 100GB gp2 volume can have maximum of 300 IOPS. With Amazon EBS gp2, the most IOPS we can have per volume is 10,000 IOPS. Amazon EBS Provisioned IOPS storage (io1) is high-performance storage designed to deliver fast and predictable consistent performance for very intense DB workloads. Io1 storage enables us to define the number of IOPS, regardless of the volume size. The maximum number of IOPS per io1 volume is 30,000 IOPS.

Thanks to an Amazon EBS feature called Elastic Volumes, you can increase volume size, adjust performance, or change the volume type while the volume is in use. And, your database on Amazon EC2 will continue to operate normally. With Amazon RDS, you can independently scale CPU, memory, storage, and IOPS. There is minimal downtime when you are scaling up on a Multi-AZ environment because the standby database gets upgraded first. Horizontal scaling can be performed by using read replicas in order to enhance performance of read-heavy applications. On Amazon RDS, you can have up to five read replicas, while for hosted MySQL database, there is no limitation. 

When you create a read replica, you specify an existing DB instance as the source. Amazon RDS takes a snapshot of the source instance and creates a read-only instance from the snapshot. MySQL Amazon RDS uses native asynchronous replication to update the read replica. A read replica can be promoted into a master; however, it is not a replacement for the high availability that Multi-AZ provides.

Cloud Volumes ONTAP for Database Storage Management

In cases where MySQL is on an Amazon EC2 instance and the maximum io1 IOPS per volume isn’t enough, we can turn to NetApp’s Cloud Volumes ONTAP for AWS. Cloud Volumes ONTAP offers easy management of multiple storage pools of varying IO performance rates and supports all database types: SQL Server, SQL DB, Oracle, and more.

Cloud Volumes ONTAP can use multiple Amazon EBS volumes in a RAID 0 configuration to increase the number of IOPS you can receive out of a single storage pool. That’s something that Linux software can do, but Cloud Volumes ONTAP lets you do much more.

Added Value with Cloud Volumes ONTAP

Cloud Volumes ONTAP’s main benefits come from the features that make it easier to protect your data and save on costs while doing so. 

Cloud Volumes ONTAP HA for AWS offers a high availability configuration that is self-aware and capable of self-healing to make sure no unexpected outages will affect your database’s operation through automatic, seamless failover and failback to an identical redundant node. Both nodes are in synchronous replication and may reside in different Availability Zones.

Cloud Volumes ONTAP, in conjunction with NetApp SnapCenter®, provides the capability to perform instant, application-consistent snapshot backups of your database storage. These snapshot backups can be instantly restored using the Cloud Volumes ONTAP SnapRestore® feature. Snapshots can also be used to mirror the database system, such as for a DR environment, to another Cloud Volumes ONTAP storage system using SnapMirror®, or archived for backup redundancy using SnapVault®.

Cloud Volumes ONTAP also lowers AWS storage costs, making it less expensive to store your database. It makes use of powerful data deduplication, compression, and thin provisioning storage efficiencies, and it can perform data tiering to Amazon S3, which can lower storage costs for use cases such as DR to as low as $0.03 per GB per month. Costs can also be lowered through the use of FlexClone® flexible clone environments to quickly create temporary dev/test environments.

Cloud Volumes ONTAP also makes lift-and-shift migration easy, and leverages SnapMirror to replicate between on-premises and Cloud Volumes ONTAP systems. Its more efficient snapshots only copy the incremental changes, which makes them faster to create and more efficient than Amazon EBS snapshots which makes an initial full copy of the entire volume. With Cloud Volumes ONTAP HA, you even have the ability to have those snapshots automatically stored in another Availability Zone, adding another way of protecting your database from unexpected outages.Gaining all these benefits can be a real deciding factor when considering whether to choose AWS RDS vs. EC2-hosted databases.

Conclusion: Amazon EC2-Hosted or AWS RDS MySQL?

When your database is hosted on AWS, you get some relief from the headaches that come with hosting a database on-premises. But this also means losing some flexibility as some of the MySQL features aren’t available on Amazon RDS. 

So who comes out on top when it comes to Amazon RDS vs EC2-hosted MySQL databases? Is Amazon RDS the right solution for your workload or is Amazon EC2 hosting the better option? It depends. It depends. There is no one-size-fits-all solution—each company has its own needs and constraints. That said, if a business is set on moving to the cloud, an MySQL database migration is worth considering, especially when you take into account the available cost-effective options. Whichever format you choose for your database on AWS, consider the added value that comes with using Cloud Volumes ONTAP to lower storage costs and add an extra layer of protection. 

For a less-expensive way to protect and store your AWS MySQL database, try Cloud Volumes ONTAP with a free 30-day trial on AWS.