Supercharge your MySQL database on AWS

Over the past 15 years, cloud-managed databases have evolved to allow you to take advantage of the cloud’s agility, scalability, and flexibility in architecture. Open source databases have accelerated this transformation as an essential part of a cost-effective, modern data strategy. Amazon’s journey with managed open source databases started in 2009 with the launch of Amazon Relational Database Service (Amazon RDS) for MySQL. MySQL is one of the most popular open source database engines that fueled the early internet days and continues to remain a preferred choice for several enterprises looking to build straightforward and intuitive web applications. AWS has a rich history of innovating in the MySQL space, and this post showcases our capabilities and how we make it come together behind the scenes.

AWS’s innovation for MySQL

The launch of MySQL as the first database engine on Amazon RDS was followed by the launch of Amazon Aurora MySQL-Compatible Edition and Amazon RDS for MariaDB in 2015. Amazon RDS for MySQL frees you to focus on application development by managing time-consuming database administration tasks, including backups, upgrades, software patching, performance improvements, monitoring, scaling, and replication. Amazon Aurora is a relational database management system built for the cloud with full MySQL and PostgreSQL compatibility. Aurora gives you the performance and availability of commercial-grade databases at one-tenth the cost. As a result of years of innovation, hundreds of thousands of customers are running their MySQL databases on AWS. AWS has more than 1 million instances running managed MySQL. We continue to observe that customers are migrating existing MySQL workloads and building new workloads using managed MySQL on AWS. The following figure shows a brief history of AWS’s innovation with managed MySQL since its inception in 2009.

With Amazon RDS for MySQL, you get compute coupled with storage with the option to create as many as 15 read replicas per primary instance and up to 155 replicas with cascaded replicas. You can benefit from as much as 99.95% availability with Multi-AZ configurations. When you choose a Multi-AZ configuration with one standby, Amazon RDS control plane deploys a secondary standby instance with physical synchronous replication between the primary Amazon Elastic Block Store (Amazon EBS) volume and secondary EBS volume and failover within 1–2 minutes. The following diagram illustrates the Multi-AZ architecture.

You can use Multi-AZ with two readable standbys, providing you additional read capacity from secondary instances, reduced failover times between 25–75 seconds, and improved write performance. For more information, refer to Amazon RDS Multi-AZ with two readable standbys for RDS PostgreSQL now supports inbound replication.

Our customers told us that they had scaling and lifecycle management challenges with traditional self-managed databases. This is why we innovated and launched Aurora, where compute and storage are decoupled, allowing you to have independent lifecycles. Aurora won the 2019 SIGMOD Systems Award for fundamentally redesigning relational database storage for cloud environments. For a deep dive into the architecture, refer to Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases. Aurora offers 99.99% availability and your data gets replicated six ways across three Availability Zones, at no incremental cost. Aurora’s architecture of separating the compute and storage layers has also allowed us to add features that have enhanced capabilities in MySQL, such as the ability to instantly spin up database clones, Aurora Global Database for multi-Region architecture, or Amazon Aurora Serverless v2 for applications that require vertical scaling capabilities.

Our customers rely on the security and long-term stability of open-source databases. Collaboration with the upstream open-source communities is critical to how we build and operate database services. We collaborate with the communities by contributing to the MySQL and MariaDB engines, as well as the broader ecosystem, including projects such as Percona xtrabackup, HammerDB, and rocksDB. Our most recent open sourced innovations for MySQL include the Advanced JDBC Wrapper Driver for Aurora, and the audit plugin for MySQL. The MariaDB Foundation recently recognized Amazon as a major contributor to MariaDB. To learn about AWS’s recent sponsorship of MariaDB, refer to MariaDB Foundation Announces AWS as a Diamond Sponsor.

In the following sections, we discuss some of our key features across Amazon RDS for MySQL and Amazon Aurora MySQL. These features span across scalability, availability, data portfolio integrations, and price-performance—all important criteria in picking a suitable cloud database for your workload.

Scalability

Managed MySQL on AWS offers both horizontal scalability and vertical scalability features. Outside of easily spinning up replicas, customers can choose to scale in several other ways, some of which are discussed below.

Aurora Serverless v2 (Amazon Aurora MySQL)

Our customers needed the ability to scale their applications vertically in response to varying workload patterns. The traditional method of vertical scaling is disruptive because it required downtime to change an instance to a larger size. Aurora Serverless V2 addresses this challenge of vertical scaling by seamlessly scaling compute of Aurora database instance, without a need for downtime. It automatically scales capacity up and down based on your application’s needs. You can run your database in the cloud without managing any database instances. Aurora Serverless v2 supports multiple serverless instances in a cluster, mixed-cluster configurations (serverless and provisioned in the same cluster), granular scale-up and scale-down, Multi-AZ capabilities and Aurora Global Database.

If you’re interested in seeing how Aurora Serverless v2 can scale up and scale down in response to application needs, review the following demo (at 27 minutes). To enable serverless functionality, AWS manages a fleet of compute instances and constantly monitors for database processes that may need more resources, such as CPU, memory, and networking to scale up in-place. Once identified, AWS automatically moves the database instance from a busy host to an idle host, and does so non-disruptively. We are observing that customers with seasonal needs, ISVs with multi-tenant applications, and smaller segments of customers with low CPU utilization derive maximum benefits from Aurora Serverless.

Aurora Global Database (Amazon Aurora MySQL)

Most customers, especially those in highly regulated industries such as financial services have strict Disaster Recovery requirements to meet their regulatory and compliance obligations. In the event of an outage, switching applications from one Region to another can be time consuming and result in excessive data loss. To enable managed disaster recovery that can minimize RPO and RTO without a negative performance impact to the production workload, we launched Aurora Global Database. Aurora Global Database is designed for globally distributed applications, allowing a single Aurora database to span multiple Regions. Its multi-Region architecture can support businesses that are looking for quick availability and recoverability of their data and application during a failure. This architecture means that your data and applications are always protected from Region-wide failures, on top of the built-in availability features that Aurora offers for in-Region protection. Physical replication between Regions, unlike binlog-based replication, ensures that your data is in sync with minimal performance impact on the primary database cluster.

Although disaster recovery is a prominent use case, Aurora Global Database is also relevant to customers with distributed applications or those requiring read scalability. You can use Aurora Global Database to offload read traffic to other Regions and free up the primary Region. You can also use write forwarding on Amazon Aurora MySQL to simplify endpoint management and issue write transactions in secondary Regions. Aurora Global Database forwards these transactions to the primary Region and commits them in the primary Region first.

Aurora Global Database also offers configurations such as headless (secondary region without compute nodes) or Global Database with Aurora Serverless v2 (instances in secondary or primary Region can be serverless allowing you to scale faster than spinning up new instances) to help you find your optimal balance between price and performance.

Multi-AZ with two readable standbys (Amazon RDS for MySQL)

Amazon RDS for MySQL offers Multi-AZ cluster deployments with two readable standbys. Both standby DB instances can also serve read traffic without the need to attach additional read replica DB instances. This deployment option is ideal when your workloads require lower write latency, automated failovers, and more read capacity. This configuration supports up to two-times faster transaction commits than a Multi-AZ DB instance deployment with one standby, without compromising data durability. Automated failovers in this configuration typically take under 35 seconds.

Data portfolio integrations

Managed MySQL offers integrations with several other AWS services. In this section, we discuss some of our data portfolio integrations.

Aurora machine learning integration (Amazon Aurora MySQL)

Amazon Aurora MySQL natively integrates with Amazon SageMaker. Amazon SageMaker is a fully managed service that brings together a broad set of tools to enable high-performance, low-cost machine learning (ML) for any use case. With SageMaker, you can build, train and deploy ML models at scale using tools like notebooks, debuggers, profilers, pipelines, MLOps, and more – all in one integrated development environment (IDE). SageMaker supports governance requirements with simplified access control and transparency over your ML projects. In addition, you can build your own FMs (large models trained on massive datasets), with purpose-built tools to fine-tune, experiment, retrain, and deploy FMs. SageMaker offers access to hundreds of pretrained models, including publicly available FMs, that you can deploy with just a few clicks.

This integration has halved the number of steps needed to move transactional data in managed MySQL to SageMaker, allowing you to build, train, and deploy ML models for any use case. With this built-in integration, you spend no time writing code to access the database for formatting data for ML models. Instead, you can run a simple SQL query to invoke an ML model of your choice and then use the results of the model in your application. This integration is particularly valuable with the recent growth in generative AI use cases.

Amazon Aurora MySQL zero-ETL integration with Amazon Redshift (Amazon Aurora MySQL and Amazon RDS for MySQL)

Amazon Aurora and Amazon RDS support zero-ETL integration with Amazon Redshift (AWS’s cloud data warehouse) to enable near-real-time analytics and ML using Amazon Redshift on petabytes of transactional data from Aurora. Within seconds of transactional data being written into Aurora, the data is available in Amazon Redshift, so you don’t have to build and maintain complex data pipelines to perform extract, transform, and load (ETL) operations. As of this writing, the integration is Generally Available for Amazon Aurora MySQL and is available in preview for Amazon RDS for MySQL customers.

Price-performance

In this section, we discuss some of our many enhancements that offer better price-performance ratios to our customers .

Aurora I/O-Optimized configuration (Amazon Aurora MySQL)

Aurora I/O-Optimized was launched in May 2023. This new configuration provides improved price-performance and predictable pricing for customers with I/O-intensive applications. Aurora I/O-Optimized offers improved performance, increasing throughput and reducing latency for your most demanding workloads. With Aurora I/O-Optimized, there is no charge for read and write I/O operations—you only pay for your database instances and storage usage, making it easy to predict your database spend up front. If your I/O spend exceeds 25% of total spend, you can use this configuration to save up to 40% of your database costs. The I/O cost savings increases linearly with higher IOPS usage.

Optimized Reads and Writes (Amazon Aurora MySQL and Amazon RDS for MySQL)

With the launch of Optimized Writes, AWS was able to get rid of a two-step write process that involved an interim write on MySQL’s doublewrite buffer, typically used to maintain consistency of a 16 KiB page write. This was only made possible by the AWS Nitro System, which has built-in hardware capabilities to ensure the integrity of a 16 KiB page write for MySQL. As a result, Amazon RDS for MySQL now offers up to 2X the write throughput compared to self-managed MySQL. As part of our work with Optimized Writes, we also identified additional write throughput improvements which can deliver an incremental 3X improvement in write throughput for your applications. To use the Optimized Writes feature you have to choose supported DB instance class and be on MySQL version 8.0.30 and above.

Optimized Reads takes advantage of local NVMe instance stores to hold temporary objects and data created during analytical operations, read-heavy operations, or just queries that in general involve complex operations. Queries that use temporary tables, such as those involving sorts, hash aggregations, high-load joins, and Common Table Expressions (CTEs), can run up to 2X faster with Optimized Reads. It is worth noting, that Amazon Aurora for MySQL does not use doublewrite buffer so it solves the throughput problem natively. To use the Optimized Reads feature you have to choose supported DB instance with an instance store, such as db.m5d or db.m6gd class.

Availability

In this section, we discuss features that help customers ensure availability when using managed MySQL on AWS.

Managed Blue/Green deployment (Amazon Aurora MySQL and Amazon RDS for MySQL)

Managed Blue/Green deployment is available on both Amazon RDS for MySQL and Amazon Aurora MySQL. We heard from customers over the years that changes to databases such as major and minor version upgrades, parameter changes, and schema changes are very disruptive and involve unpredictable downtime. Managed Blue/Green deployment offers a safer, faster, and simpler mechanism to make such changes. Blue/Green deployments create a fully managed staging environment that allows you to deploy and test production changes, while keeping your current production database safe. With a single click, you can promote the staging environment to be the new production system in as fast as a minute, with no changes to your application and no data loss. Blue/Green deployments use built-in switchover guardrails that will time out the promotion of the staging environment if it exceeds your maximum tolerable downtime, detects replication errors, or identifies instance health check errors.

Today, several customers rely on this capability to ensure high availability for their business-critical processes.

Zero-downtime patching (Amazon Aurora MySQL)

Performing upgrades for Aurora DB clusters involves the possibility of an outage when the database is shut down while it’s being upgraded. The zero-downtime patching (ZDP) feature attempts, on a best-effort basis, to preserve client connections through an Aurora upgrade. If ZDP completes successfully, application sessions are preserved and the database engine restarts while the upgrade is in progress.

What’s new?

In addition to the recent innovations on managed MySQL that we’ve discussed so far, we have an exciting lineup of features recently launched or announced at AWS re:Invent.

The following are new features for Amazon Aurora MySQL:

• Zero-ETL integration with Amazon Redshift, first announced at last year’s Re:Invent, is now generally available. Zero-ETL integration makes transactional data in Aurora available in Amazon Redshift within seconds, without the need to perform cumbersome data transformations manually.
• Enhanced binlog on Aurora launched in May 2023. The enhanced binlog reduces the compute overhead of enabling binlog, which in certain cases can reach up to 50%, down to 13%. This in turn allows additional throughput on the same hardware. The enhanced binlog also improves database recovery time when the binlog is enabled by up to 99% compared to recovery when community binlog is enabled.
• Local write forwarding was launched in August 2023. This new capability makes it simple to scale read workloads which require read after write consistency. Customers can now issue transactions containing both reads and writes on Aurora read replicas and the writes will be automatically forwarded to the single writer instance for execution.

The following are new features for Amazon RDS for MySQL:

• Zero-ETL integration with Amazon Redshift is now available in preview.
• MySQL Innovation Releases, starting with MySQL 8.1, are now available in the Amazon RDS Preview environment. This will allow you to test out MySQL community’s Innovation Releases.
• Up to a 3X improvement in write throughput starting with MySQL 8.0.35.
• You can now set up active/active clusters with your RDS for MySQL instances, using the Group replication plugin. An active/active cluster set up using the Group Replication plugin on RDS for MySQL instances provides virtually synchronous replication, with built-in conflict detection/handling and consistency guarantees.
• Multi-AZ DB clusters with two readable standbys now support minor version upgrades with under 1 second of downtime using Amazon RDS Proxy.

Finally, across both Aurora and Amazon RDS, we now offer Amazon RDS Extended Support for up to 3 years beyond the community end of life (EOL) of MySQL major version 5.7 for customers who need to continue using this version and don’t want to compromise on security and availability. Amazon Aurora MySQL offers 12 months of support for no charge, post-community EOL. Amazon RDS for MySQL offers 4 months of support for no charge, post-community EOL.

Summary

AWS customers across industries and size have adopted managed MySQL on AWS as their preferred database engine and benefit from the innovations launched so far. Customers such as Intuit, Freshworks, Airbnb, and several others have migrated their MySQL workloads to AWS and continue to operate at scale. AWS has a rich history of innovating in the MySQL space and we are continuing to do so at a rapid pace. With years of experience and a proven ability to support MySQL workloads at scale, AWS remains the best place to build and grow your MySQL workloads. If you are a MySQL user looking to move to the cloud or contemplating building your next cloud application on MySQL, get started with MySQL on AWS.

About the Authors

Aditya Samant is a relational database industry veteran with over 2 decades of experience working with commercial and open-source databases. He currently works at Amazon Web Services as a Sr. Database Specialist Solutions Architect. In his role, he spends his time working with customers designing scalable, secure and robust cloud native architectures. Aditya also works closely with the service teams and collaborates on designing and delivery of the new features for Amazon’s flagship relational database, Amazon Aurora.

Shivesh Bakshi is a Worldwide Go to Market Lead for MySQL on AWS. Shivesh is passionate about helping MySQL customers maximize value from their MySQL deployments on AWS and driving customer success.