This article is more than 1 year old

Juggling hundreds and thousands of MySQL databases? Throw them into the cloud

Time to wake up to Amazon Aurora

Sponsored Feature Since its debut almost 30 years ago, MySQL has carved out an enviable position in the relational database world. The open-source proposition continues to run almost neck and neck with Oracle in market share, while arguably having the edge in popularity with developers.

However, that very popularity can present challenges, not least of which is that organizations can find themselves with vast estates of MySQL databases. This can be tricky to handle, from both a technical and management perspective, and it is not always clear that the "community" can keep up with the burgeoning demand for new tooling.

At the same time, organizations are increasingly looking to managed cloud services to underpin their data infrastructure, manage costs, and free up their database specialists to concentrate on adding value rather than spending their time on important, but essentially routine, admin tasks.

So, it is perhaps no surprise to learn that the fastest growing service in Amazon Web Services (AWS) history is its relational database, Amazon Aurora, which is fully compatible with MySQL, says Chayan Biswas, Senior Manager of Product Management for Amazon Aurora. And that is in no small part because of customers' desire to consolidate their sprawling MySQL workloads and improve cost and operational efficiency.

Aurora is a relational database that is fully compatible with both MySQL and PostgreSQL, built for the cloud, and designed for performance and availability at global scale. Its underlying architecture is also, Biswas says, purpose-built for relational databases, allowing AWS to offer "increased availability, durability, and also optimizations."

Compute and storage are separated, and data is stored across three Availability Zones, with each zone carrying two copies of the data. "So, you get six copies for the price of one," he says. "And that also gives you better durability. Your data gets distributed, in this purpose-built storage layer, into hundreds or even 1000s of storage nodes, depending on the size of your database."

And the size of that database can be massive, with AWS supporting automatic scaling up to 128 terabytes.

But having massive scale at your fingertips is only useful if you can get to your data quickly and manage it easily. On the compute side of Aurora, says Biswas, "You have one primary writer instance, which performs both read and write operations, and up to 15 low latency read replicas with typically less than 30 milliseconds of lag. So, you can use these 15 read replicas to scale your read-only workload."

Making your database truly global

Furthermore, AWS Amazon Aurora Global Database technology employs the same storage layer capabilities that Aurora uses in region to distribute data in up to five additional AWS Regions. Replication is performed through dedicated infrastructure in the storage layer with speed typically less than a second, says Biswas "So when it comes to disaster recovery, recovery point objective, the amount of data you could potentially lose is one second." And recovery time, he adds, is "less than a minute."

This offers a massive boost over MySQL's native replication, which is a lot harder to scale to the same extent. "What we have seen is your database loses almost 50 per cent of the throughput if you enable native replication," says Biswas.

Aurora Global Database also feeds into customers' ability to scale their applications across regions. "We support global databases in up to five separate regions with no overhead increase to your primary region. If you're deploying an application globally, you can run your read only workloads in these multiple locations."

Another feature is support for write forwarding, which allows customers to deploy applications which are agnostic regarding their location, "They can issue writes from the secondary regions as well, and these are forwarded to the one region where you have the read/write instance."

But while users might be impressed by the level of scalability and volume of raw storage available with Aurora, they might also be wary of capacity management. To mitigate concerns, they have the option of tapping into Aurora's Serverless capability for Amazon Aurora MySQL-Compatible Edition and either leaving compute capacity management to Aurora, or choosing to provision regular instances.

That choice comes down to balancing cost and operational efficiencies, says Biswas. "Because you don't have to provision for your peak workloads anymore. You're only paying for what you're using." Alternatively, "If you have a steady state workload, no variability … you'd consider using a provisioned instance, and even go for reserved instances for greater savings."

"Amazon Aurora Serverless makes Aurora MySQL suitable for handling vast numbers of workloads and databases, which has led both large financial institutions and SaaS vendors to already deploy it," Biswas says. "Organizations with a large number of database workloads can find the right balance between cost and operational complexity using Aurora Serverless. They get to consolidate just the right number of databases for cost efficiency while reducing the operational overhead otherwise needed for monitoring and rebalancing capacity for each individual database."

Another key feature for this type of organization, which may be supporting thousands of databases, is Fast Database Cloning. This allows admins to quickly create a clone of a database, something that would be cumbersome with native MySQL tooling, and potentially involve hours of downtime.

As Biswas explains, "It's possible that, within the cohort of customers you consolidated, some become more active, and start using your service more."

It can make sense to "separate" these customers out, he says, giving them their own database. This typically takes about five minutes, says Biswas, using a copy on write protocol. "It means that you're not paying for two copies of your database. Instead, you are only charged for the changes made to the clone."

Thousands and thousands?

AWS says that Aurora Serverless is also useful for large companies with thousands of applications which are generating more usage at certain times, for example during specific accounting operations or at payroll time towards the end of the month.

If that sounds like stretching the boundaries of what an organization might want to do, just consider the example of SaaS vendor Acquia which recently migrated its Open Digital Experience platform onto Aurora. This involved moving 140,000 MySQL databases supporting 30,000 customer sites. The company had previously had to overprovision databases just to ensure it met its 99.95 percent uptime service level agreement (SLA). But switching to Aurora meant it could reduce costs and downtime, and reassign technicians from simply overseeing and managing the platform.

Further scalability can be gained through Amazon RDS Proxy, which is available for Amazon Aurora MySQL-Compatible Edition, and is described by Biswas as a "fully managed, SQL aware database proxy".

RDS Proxy pools and shares database connections to improve the scalability of applications. With customers running tens of thousands of applications connecting to several consolidated MySQL databases simultaneously, having each application open multiple database connections could become a bottleneck.

"If you have too many connections with MySQL, your compute, memory, networking, all of those resources are being used just for managing these connections," Biswas explains. RDS Proxy takes on the burden of managing the connections from the application, he says, while using a pooled set of database connections to handle queries.

Faster failover improves availability

RDS Proxy also further improves application availability, adds Biswas. "If your primary read-write instance fails or becomes unavailable, Aurora will detect that and automatically failover to one of those 15 read replicas we talked about."

If a failover happens, the proxy will hold on to the connection it has, and queue up any queries sent during the time the failover is happening. And once the database is up again, it will reconnect to it and continue. As well as improving availability, this also negates the effect of Domain Name System (DNS) propagation delays and makes failovers up to 66 percent faster, bringing them down to 10 seconds or less, says Biswas.

This makes RDS Proxy a good fit for highly scalable applications built using modern serverless technologies such as AWS Lambda, says Biswas, while AWS also offers the AWS Java Database Connectivity (JDBC) Driver for MySQL that can be used by stateful applications for connection pooling and faster failovers.

While Aurora MySQL offers improved features over community MySQL, it is also fully compatible with third party and native MySQL tooling. That means customers can use familiar tools such as MySQL Dump or Percona's XtraDB Backup to begin their migration to Aurora by dumping their data into an S3 bucket. Or they can just use AWS Database Migration Service (DMS), to migrate the database and then continuously capture changes happening in the source database.

Of course, modern application development is increasingly centered on microservices and container-based deployments. So AWS offers an open source Kubernetes operator software extension to support native tooling for creating Aurora databases in Kubernetes environments. "You can use AWS Controllers for Kubernetes (ACK) to provision and connect to Aurora databases. You don't have to exit out of your native Kubernetes tooling, and, in fact, you can use your DevOps pipelines to continue deploying container environments as well as your databases or setting up connectivity," explains Biswas.

The compatibility with third party tools is one of the factors which has made MySQL so popular with developers. And flexibility and ease of use are also features that AWS is keen to improve with Aurora.

"If you have applications built for MySQL or PostgreSQL, you should be able to use Aurora with little to no change," Biswas concludes.

Sponsored by AWS.

More about

More about

More about

TIP US OFF

Send us news