Storage
Mobility
Protection
Analysis and control
TOPICShare
MySQL is one of the most popular relational databases in use today. Whether creating a new MySQL database on AWS or migrating a current environment to the cloud, deciding between a fully managed or a self-managed AWS database might not be an easy decision to make.
In this blog post we compare the benefits, limitations, and tradeoffs between migrating a MySQL database to Amazon Relational Database Service (Amazon RDS), the fully managed service from AWS, or hosting your database on Amazon EC2 Instances. We’ll also see how you can leverage NetApp’s Cloud Volumes ONTAP to make MySQL migration and operation in the cloud easier and more cost effective.
Find out more in these sections:
- About Amazon RDS
- Amazon RDS vs. Amazon EC2-Hosted MySQL
- Cloud Volumes ONTAP for Database Storage Management
About Amazon RDS
Amazon RDS is a fully managed service that enables you to easily deploy and scale a relational database in the cloud, using the AWS Management Console, Command Line Interface, or API calls. Being a fully managed service, Amazon RDS makes database maintenance easier, streamlining day-to-day operations and simplifying tasks such as configuration, backup, and upgrading.
Amazon RDS currently supports:
- MySQL versions 5.5, 5.6, 5.7 and 8.0
- Other databases, such as MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, and the MySQL-compatible Amazon Aurora DB engine
This level of support makes it ideal for most MySQL database migrations and AWS continues to extend their support to additional versions as they mature their service to serve more and more MySQL database migration use cases.
As Amazon is responsible for infrastructure, backups, and updates on the Amazon RDS DB instance, the shell access to the underlying operating system is disabled. 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.
There are a few restrictions to MySQL use. You cannot use Transportable Tablespace, Replication Filters, and Semi-Synchronous Replication. Using Authentication plugin, Password Strength plugin and Group Replication plugins are also unavailable.
These are important limitations to keep in mind if you’re considering an AWS RDS MySQL migration.
Amazon RDS vs. EC2-Hosted MySQL
Amazon Elastic Compute Cloud (Amazon EC2) is a service that provides secure and resizable compute capacity in the cloud. It offers a wide range of Amazon EC2 instances with different hardware characteristics to better suit your needs, on which you can run your database.
Hosting a MySQL database on an EC2 instance, you will be able to choose the underlying building blocks such as operating system, storage settings, and database configuration, giving you full control and flexibility over your MySQL database and surpassing the limitations of Amazon RDS, while also leveraging all the advantages of the AWS Cloud platform and services.
However, having full control and flexibility in an EC2-hosted MySQL database, comes with the loss of the managed features built-in AWS RDS, increasing the responsibility for the day-to-day operations.
Costs: AWS RDS MySQL Pricing vs Amazon EC2
AWS RDS pricing depends on several parameters. The first and largest expense to keep in mind is the running time of the instance, which is charged on a per hour basis. 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 estimated cost for m5.xlarge and db.m5.xlarge instances with 1 TB io1 storage and 3000 IOPS.
As you can see, a fully managed database service comes with an additional premium cost of 30% more (On-Demand cost model) when compared to a self-managed service.
High Availability: Amazon RDS vs MySQL on 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 that 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.
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. The placement of these read replicas enables you to:
- Relocate read operations to the read replica located in the region nearest to your users
- Enhance your disaster recovery capacities
- Migrate your database from one region to another.
On a hosted AWS EC2 MySQL database you can use partial replication, Global Transaction Identifier replication, or traditional statement-based replication for high availability. You can also use Amazon’s AZs and regions, although this has to be manually configured.
On a 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 your Amazon EC2 instance, you can also:
- Install plugins
- Configure semi-synchronous replication
- Configure MySQL (MHA Master High Availability Manager) for automatic failover scenarios.
Backup: Amazon EC2 vs RDS
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, to prevent data loss. To achieve this, you can 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.
Scalability: Amazon EC2 Database vs. RDS
You can easily scale your database to meet your growing application demands. Whether you use Amazon RDS or MySQL hosted on EC2, vertical scaling is accomplished with a click of a button.
When hosted on an Amazon EC2 instance, in addition to the fact that you can optimize its performance by tuning your MySQL database configuration, you can also scale and enhance performance with the great capabilities of Amazon EBS, up to 64.000 IOPS using Provisioned IOPS SSD volume type.
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, however when you are scaling up on a Multi-AZ environment, there is minimal downtime 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 a hosted MySQL database, there is no limitation.
When creating 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 also be promoted into a master; however, it is not a replacement for the high availability that Multi-AZ provides.
Cloud Volumes ONTAP for AWS MySQL 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.
The Added Value to Database Deployment with Cloud Volumes ONTAP
Cloud Volumes ONTAP deployment for MySQL databases combines the benefits of the managed service of Amazon RDS and the full control of self-managed EC2-hosted MySQL, plus the NetApp data management tools to make it easier to protect your data and reduce your costs.
- High Availability: Cloud Volumes ONTAP offers a multi-AZ high availability configuration that uses a synchronous dual-node setup to ensure your database’s continual operation. , ensuring zero data loss (RPO=0) and minimal downtime (RTO<60 seconds).
- Data Protection: NetApp Snapshot™ technology provides instant, application-consistent snapshot backups of your database storage, efficiently saving incremental changes after the original copy is created. Snapshot technology can also be used to mirror the database system, such as for a DR or backup environments, using SnapMirror® data replication on-prem or in the cloud.
- Reduced Costs: Cloud Volumes ONTAP reduces both the footprint and the associated costs for storing your database data in the cloud. Its cost-efficiency technologies include data deduplication, compression, compaction, and thin provisioning. Plus data that is not in frequent use can be tiered automatically from Amazon EBS to Amazon S3, significantly lowering costs of cloud storage. FlexClone® creates instant, writable clone volumes that only consume storage for delta data, a utility not supported by Amazon EBS and Amazon RDS. These instant clone volumes make the dev/test process faster and less expensive.
- Data Mobility: With Cloud Volumes ONTAP and SnapMirror you can make MySQL database migration or replication between on-premises and public clouds easy, whether you’re moving between the data center, AWS, Azure or Google Cloud.
Gaining all these benefits can be a real deciding factor when considering whether to choose AWS RDS vs. EC2-hosted databases.
Conclusion
Migrating your MySQL database to AWS can be extremely beneficial compared to an on-premises solution, since AWS will cover the maintenance and infrastructure aspects, reduce costs and bring inherited features such as high availability and scalability.
Amazon RDS offers relief from the administrative responsibilities automating the entire process of configuration, management and maintenance of your MySQL database, allowing you to focus on more important tasks, yet with some limitations. On the other hand, Amazon EC2 hosting offers complete control and flexibility for your MySQL database, allowing you to use MySQL features that are not currently supported by Amazon RDS, although that does come with some increased operational overhead.
Once you understand the requirements to suit the needs of your application, you would be able to choose the right solution. 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.
Find out more about how Cloud Volumes ONTAP benefits database deployments in our database customer case studies here.
