Large tables can pose challenges for many operations when working with a database. Occasionally, we may need to modify the table definition. Since RDS replication does not use asynchronous for its replication, the typical switchover procedure is not feasible. However, the Blue/Green feature of RDS utilizes asynchronous replication, which allows us to update the table […]
22
2025
Using Blue/Green Deployment For (near) Zero-Downtime Primary Key Updates in RDS MySQL
27
2020
A First Glance at Amazon Aurora Serverless RDS
If you often deploy services in the cloud, you certainly, at least once, forgot to stop a test instance. I am like you and I forgot my share of these. Another mistake I do once in a while is to provision a bigger instance than needed, just in case, and forget to downsize it. While this is true for compute instances, it is especially true for database instances. Over time, this situation ends up adding a cost premium. In this post, we’ll discuss a solution to mitigate these extra costs, the use of the RDS Aurora Serverless service.
What is Amazon Aurora Serverless?
Since last spring, Amazon unveiled a new database related product: RDS Aurora Serverless. The aim of this new product is to simplify the management around Aurora clusters. It brings a likely benefit for the end users, better control over cost. Here are some of the benefits we can expect from this product:
- Automatic scaling up
- Automatic scaling down
- Automatic shutdown after a period of inactivity
- Automatic startup
The database is constantly monitored and if the load grows beyond a given threshold, a bigger Aurora instance is added to the cluster, the connections are moved and the old instance is dropped. The opposite steps happen when a low load is detected. Also, if the database is completely inactive for some time, it is automatically stopped and restarted when needed. The RDS Aurora Serverless cluster type is available for MySQL (5.6 and 5.7) and PostgreSQL (10.12).
Architecture
The RDS Aurora Serverless architecture is similar to the regular RDS Aurora one. There are three main components; a proxy layer handling the endpoints, the servers processing the queries, and the storage. The proxy layer and the storage are about the same. As the name implies, what is dynamic with the Aurora Serverless type are the servers.
There are not many details available as to how things are actually implemented but likely but the proxy layer is able to transfer a connection from one server to another when there is a scale up or down event. Essentially, we can assume that when the cluster is modified, the steps are the following:
- A new Aurora server instance is created with the new size
- The new instance is added to the Aurora cluster
- The writer role is transferred to the new instance
- The existing connections are moved
- The old instance is removed
How To Configure It
The configuration of an RDS Aurora Serverless cluster is very similar to a regular Aurora cluster, there are just a few additional steps. First, of course, you need to choose the serverless type:
And then you have to specify the limits of your cluster in “Capacity”. The capacity unit is ACU which stands for Aurora Capacity Unit. I couldn’t find the exact meaning for the ACU, the documentation has: “Each ACU is a combination of processing and memory capacity.”. An ACU seems to provide about 2GB of RAM and the range of possible values is 1 to 256. You set the minimum and maximum ACU you want for the cluster in the following dialog box:
The last step is to specify the inactivity timeout after which the database is paused:
How It Works
Startup
If the Aurora Serverless cluster has no running server instances, an attempt to connect to the database will trigger the creation of a new instance. This process takes some time. I used a simple script to measure the connection time after an inactivity timeout and found the following statistics:
Min = 31s Max = 54s average = 42s StdDev = 7.1s Count = 17
You’ll need to make sure the application is aware of a new connection, as the database can take close to a minute to complete. I got caught a few times with sysbench timing out after 30s. It is important to remember the initial capacity used is the same as the one when the Aurora Serverless instance stopped, unless you enabled the “Force scaling the capacity…” parameter in the configuration.
Pause
If an Aurora Serverless cluster is idle for more than its defined inactivity time, it will be automatically paused. The inactivity here is defined in terms of active connections, not queries. An idle connection doing nothing will prevent the Aurora Serverless instance from stopping. If you intend to use the automatic pause feature, I recommend setting the “wait_timeout” and “interactive_timeout” to values in line with the cluster inactivity time.
Scale Up
A process monitors the Aurora Serverless instance and if it sees a performance issue that could be solved by the use of a larger instance type, it triggers a scale up event. When there is an ongoing scale up (or down) event, you’ll see a process like this one in the MySQL process list:
call action start_seamless_scaling('AQEAAEgBh893wRScvsaFbDguqAqinNK7...
Bear in mind a scale up event can take some time, especially if the server is very busy. While doing some benchmarks, I witness more than 200s on a few occasions. The queries load is affected for a few seconds when the instances are swapped.
To illustrate the scale up behavior, I ran a modified sysbench benchmark to force some CPU load. Here’s a 32 threads benchmarks scanning a table on an Aurora Serverless cluster having an initial capacity of 1.
The first scale up happened a little after 600s while the second one occurred around 1100s. The second event didn’t improve much the load but that is likely an artifact of the benchmark. It took a long time to increase the capacity from 1 to 2, it could be related to the high CPU usage on the instance. There is usually a small disruption of the query load when the instances are swapped but nothing too bad.
Scale Down
While scale up events happen when needed, scale down events are throttled to about once per 5 minutes except if the previous scaling event was a “scale up”, then the delay is 15 minutes.
Pros and Cons of Aurora Serverless
The RDS Aurora Serverless offering is very compelling for many use cases. It reduces the cost and simplifies the management. However, you must accept the inherent limitations like the long start up time when the instance was on pause and the small hiccups when the capacity is modified. If you cannot cope with the start up time, you can just configure the instance so it doesn’t pause, it will scale down to a capacity of 1 which seems to map to a t3.small instance type.
Of course, such an architecture imposes some drawbacks. Here’s a list of a few cons:
- As we have seen, the scale up time is affected by the database load
- Failover can also take more time than normally expected, especially if the ACU value is high
- You are limited to one node although, at an ACU of 256, it means a db.r4.16xlarge
- No public IP but you can set up a Data API
- The application must be robust in the way it deals with database connections because of possible delays and reconnections
Cost Savings
The cost of an RDS Aurora cluster has three components: the instance costs, the IO costs, and the storage costs. The Aurora Serverless offering affects only the instance costs. The cost is a flat rate per capacity unit per hour. Like for the normal instances, the costs are region-dependent. The lowest is found in the us-east at $0.06 USD per Capacity unit per hour.
If we consider a database used by web developers during the day and which can be paused out of the normal work hours and during the weekends, the saving can be above $240/month if the daily average capacity is only eight hours.
09
2020
Amazon Aurora Multi-Primary First Impression
For what reason should I use a real multi-primary setup?
To be clear, not a multi-writer solution where any node can become the active writer in case of needs, as for Percona XtraDB Cluster (PXC) or Percona Server for MySQL using Group_replication. No, we are talking about a multi-primary setup where I can write at the same time on multiple nodes. I want to insist on this “why?”.
After having excluded the possible solutions mentioned above, both covering the famous 99.995% availability, which is 26.30 minutes of downtime in a year, what is left?
Disaster Recovery? Well, that is something I would love to have, but to be a real DR solution we need to put several kilometers (miles for imperial) in the middle.
And we know (see here and here) that aside from some misleading advertising, we cannot have a tightly coupled cluster solution across geographical regions.
So, what is left? I may need more HA, ok, that is a valid reason. Or I may need to scale the number of writes, which is a valid reason as well. This means, in the end, that I am looking to a multi-primary because:
- Scale writes (more nodes more writes)
- Consistent reads (what I write on A must be visible on B)
- Gives me 0 (zero) downtime, or close to that (5 nines is a maximum downtime of 864 milliseconds per day!!)
- Allow me to shift the writer pointer at any time from A to B and vice versa, consistently.
Now, keeping myself bound to the MySQL ecosystem, my natural choice would be MySQL NDB cluster. But my (virtual) boss was at AWS re-invent and someone mentioned to him that Aurora Multi-Primary does what I was looking for. This (long) article is my voyage in discovering if that is true or … not.
Given I am focused on the behavior first, and NOT interested in absolute numbers to shock the audience with millions of QPS, I will use low-level Aurora instances. And will perform tests from two EC2 in the same VPC/region of the nodes.
You can find the details about the tests on GitHub here.
Finally, I will test:
- Connection speed
- Stale read
- Write single node for baseline
- Write on both node:
- Scaling splitting the load by schema
- Scaling same schema
Test Results
Let us start to have some real fun. The first test is …
Connection Speed
The purpose of this test is to evaluate the time taken in opening a new connection and time taken to close it. The action of the open/close connection can be a very expensive operation, especially if applications do not use a connection pool mechanism.
As we can see, ProxySQL results to be the most efficient way to deal with opening connections, which was expected given the way it is designed to reuse open connections towards the backend.
Different is the close connection operation, in which ProxySQL seems to take a little bit longer.
As a global observation, we can say that by using ProxySQL we have more consistent behavior. Of course, this test is a simplistic one, and we are not checking the scalability (from 1 to N connections) but it is good enough to give us the initial feeling. Specific connection tests will be the focus of the next blog on Aurora MM.
Stale Reads
Aurora multi-primary uses the same mechanism of the default Aurora to update the buffer pool:
Using the Page Cache update, just doing both ways. This means that the Buffer Pool of Node2 is updated with the modification performed in Node1 and vice versa.
To verify if an application would be really able to have consistent reads, I have run this test. This test is meant to measure if, and how many, stale reads we will have when writing on a node and reading from the other.
Amazon Aurora multi-primary has two consistency models:
As an interesting fact, the result was that with the default consistency model (INSTANCE_RAW), we got a 100% stale read.
Given that I focused on identifying the level of the cost that exists when using the other consistency model (REGIONAL_RAW), that allows an application to have consistent reads.
The results indicate an increase of 44% in total execution time, and of 95% (22 times slower) in write execution.
It is interesting to note that the time taken is in some way predictable and consistent between the two consistency models.
The graph below shows in yellow how long the application must wait to see the correct data on the reader node. In blue is the amount of time the application waits to get back the same consistent read because it must wait for the commit on the writer.
As you can see, the two are more or less aligned.
Given the performance cost imposed by using REGIONAL_RAW, all the other tests are done with the default INSTANCE_RAW, unless explicitly stated.
Writing Tests
All tests run in this section were done using sysbench-tpcc with the following settings:
sysbench ./tpcc.lua --mysql-host=<> --mysql-port=3306 --mysql-user=<> --mysql-password=<> --mysql-db=tpcc --time=300 --threads=32 --report-interval=1 --tables=10 --scale=15 --mysql_table_options=" CHARSET=utf8 COLLATE=utf8_bin" --db-driver=mysql prepare sysbench /opt/tools/sysbench-tpcc/tpcc.lua --mysql-host=$mysqlhost --mysql-port=$port --mysql-user=<> --mysql-password=<> --mysql-db=tpcc --db-driver=mysql --tables=10 --scale=15 --time=$time --rand-type=zipfian --rand-zipfian-exp=0 --report-interval=1 --mysql-ignore-errors=all --histogram --report_csv=yes --stats_format=csv --db-ps-mode=disable --threads=$threads run
Write Single Node (Baseline)
Before starting the comparative analysis, I was looking to define what was the “limit” of traffic/load for this platform.
From the graph above, we can see that this setup scales up to 128 threads and after that, the performance remains more or less steady.
Amazon claims that we can mainly double the performance when using both nodes in write mode and use a different schema to avoid conflict.
Once more, remember I am not interested in the absolute numbers here, but I am expecting the same behavior. Given that, our expectation is to see:
Write on Both Nodes, Different Schemas
So AWS recommend this as the scaling solution:
And I diligently follow the advice. I used two EC2 nodes in the same subnet of the Aurora Node, writing to a different schema (tpcc & tpcc2).
Overview
Let us make it short and go straight to the point. Did we get the expected scalability?
Well, no:
We just had a 26% increase, quite far to be the expected 100% Let us see what happened in detail (if not interested just skip and go to the next test).
Node 2
As you can see, Node1 was (more or less) keeping up with the expectations and being close to the expected performance. But Node2 was just not keeping up, and performances there were just terrible.
The graphs below show what happened.
While Node1 was (again more or less) scaling up to the baseline expectations (128 threads), Node2 collapsed on its knees at 16 threads. Node2 was never able to scale up.
Reads
Node 1
Node1 is scaling the reads as expected, but also here and there we can see performance deterioration.
Node 2
Node2 is not scaling Reads at all.
Writes
Now someone may think I was making a mistake and I was writing on the same schema. I assure you I was not. Check the next test to see what happened if using the same schema.
Write on Both Nodes, Same Schema
Overview
Now, now, Marco, this is unfair. You know this will cause contention. Yes, I do! But nonetheless, I was curious to see what was going to happen and how the platform would deal with that level of contention.
My expectations were to have a lot of performance degradation and an increased number of locks. About conflict I was not wrong, node2 after the test reported:
+-------------+---------+-------------------------+ | table | index | PHYSICAL_CONFLICTS_HIST | +-------------+---------+-------------------------+ | district9 | PRIMARY | 3450 | | district6 | PRIMARY | 3361 | | district2 | PRIMARY | 3356 | | district8 | PRIMARY | 3271 | | district4 | PRIMARY | 3237 | | district10 | PRIMARY | 3237 | | district7 | PRIMARY | 3237 | | district3 | PRIMARY | 3217 | | district5 | PRIMARY | 3156 | | district1 | PRIMARY | 3072 | | warehouse2 | PRIMARY | 1867 | | warehouse10 | PRIMARY | 1850 | | warehouse6 | PRIMARY | 1808 | | warehouse5 | PRIMARY | 1781 | | warehouse3 | PRIMARY | 1773 | | warehouse9 | PRIMARY | 1769 | | warehouse4 | PRIMARY | 1745 | | warehouse7 | PRIMARY | 1736 | | warehouse1 | PRIMARY | 1735 | | warehouse8 | PRIMARY | 1635 | +-------------+---------+-------------------------+
Which is obviously a strong indication something was not working right. In terms of performance gain, if we compare ONLY the result with the 128 Threads:
Also with the high level of conflict, we still have 12% of performance gain.
The problem is that in general, we have the two nodes behaving quite badly. If you check the graph below you can see that the level of conflict is such to prevent the nodes not only to scale but to act consistently.
Node 1
Reads
In the following graphs, we can see how node1 had issues and it actually crashed three times, during tests with 32/64/512 threads. Node2 was always up but the performances were very low.
Writes
Recovery From Crashed Node
About recovery time, reading the AWS documentation and listening to presentations, I often heard that Aurora Multi-Primary is a 0 downtime solution. Or other statements like: “in applications where you can’t afford even brief downtime for database write operations, a multi-master cluster can help to avoid an outage when a writer instance becomes unavailable. The multi-master cluster doesn’t use the failover mechanism, because it doesn’t need to promote another DB instance to have read/write capability”
To achieve this the suggestion, the solution I found was to have applications pointing directly to the Nodes endpoint and not use the Cluster endpoint.
In this context, the solution pointing to the Nodes should be able to failover within a second or so, while the cluster endpoint:
Personally, I think that designing an architecture where the application is responsible for the connection to the database and failover is some kind of refuse from 2001. But if you feel this is the way, well, go for it.
What I did for testing is to use ProxySQL, as plain as possible with nothing else, and the basic monitor coming from the native monitor. I then compared the results with the tests using the Cluster endpoint. In this way, I adopt the advice of pointing directly at the nodes, but I was doing things in our time.
The results are below and they confirm (more or less) the data coming from Amazon.
A downtime of seven seconds is quite a long time nowadays, especially if I am targeting the 5 nines solution that I want to remember is 864 ms downtime per day. Using ProxySQL is going closer to that, but still too long to be called zero downtime.
I also have fail-back issues when using the AWS cluster endpoint, given it was not able to move the connection to the joining node seamlessly.
Last but not least, when using the consistency level INSTANCE_RAW, I had some data issue as well as PK conflict:
FATAL: mysql_drv_query() returned error 1062 (Duplicate entry ‘18828082’ for key ‘PRIMARY’)
Conclusions
As state at the beginning of this long blog, the reasonable expectations to go for a multi-primary solution were:
- Scale writes (more nodes more writes)
- Gives me zero downtime, or close to that (5 nines is a maximum downtime of 864 milliseconds per day!!)
- Allow me to shift the writer pointer at any time from A to B and vice versa, consistently.
Honestly, I feel we have completely failed the scaling point. Probably if I use the largest Aurora I will get much better absolute numbers, and it will take me more to encounter the same issues, but I will. In any case, if the multi-primary solution is designed to provide that scalability, and it should do that with any version.
I did not have zero downtime, but I was able to failover pretty quickly with ProxySQL.
Finally, unless the consistency model is REGIONAL_RAW, shifting from one node to the other is not prone to possible negative effects like stale reads. Given that I consider this requirement not satisfied in full.
Given all the above, I think this solution could eventually be valid only for High Availability (close to being 5 nines), but given it comes with some limitations I do not feel comfortable in preferring it over others just for that, at the end default Aurora is already good enough as a High available solution.
References
AWS re:Invent 2019: Amazon Aurora Multi-Master: Scaling out database write performance
Working with Aurora multi-master clusters
Improving enterprises ha and disaster recovery solutions reviewed
25
2019
Adaptive Hash Index on AWS Aurora
Recently I had a case where queries against Aurora Reader were 2-3 times slower than on the Writer node. In this blog post, we are going to discuss why.
I am not going to go into the details of how Aurora works, as there are other blog posts discussing that. Here I am only going to focus on one part.
The Problem
My customer reported there is a huge performance difference between the Reader and the Writer node just by running selects. I was a bit surprised, as the select queries should run locally on the reader node, the dataset could fit easily in memory, there were no reads on disk level, and everything looked fine.
I was trying to rule out every option when one of my colleagues mentioned I should have a look at the InnoDB_Adaptive_Hash_Indexes
. He was right – it was disabled on the Reader nodes, I could see it on the console.
Let’s enable the adaptive hash index
I opened the control panel and I was checking the parameter groups, but the adaptive hash index was already enabled. Ok, I might have made a mistake but I double checked myself many times and it was true. Adaptive hash was disabled on the console but enabled on the control panel. That means the AWS control panel is lying!
I have restarted the nodes multiple times, and I have created new test clusters, etc… but I was not able to enable adaptive hash on the Reader node. It was enabled on the Writer node, and it was working.
Is this causing the performance difference?
Because I was able to enable or disable the adaptive hash index on the Writer node, I continued my tests there and I could confirm that when I disabled it the queries got slower. Same speed as on the Reader node. When I enabled, AHI queries got faster.
In general with AHI on the Writer node, the customer’s queries were running 2 times faster.
AHI can help for many workloads but not all of them, and you should test your queries/workload both with and without AHI.
Why is it disabled on the Reader?
I have to be honest because I am not an AWS engineer and I do not know the code of Aurora, but I am only guessing here and I might be wrong.
Why can I change it in the parameter group?
We can modify the adaptive hash in the parameter groups, but there is no impact on the Reader nodes at all. Many customers could think they have AHI enabled but actually, they don’t. I think this is a bad practice because if we cannot enable it on the Reader node we should not be able to change it on the control panel.
Is this causing any performance problems for me?
If you are using the Reader node for selects queries, which are based on secondary keys, you are probably suffering from this but it depends on your workload if it is impacting your performance or not. In my customer’s case, the difference was 2 times slower without AHI.
But I want fast queries!
If your queries heavily benefit from AHI, you should run your queries on the Writer node or even on an async slave, or have a look on AWS RDS which does not have this limitation or use EC2 instances. You could also check query cache in Aurora.
Query Cache
In Aurora, they reworked the Query Cache which does not have the limitations like in Community Edition or in Percona Server. Cacheable queries take out an “exclusive lock” on MySQL’s query cache. In the real world, that means only one query can use the Query Cache at a time and all the other queries have to wait for the mutex. Also in MySQL 8.0 they completely removed the Query Cache.
But in Aurora they redesigned it and they removed this limitation – there is no single global mutex on the Query Cache anymore. I think one of the reasons for this is could be because they knew that Adaptive Hash won’t work.
Does AWS know about this?
I have created a ticket to AWS engineers to get some feedback on this, and they verified my findings and have confirmed Adaptive Hash Index cannot be enabled on the Reader nodes. They are looking into why we can modify it on the control panel.
Conclusion
I would recommend checking your queries on your Reader nodes to make sure they perform well and compare the performance with the Writer node. At this moment, we cannot enable AHI on Reader nodes, and I am not sure if that will change any time soon. But this can impact the performance in some cases, for sure.
05
2019
How to Upgrade Amazon Aurora MySQL from 5.6 to 5.7
Over time, software evolves and it is important to stay up to date if you want to benefit from new features and performance improvements. Database engines follow the exact same logic and providers are always careful to provide an easy upgrade path. With MySQL, the mysql_upgrade tool serves that purpose.
A database upgrade process becomes more challenging in a managed environment like AWS RDS where you don’t have shell access to the database host and don’t have access to the SUPER MySQL privilege. This post is a collaboration between Fattmerchant and Percona following an engagement focused on the upgrade of the Fattmerchant database from Amazon Aurora MySQL 5.6 to Amazon Aurora MySQL 5.7. Jacques Fu, the CTO of Fattmerchant, is the co-author of this post. Our initial plan was to follow a path laid out previously by others but we had difficulties finding any complete and detailed procedure outlining the steps. At least, with this post, there is now one.
Issues with the regular upgrade procedure
How do we normally upgrade a busy production server with minimal downtime? The simplest solution is to use a slave server with the newer version. Such a procedure has the side benefit of providing a “staging” database server which can be used to test the application with the new version. Basically we need to follow these steps:
- Enable replication on the old server
- Make a consistent backup
- Restore the backup on a second server with the newer database version – it can be a temporary server
- Run mysql_upgrade if needed
- Configure replication with the old server
- Test the application against the new version. If the tests includes conflicting writes, you may have to jump back to step 3
- If tests are OK and the new server is in sync, replication wise, with the old server, stop the application (only for a short while)
- Repoint the application to the new server
- Reset the slave
- Start the application
If the new server was temporary, you’ll need to repeat most of the steps the other way around, this time starting from the new server and ending on the old one.
What we thought would be a simple task turned out to be much more complicated. We were preparing to upgrade our database from Amazon Aurora MySQL 5.6 to 5.7 when we discovered that there was no option for an in-place upgrade. Unlike a standard AWS RDS MySQL (RDS MySQL upgrade 5.6 to 5.7) at the time of this article you cannot perform an in-place upgrade or even restore a backup across the major versions of Amazon Aurora MySQL.
We initially chose Amazon Aurora for the benefits of the tuning work that AWS provided out of the box, but we realized with any set of pros there comes a list of cons. In this case, the limitations meant that something that should have been straightforward took us off the documented path.
Our original high-level plan
Since we couldn’t use an RDS snapshot to provision a new Amazon Aurora MySQL 5.7 instance, we had to fallback to the use of a logical backup. The intended steps were:
- Backup the Amazon Aurora MySQL 5.6 write node with mysqldump
- Spin up an empty Amazon Aurora MySQL 5.7 cluster
- Restore the backup
- Make the Amazon Aurora MySQL 5.7 write node a slave of the Amazon Aurora MySQL 5.6 write node
- Once in sync, transfer the application to the Amazon Aurora MySQL 5.7 cluster
Even those simple steps proved to be challenging.
Backup of the Amazon Aurora MySQL 5.6 cluster
First, the Amazon Aurora MySQL 5.6 write node must generate binary log files. The default cluster parameter group that is generated when creating an Amazon Aurora instance does not enable these settings. Our 5.6 write node was not generating binary log files, so we copied the default cluster parameter group to a new “replication” parameter group and changed the “binlog_format” variable to MIXED. The parameter is only effective after a reboot, so overnight we rebooted the node. That was a first short downtime.
At that point, we were able to confirm, using “show master status;” that the write node was indeed generating binlog files. Since our procedure involves a logical backup and restore, we had to make sure the binary log files are kept for a long enough time. With a regular MySQL server the variable “expire_logs_days” controls the binary log files retention time. With RDS, you have to use the mysql.rds_set_configuration. We set the retention time to two weeks:
CALL mysql.rds_set_configuration('binlog retention hours', 336);
You can confirm the new setting is used with:
CALL mysql.rds_show_configuration;
For the following step, we needed a mysqldump backup along with its consistent replication coordinates. The option
--master-data
of mysqldump implies “Flush table with read lock;” while the replication coordinates are read from the server. A “Flush table” requires the SUPER privilege and this privilege is not available in RDS.
Since we wanted to avoid downtime, it is out of question to pause the application for the time it would take to backup 100GB of data. The solution was to take a snapshot and use it to provision a temporary Amazon Aurora MySQL 5.6 cluster of one node. As part of the creation process, the events tab of the AWS console will show the binary log file and position consistent with the snapshot, it looks like this:

Consistent snapshot replication coordinates
From there, the temporary cluster is idle so it is easy to back it up with mysqldump. Since our dataset is large we considered the use of MyDumper but the added complexity was not worthwhile for a one time operation. The dump of a large database can take many hours. Essentially we performed:
mysqldump -h entrypoint-temporary-cluster -u awsrootuser -pxxxx \ --no-data --single-transaction -R -E -B db1 db2 db3 > schema.sql mysqldump -h entrypoint-temporary-cluster -nt --single-transaction \ -u awsrootuser -pxxxx -B db1 db2 db3 | gzip -1 > dump.sql.gz pt-show-grants -h entrypoint-temporary-cluster -u awsrootuser -pxxxx > grants.sql
The schema consist of three databases: db1, db2 and db3. We have not included the mysql schema because it will cause issues with the new 5.7 instance. You’ll see why we dumped the schema and the data separately in the next section.
Restore to an empty Amazon Aurora MySQL 5.7 cluster
With our backup done, we are ready to spin up a brand new Amazon Aurora MySQL 5.7 cluster and restore the backup. Make sure the new Amazon Aurora MySQL 5.7 cluster is in a subnet with access to the Amazon Aurora MySQL 5.6 production cluster. In our schema, there a few very large tables with a significant number of secondary keys. To speed up the restore, we removed the secondary indexes of these tables from the schema.sql file and created a restore-indexes.sql file with the list of alter table statements needed to recreate them. Then we restored the data using these steps:
cat grants.sql | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx cat schema-modified.sql | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx zcat dump.sql.gz | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx cat restore-indexes.sql | mysql -h entrypoint-new-aurora-57 -u awsroot -pxxxx
Configure replication
At this point, we have a new Amazon Aurora MySQL 5.7 cluster provisioned with a dataset at a known replication coordinates from the Amazon Aurora MySQL 5.6 production cluster. It is now very easy to setup replication. First we need to create a replication user in the Amazon Aurora MySQL 5.6 production cluster:
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%' identified by 'agoodpassword';
Then, in the new Amazon Aurora MySQL 5.7 cluster, you configure replication and start it by:
CALL mysql.rds_set_external_master ('mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com', 3306, 'repl_user', 'agoodpassword', 'mysql-bin-changelog.000018', 65932380, 0); CALL mysql.rds_start_replication;
The endpoint mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com
points to the Amazon Aurora MySQL 5.6 production cluster.
Now, if everything went well, the new Amazon Aurora MySQL 5.7 cluster will be actively syncing with its master, the current Amazon Aurora MySQL 5.6 production cluster. This process can take a significant amount of time depending on the write load and the type of instance used for the new cluster. You can monitor the progress with the show slave status\G
command, the Seconds_Behind_Master will tell you how far behind in seconds the new cluster is compared to the old one. It is not a measurement of how long it will take to resync.
You can also monitor throughput using the AWS console. In this screenshot you can see the replication speeding up over time before it peaks when it is completed.

Replication speed
Test with Amazon Aurora MySQL 5.7
At this point, we have an Amazon Aurora MySQL 5.7 cluster in sync with the production Amazon Aurora MySQL 5.6 cluster. Before transferring the production load to the new cluster, you need to test your application with MySQL 5.7. The easiest way is to snapshot the new Amazon Aurora MySQL 5.7 cluster and, using the snapshot, provision a staging Amazon Aurora MySQL 5.7 cluster. Test your application against the staging cluster and, once tested, destroy the staging cluster and any unneeded snapshots.
Switch production to the Amazon Aurora MySQL 5.7 cluster
Now that you have tested your application with the staging cluster and are satisfied how it behaves with Amazon Aurora MySQL 5.7, the very last step is to migrate the production load. Here are the last steps you need to follow:
- Make sure the Amazon Aurora MySQL 5.7 cluster is still in sync with the Amazon Aurora MySQL 5.6 cluster
- Stop the application
- Validate the
Show master status;
of the 5.6 cluster is no longer moving - Validate from the
Show slave status\G
in the 5.7 cluster the Master_Log_File and Exec_Master_Log_Pos match the output of the “Show master status;” from the 5.6 cluster - Stop the slave in the 5.7 cluster with
CALL mysql.rds_stop_replication;
- Reset the slave in the 5.7 cluster with
CALL mysql.rds_reset_external_master;
- Reconfigure the application to use the 5.7 cluster endpoint
- Start the application
The application is down from steps 2 to 8. Although that might appear to be a long time, these steps can easily be executed within a few minutes.
Summary
So, in summary, although RDS Aurora doesn’t support an in place upgrade between Amazon Aurora MySQL 5.6 and 5.7, there is a possible migration path, minimizing downtime. In our case, we were able to limit the downtime to only a few minutes.
Co-Author: Jacques Fu, Fattmerchant
Jacques is CTO and co-founder at the fintech startup Fattmerchant, author of Time Hacks, and co-founder of the Orlando Devs, the largest developer meetup in Orlando. He has a passion for building products, bringing them to market, and scaling them.
17
2019
Using Parallel Query with Amazon Aurora for MySQL
Parallel query execution is my favorite, non-existent, feature in MySQL. In all versions of MySQL – at least at the time of writing – when you run a single query it will run in one thread, effectively utilizing one CPU core only. Multiple queries run at the same time will be using different threads and will utilize more than one CPU core.
On multi-core machines – which is the majority of the hardware nowadays – and in the cloud, we have multiple cores available for use. With faster disks (i.e. SSD) we can’t utilize the full potential of IOPS with just one thread.
AWS Aurora (based on MySQL 5.6) now has a version which will support parallelism for SELECT queries (utilizing the read capacity of storage nodes underneath the Aurora cluster). In this article, we will look at how this can improve the reporting/analytical query performance in MySQL. I will compare AWS Aurora with MySQL (Percona Server) 5.6 running on an EC2 instance of the same class.
In Short
Aurora Parallel Query response time (for queries which can not use indexes) can be 5x-10x better compared to the non-parallel fully cached operations. This is a significant improvement for the slow queries.
Test data and versions
For my test, I need to choose:
- Aurora instance type and comparison
- Dataset
- Queries
Aurora instance type and comparison
According to Jeff Barr’s excellent article (https://aws.amazon.com/blogs/aws/new-parallel-query-for-amazon-aurora/) the following instance classes will support parallel query (PQ):
“The instance class determines the number of parallel queries that can be active at a given time:
- db.r*.large – 1 concurrent parallel query session
- db.r*.xlarge – 2 concurrent parallel query sessions
- db.r*.2xlarge – 4 concurrent parallel query sessions
- db.r*.4xlarge – 8 concurrent parallel query sessions
- db.r*.8xlarge – 16 concurrent parallel query sessions
- db.r4.16xlarge – 16 concurrent parallel query sessions”
As I want to maximize the concurrency of parallel query sessions, I have chosen db.r4.8xlarge. For the EC2 instance I will use the same class: r4.8xlarge.
Aurora:
mysql> show global variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | aurora_version | 1.18.0 | | innodb_version | 1.2.10 | | protocol_version | 10 | | version | 5.6.10 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+
MySQL on ec2
mysql> show global variables like '%version%'; +-------------------------+------------------------------------------------------+ | Variable_name | Value | +-------------------------+------------------------------------------------------+ | innodb_version | 5.6.41-84.1 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1.1,TLSv1.2 | | version | 5.6.41-84.1 | | version_comment | Percona Server (GPL), Release 84.1, Revision b308619 | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | | version_suffix | | +-------------------------+------------------------------------------------------+
Table
I’m using the “Airlines On-Time Performance” database from http://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time (You can find the scripts I used here: https://github.com/Percona-Lab/ontime-airline-performance).
mysql> show table status like 'ontime'\G *************************** 1. row *************************** Name: ontime Engine: InnoDB Version: 10 Row_format: Compact Rows: 173221661 Avg_row_length: 409 Data_length: 70850183168 Max_data_length: 0 Index_length: 0 Data_free: 7340032 Auto_increment: NULL Create_time: 2018-09-26 02:03:28 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
The table is very wide, 84 columns.
Working with Aurora PQ (Parallel Query)
Documentation: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-parallel-query.html
Aurora PQ works by doing a full table scan (parallel reads are done on the storage level). The InnoDB buffer pool is not used when Parallel Query is utilized.
For the purposes of the test I turned PQ on and off (normally AWS Aurora uses its own heuristics to determine if the PQ will be helpful or not):
Turn on and force:
mysql> set session aurora_pq = 1; Query OK, 0 rows affected (0.00 sec) mysql> set aurora_pq_force = 1; Query OK, 0 rows affected (0.00 sec)
Turn off:
mysql> set session aurora_pq = 0; Query OK, 0 rows affected (0.00 sec)
The EXPLAIN plan in MySQL will also show the details about parallel query execution statistics.
Queries
Here, I use the “reporting” queries, running only one query at a time. The queries are similar to those I’ve used in older blog posts comparing MySQL and Apache Spark performance (https://www.percona.com/blog/2016/08/17/apache-spark-makes-slow-mysql-queries-10x-faster/ )
Here is a summary of the queries:
- Simple queries:
-
select count(*) from ontime where flightdate > '2017-01-01'
-
select avg(DepDelay/ArrDelay+1) from ontime
-
- Complex filter, single table:
select SQL_CALC_FOUND_ROWS FlightDate, UniqueCarrier as carrier, FlightNum, Origin, Dest FROM ontime WHERE DestState not in ('AK', 'HI', 'PR', 'VI') and OriginState not in ('AK', 'HI', 'PR', 'VI') and flightdate > '2015-01-01' and ArrDelay < 15 and cancelled = 0 and Diverted = 0 and DivAirportLandings = 0 ORDER by DepDelay DESC LIMIT 10;
3. Complex filter, join “reference” table
select SQL_CALC_FOUND_ROWS FlightDate, UniqueCarrier, TailNum, FlightNum, Origin, OriginCityName, Dest, DestCityName, DepDelay, ArrDelay FROM ontime_ind o JOIN carriers c on o.carrier = c.carrier_code WHERE (carrier_name like 'United%' or carrier_name like 'Delta%') and ArrDelay > 30 ORDER by DepDelay DESC LIMIT 10\G
4. select one row only, no index
Query 1a: simple, count(*)
Let’s take a look at the most simple query: count(*). This variant of the “ontime” table has no secondary indexes.
select count(*) from ontime where flightdate > '2017-01-01';
Aurora, pq (parallel query) disabled:
I disabled the PQ first to compare:
mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (8 min 25.49 sec) mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (2 min 48.81 sec) mysql> mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (2 min 48.25 sec) Please note: the first run was “cold run”; data was read from disk. The second and third run used the cached data. Now let's enable and force Aurora PQ: mysql> set session aurora_pq = 1; Query OK, 0 rows affected (0.00 sec) mysql> set aurora_pq_force = 1; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from ontime where flightdate > '2017-01-01'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 173706586 Extra: Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) 1 row in set (0.00 sec)
(from the EXPLAIN plan, we can see that parallel query is used).
Results:
mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (16.53 sec) mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (16.56 sec) mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (16.36 sec) mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (16.56 sec) mysql> select count(*) from ontime where flightdate > '2017-01-01'; +----------+ | count(*) | +----------+ | 5660651 | +----------+ 1 row in set (16.36 sec)
As we can see the results are very stable. It does not use any cache (ie: innodb buffer pool) either. The result is also interesting: utilizing multiple threads (up to 16 threads) and reading data from disk (using disk cache, probably) can be ~10x faster compared to reading from memory in a single thread.
Result: ~10x performance gain, no index used
Query 1b: simple, avg
set aurora_pq = 1; set aurora_pq_force=1; select avg(DepDelay) from ontime; +---------------+ | avg(DepDelay) | +---------------+ | 8.2666 | +---------------+ 1 row in set (1 min 48.17 sec) set aurora_pq = 0; set aurora_pq_force=0; select avg(DepDelay) from ontime; +---------------+ | avg(DepDelay) | +---------------+ | 8.2666 | +---------------+ 1 row in set (2 min 49.95 sec) Here we can see that PQ gives use ~2x performance increase.
Summary of simple query performance
Here is what we learned comparing Aurora PQ performance to native MySQL query execution:
- Select count(*), not using index: 10x performance increase with Aurora PQ.
- select avg(…), not using index: 2x performance increase with Aurora PQ.
Query 2: Complex filter, single table
The following query will always be slow in MySQL. This combination of the filters in the WHERE condition makes it extremely hard to prepare a good set of indexes to make this query faster.
select SQL_CALC_FOUND_ROWS FlightDate, UniqueCarrier as carrier, FlightNum, Origin, Dest FROM ontime WHERE DestState not in ('AK', 'HI', 'PR', 'VI') and OriginState not in ('AK', 'HI', 'PR', 'VI') and flightdate > '2015-01-01' and ArrDelay < 15 and cancelled = 0 and Diverted = 0 and DivAirportLandings = '0' ORDER by DepDelay DESC LIMIT 10;
Let’s compare the query performance with and without PQ.
PQ disabled:
mysql> set aurora_pq_force = 0; Query OK, 0 rows affected (0.00 sec) mysql> set aurora_pq = 0; Query OK, 0 rows affected (0.00 sec) mysql> explain select SQL_CALC_FOUND_ROWS FlightDate, UniqueCarrier as carrier, FlightNum, Origin, Dest FROM ontime WHERE DestState not in ('AK', 'HI', 'PR', 'VI') and OriginState not in ('AK', 'HI', 'PR', 'VI') and flightdate > '2015-01-01' and ArrDelay < 15 and cancelled = 0 and Diverted = 0 and DivAirportLandings = 0 ORDER by DepDelay DESC LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 173706586 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> select SQL_CALC_FOUND_ROWS FlightDate, UniqueCarrier as carrier, FlightNum, Origin, Dest FROM ontime WHERE DestState not in ('AK', 'HI', 'PR', 'VI') and OriginState not in ('AK', 'HI', 'PR', 'VI') and flightdate > '2015-01-01' and ArrDelay < 15 and cancelled = 0 and Diverted = 0 and DivAirportLandings = 0 ORDER by DepDelay DESC LIMIT 10; +------------+---------+-----------+--------+------+ | FlightDate | carrier | FlightNum | Origin | Dest | +------------+---------+-----------+--------+------+ | 2017-10-09 | OO | 5028 | SBP | SFO | | 2015-11-03 | VX | 969 | SAN | SFO | | 2015-05-29 | VX | 720 | TUL | AUS | | 2016-03-11 | UA | 380 | SFO | BOS | | 2016-06-13 | DL | 2066 | JFK | SAN | | 2016-11-14 | UA | 1600 | EWR | LAX | | 2016-11-09 | WN | 2318 | BDL | LAS | | 2016-11-09 | UA | 1652 | IAD | LAX | | 2016-11-13 | AA | 23 | JFK | LAX | | 2016-11-12 | UA | 800 | EWR | SFO | +------------+---------+-----------+--------+------+
10 rows in set (3 min 42.47 sec)
/* another run */
10 rows in set (3 min 46.90 sec)
This query is 100% cached. Here is the graph from PMM showing the number of read requests:
- Read requests: logical requests from the buffer pool
- Disk reads: physical requests from disk
Buffer pool requests:
Now let’s enable and force PQ:
PQ enabled:
mysql> set session aurora_pq = 1; Query OK, 0 rows affected (0.00 sec) mysql> set aurora_pq_force = 1; Query OK, 0 rows affected (0.00 sec) mysql> explain select SQL_CALC_FOUND_ROWS FlightDate, UniqueCarrier as carrier, FlightNum, Origin, Dest FROM ontime WHERE DestState not in ('AK', 'HI', 'PR', 'VI') and OriginState not in ('AK', 'HI', 'PR', 'VI') and flightdate > '2015-01-01' and ArrDelay < 15 and cancelled = 0 and Diverted = 0 and DivAirportLandings = 0 ORDER by DepDelay DESC LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ontime type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 173706586 Extra: Using where; Using filesort; Using parallel query (12 columns, 4 filters, 3 exprs; 0 extra) 1 row in set (0.00 sec) mysql> select SQL_CALC_FOUND_ROWS -> FlightDate, UniqueCarrier as carrier, FlightNum, Origin, Dest -> FROM ontime -> WHERE -> DestState not in ('AK', 'HI', 'PR', 'VI') -> and OriginState not in ('AK', 'HI', 'PR', 'VI') -> and flightdate > '2015-01-01' -> and ArrDelay < 15 -> and cancelled = 0 -> and Diverted = 0 -> and DivAirportLandings = 0 -> ORDER by DepDelay DESC -> LIMIT 10; +------------+---------+-----------+--------+------+ | FlightDate | carrier | FlightNum | Origin | Dest | +------------+---------+-----------+--------+------+ | 2017-10-09 | OO | 5028 | SBP | SFO | | 2015-11-03 | VX | 969 | SAN | SFO | | 2015-05-29 | VX | 720 | TUL | AUS | | 2016-03-11 | UA | 380 | SFO | BOS | | 2016-06-13 | DL | 2066 | JFK | SAN | | 2016-11-14 | UA | 1600 | EWR | LAX | | 2016-11-09 | WN | 2318 | BDL | LAS | | 2016-11-09 | UA | 1652 | IAD | LAX | | 2016-11-13 | AA | 23 | JFK | LAX | | 2016-11-12 | UA | 800 | EWR | SFO | +------------+---------+-----------+--------+------+ 10 rows in set (41.88 sec) /* run 2 */ 10 rows in set (28.49 sec) /* run 3 */ 10 rows in set (29.60 sec)
Now let’s compare the requests:
As we can see, Aurora PQ is almost NOT utilizing the buffer pool (there are a minor number of read requests. Compare the max of 4K requests per second with PQ to the constant 600K requests per second in the previous graph).
Result: ~8x performance gain
Query 3: Complex filter, join “reference” table
In this example I join two tables: the main “ontime” table and a reference table. If we have both tables without indexes it will simply be too slow in MySQL. To make it better, I have created an index for both tables and so it will use indexes for the join:
CREATE TABLE `carriers` ( `carrier_code` varchar(8) NOT NULL DEFAULT '', `carrier_name` varchar(200) DEFAULT NULL, PRIMARY KEY (`carrier_code`), KEY `carrier_name` (`carrier_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show create table ontime_ind\G ... PRIMARY KEY (`id`), KEY `comb1` (`Carrier`,`Year`,`ArrDelayMinutes`), KEY `FlightDate` (`FlightDate`) ) ENGINE=InnoDB AUTO_INCREMENT=178116912 DEFAULT CHARSET=latin1
Query:
select SQL_CALC_FOUND_ROWS FlightDate, UniqueCarrier, TailNum, FlightNum, Origin, OriginCityName, Dest, DestCityName, DepDelay, ArrDelay FROM ontime_ind o JOIN carriers c on o.carrier = c.carrier_code WHERE (carrier_name like 'United%' or carrier_name like 'Delta%') and ArrDelay > 30 ORDER by DepDelay DESC LIMIT 10\G
PQ disabled, explain plan:
mysql> set aurora_pq_force = 0; Query OK, 0 rows affected (0.00 sec) mysql> set aurora_pq = 0; Query OK, 0 rows affected (0.00 sec) mysql> explain -> select SQL_CALC_FOUND_ROWS -> FlightDate, UniqueCarrier, TailNum, FlightNum, Origin, OriginCityName, Dest, DestCityName, DepDelay, ArrDelay -> FROM ontime_ind o -> JOIN carriers c on o.carrier = c.carrier_code -> WHERE -> (carrier_name like 'United%' or carrier_name like 'Delta%') -> and ArrDelay > 30 -> ORDER by DepDelay DESC -> LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: range possible_keys: PRIMARY,carrier_name key: carrier_name key_len: 203 ref: NULL rows: 3 Extra: Using where; Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: o type: ref possible_keys: comb1 key: comb1 key_len: 3 ref: ontime.c.carrier_code rows: 2711597 Extra: Using index condition; Using where 2 rows in set (0.01 sec)
As we can see MySQL uses indexes for the join. Response times:
/* run 1 – cold run */
10 rows in set (29 min 17.39 sec)
/* run 2 – warm run */
10 rows in set (2 min 45.16 sec)
PQ enabled, explain plan:
mysql> explain -> select SQL_CALC_FOUND_ROWS -> FlightDate, UniqueCarrier, TailNum, FlightNum, Origin, OriginCityName, Dest, DestCityName, DepDelay, ArrDelay -> FROM ontime_ind o -> JOIN carriers c on o.carrier = c.carrier_code -> WHERE -> (carrier_name like 'United%' or carrier_name like 'Delta%') -> and ArrDelay > 30 -> ORDER by DepDelay DESC -> LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: c type: ALL possible_keys: PRIMARY,carrier_name key: NULL key_len: NULL ref: NULL rows: 1650 Extra: Using where; Using temporary; Using filesort; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) *************************** 2. row *************************** id: 1 select_type: SIMPLE table: o type: ALL possible_keys: comb1 key: NULL key_len: NULL ref: NULL rows: 173542245 Extra: Using where; Using join buffer (Hash Join Outer table o); Using parallel query (11 columns, 1 filters, 1 exprs; 0 extra) 2 rows in set (0.00 sec)
As we can see, Aurora does not use any indexes and uses a parallel scan instead.
Response time:
mysql> select SQL_CALC_FOUND_ROWS -> FlightDate, UniqueCarrier, TailNum, FlightNum, Origin, OriginCityName, Dest, DestCityName, DepDelay, ArrDelay -> FROM ontime_ind o -> JOIN carriers c on o.carrier = c.carrier_code -> WHERE -> (carrier_name like 'United%' or carrier_name like 'Delta%') -> and ArrDelay > 30 -> ORDER by DepDelay DESC -> LIMIT 10\G ... *************************** 4. row *************************** FlightDate: 2017-05-04 UniqueCarrier: UA TailNum: N68821 FlightNum: 1205 Origin: KOA OriginCityName: Kona, HI Dest: LAX DestCityName: Los Angeles, CA DepDelay: 1457 ArrDelay: 1459 *************************** 5. row *************************** FlightDate: 1991-03-12 UniqueCarrier: DL TailNum: FlightNum: 1118 Origin: ATL OriginCityName: Atlanta, GA Dest: STL DestCityName: St. Louis, MO ... 10 rows in set (28.78 sec) mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 4180974 | +--------------+ 1 row in set (0.00 sec)
Result: ~5x performance gain
(this is actually comparing the index cached read to a non-index PQ execution)
Summary
Aurora PQ can significantly improve the performance of reporting queries as such queries may be extremely hard to optimize in MySQL, even when using indexes. With indexes, Aurora PQ response time can be 5x-10x better compared to the non-parallel, fully cached operations. Aurora PQ can help improve performance of complex queries by performing parallel reads.
The following table summarizes the query response times:
Query | Time, No PQ, index | Time, PQ |
select count(*) from ontime where flightdate > ‘2017-01-01’ | 2 min 48.81 sec | 16.53 sec |
select avg(DepDelay) from ontime; | 2 min 49.95 sec | 1 min 48.17 sec |
select SQL_CALC_FOUND_ROWS
FlightDate, UniqueCarrier as carrier, FlightNum, Origin, Dest FROM ontime WHERE DestState not in (‘AK’, ‘HI’, ‘PR’, ‘VI’) and OriginState not in (‘AK’, ‘HI’, ‘PR’, ‘VI’) and flightdate > ‘2015-01-01’ and ArrDelay < 15 and cancelled = 0 and Diverted = 0 and DivAirportLandings = 0 ORDER by DepDelay DESC LIMIT 10; |
3 min 42.47 sec | 28.49 sec |
select SQL_CALC_FOUND_ROWS
FlightDate, UniqueCarrier, TailNum, FlightNum, Origin, OriginCityName, Dest, DestCityName, DepDelay, ArrDelay FROM ontime_ind o JOIN carriers c on o.carrier = c.carrier_code WHERE (carrier_name like ‘United%’ or carrier_name like ‘Delta%’) and ArrDelay > 30 ORDER by DepDelay DESC LIMIT 10\G |
2 min 45.16 sec | 28.78 sec |
—
Photo by Thomas Lipke on Unsplash
11
2019
AWS Aurora MySQL – HA, DR, and Durability Explained in Simple Terms
It’s a few weeks after AWS re:Invent 2018 and my head is still spinning from all of the information released at this year’s conference. This year I was able to enjoy a few sessions focused on Aurora deep dives. In fact, I walked away from the conference realizing that my own understanding of High Availability (HA), Disaster Recovery (DR), and Durability in Aurora had been off for quite a while. Consequently, I decided to put this blog out there, both to collect the ideas in one place for myself, and to share them in general. Unlike some of our previous blogs, I’m not focused on analyzing Aurora performance or examining the architecture behind Aurora. Instead, I want to focus on how HA, DR, and Durability are defined and implemented within the Aurora ecosystem. We’ll get just deep enough into the weeds to be able to examine these capabilities alone.
Aurora MySQL – What is it?
We’ll start with a simplified discussion of what Aurora is from a very high level. In its simplest description, Aurora MySQL is made up of a MySQL-compatible compute layer and a multi-AZ (multi availability zone) storage layer. In the context of an HA discussion, it is important to start at this level, so we understand the redundancy that is built into the platform versus what is optional, or configurable.
Aurora Storage
The Aurora Storage layer presents a volume to the compute layer. This volume is built out in 10GB increments called protection groups. Each protection group is built from six storage nodes, two from each of three availability zones (AZs). These are represented in the diagram above in green. When the compute layer—represented in blue—sends a write I/O to the storage layer, the data gets replicated six times across three AZs.
Durable by Default
In addition to the six-way replication, Aurora employs a 4-of-6 quorum for all write operations. This means that for each commit that happens at the database compute layer, the database node waits until it receives write acknowledgment from at least four out of six storage nodes. By receiving acknowledgment from four storage nodes, we know that the write has been saved in at least two AZs. The storage layer itself has intelligence built-in to ensure that each of the six storage nodes has a copy of the data. This does not require any interaction with the compute tier. By ensuring that there are always at least four copies of data, across at least two datacenters (AZs), and ensuring that the storage nodes are self-healing and always maintain six copies, it can be said that the Aurora Storage platform has the characteristic of Durable by Default. The Aurora storage architecture is the same no matter how large or small your Aurora compute architecture is.
One might think that waiting to receive four acknowledgments represents a lot of I/O time and is therefore an expensive write operation. However, Aurora database nodes do not behave the way a typical MySQL database instance would. Some of the round-trip execution time is mitigated by the way in which Aurora MySQL nodes write transactions to disk. For more information on exactly how this works, check out Amazon Senior Engineering Manager, Kamal Gupta’s deep-dive into Aurora MySQL from AWS re:Invent 2018.
HA and DR Options
While durability can be said to be a default characteristic to the platform, HA and DR are configurable capabilities. Let’s take a look at some of the HA and DR options available. Aurora databases are deployed as members of an Aurora DB Cluster. The cluster configuration is fairly flexible. Database nodes are given the roles of either Writer or Reader. In most cases, there will only be one Writer node. The Reader nodes are known as Aurora Replicas. A single Aurora Cluster may contain up to 15 Aurora Replicas. We’ll discuss a few common configurations and the associated levels of HA and DR which they provide. This is only a sample of possible configurations: it is not meant to represent an exhaustive list of the possible configuration options available on the Aurora platform.
Single-AZ, Single Instance Deployment
The most basic implementation of Aurora is a single compute instance in a single availability zone. The compute instance is monitored by the Aurora Cluster service and will be restarted if the database instance or compute VM has a failure. In this architecture, there is no redundancy at the compute level. Therefore, there is no database level HA or DR. The storage tier provides the same high level of durability described in the sections above. The image below is a view of what this configuration looks like in the AWS Console.
Single-AZ, Multi-Instance
HA can be added to a basic Aurora implementation by adding an Aurora Replica. We increase our HA level by adding Aurora Replicas within the same AZ. If desired, the Aurora Replicas can be used to also service some of the read traffic for the Aurora Cluster. This configuration cannot be said to provide DR because there are no database nodes outside the single datacenter or AZ. If that datacenter were to fail, then database availability would be lost until it was manually restored in another datacenter (AZ). It’s important to note that while Aurora has a lot of built-in automation, you will only benefit from that automation if your base configuration facilitates a path for the automation to follow. If you have a single-AZ base deployment, then you will not have the benefit of automated Multi-AZ availability. However, as in the previous case, durability remains the same. Again, durability is a characteristic of the storage layer. The image below is a view of what this configuration looks like in the AWS Console. Note that the Writer and Reader are in the same AZ.
Multi-AZ Options
Building on our previous example, we can increase our level of HA and add partial DR capabilities to the configuration by adding more Aurora Replicas. At this point we will add one additional replica in the same AZ, bringing the local AZ replica count to three database instances. We will also add one replica in each of the two remaining regional AZs. Aurora provides the option to configure automated failover priority for the Aurora Replicas. Choosing your failover priority is best defined by the individual business needs. That said, one way to define the priority might be to set the first failover to the local-AZ replicas, and subsequent failover priority to the replicas in the other AZs. It is important to remember that AZs within a region are physical datacenters located within the same metro area. This configuration will provide protection for a disaster localized to the datacenter. It will not, however, provide protection for a city-wide disaster. The image below is a view of what this configuration looks like in the AWS Console. Note that we now have two Readers in the same AZ as the Writer and two Readers in two other AZs.
Cross-Region Options
The three configuration types we’ve discussed up to this point represent configuration options available within an AZ or metro area. There are also options available for cross-region replication in the form of both logical and physical replication.
Logical Replication
Aurora supports replication to up to five additional regions with logical replication. It is important to note that, depending on the workload, logical replication across regions can be notably susceptible to replication lag.
Physical Replication
One of the many announcements to come out of re:Invent 2018 is a product called Aurora Global Database. This is Aurora’s implementation of cross-region physical replication. Amazon’s published details on the solution indicate that it is storage level replication implemented on dedicated cross-region infrastructure with sub-second latency. In general terms, the idea behind a cross-region architecture is that the second region could be an exact duplicate of the primary region. This means that the primary region can have up to 15 Aurora Replicas and the secondary region can also have up to 15 Aurora Replicas. There is one database instance in the secondary region in the role of writer for that region. This instance can be configured to take over as the master for both regions in the case of a regional failure. In this scenario the secondary region becomes primary, and the writer in that region becomes the primary database writer. This configuration provides protection in the case of a regional disaster. It’s going to take some time to test this, but at the moment this architecture appears to provide the most comprehensive combination of Durability, HA, and DR. The trade-offs have yet to be thoroughly explored.
Multi-Master Options
Amazon is in the process of building out a new capability called Aurora Multi-Master. Currently, this feature is in preview phase and has not been released for general availability. While there were a lot of talks at re:Invent 2018 which highlighted some of the components of this feature, there is still no affirmative date for release. Early analysis points to the feature being localized to the AZ. It is not known if cross-region Multi-Master will be supported, but it seems unlikely.
Summary
As a post re:Invent takeaway, what I learned was that there is an Aurora configuration to fit almost any workload that requires strong performance behind it. Not all heavy workloads also demand HA and DR. If this describes one of your workloads, then there is an Aurora configuration that fits your needs. On the flip side, it is also important to remember that while data durability is an intrinsic quality of Aurora, HA and DR are not. These are completely configurable. This means that the Aurora architect in your organization must put thought and due diligence into the way they design your Aurora deployment. While we all need to be conscious of costs, don’t let cost consciousness become a blinder to reality. Just because your environment is running in Aurora does not mean you automatically have HA and DR for your database. In Aurora, HA and DR are configuration options, and just like the on-premise world, viable HA and DR have additional costs associated with them.
For More Information See Also:
- https://www.youtube.com/watch?v=U42mC_iKSBg
- https://www.youtube.com/watch?v=2WG01wJIGSQ
- https://www.youtube.com/watch?v=ZOIkOnW640A&t=3465s
- https://www.slideshare.net/AmazonWebServices/whats-new-in-amazon-aurora-dat204r1-aws-reinvent-2018
- https://www.slideshare.net/AmazonWebServices/deep-dive-on-amazon-aurora-mysql-performance-tuning-dat429r1-aws-reinvent-2018
- https://www.slideshare.net/AmazonWebServices/ask-me-anything-about-amazon-aurora-dat369r1-aws-reinvent-2018
- https://www.slideshare.net/AmazonWebServices/amazon-aurora-storage-demystified-how-it-all-works-dat363-aws-reinvent-2018
- https://aws.amazon.com/about-aws/whats-new/2018/11/announcing-amazon-aurora-global-database/
- https://pages.awscloud.com/amazon-aurora-multimaster-preview.html
09
2019
Amazon Aurora Serverless – The Sleeping Beauty
One of the most exciting features Amazon Aurora Serverless brings to the table is its ability to go to sleep (pause) when idle. This is a fantastic feature for development and test environments. You get access to a powerful database to run tests quickly, but it goes easy on your wallet as you only pay for storage when the instance is paused.
You can configure Amazon RDS Aurora Serverless to go to sleep after a specified period of time. This can be set to anywhere between five minutes and 24 hours
For this feature to work, however, inactivity has to be complete. If you have so much as a single query or even maintain an idle open connection, Amazon Aurora Serverless will not be able to pause.
This means, for example, that pretty much any monitoring you may have enabled, including our own Percona Monitoring and Management (PMM) will prevent the instance from pausing. It would be great if Amazon RDS Aurora Serverless would allow us to specify user accounts to ignore, or additional service endpoints which should not prevent it from pausing, but currently you need to get by without such monitoring and diagnostic tools, or else enable them only for duration of the test run.
If you’re using Amazon Aurora Serverless to back very low traffic applications, you might consider disabling the automatic pause function, since waking up currently takes quite a while. Otherwise, your users should be prepared for a 30+ seconds wait while Amazon Aurora Serverless activates.
Having such a high time to activate means you need to be mindful of timeout configuration in your test/dev scripts so you do not have to deal with sporadic failures. Or you can also use something like the mysqladmin ping command to activate the instance before your test run.
Some activation experiments
Let’s now take a closer look at Amazon RDS Aurora Serverless activation times. These times are measured for MySQL 5.6 based Aurora Serverless – the only one currently available. I expect numbers could be different in other editions
I measured the time it takes to run a trivial query (SELECT 1) after the instance goes to sleep. You’ll see I manually scaled the Amazon RDS Aurora Serverless instance to a desired capacity in ACU (Aurora Compute Units), and then had the script wait for six minutes to allow for pause to happen before running the query. The test was performed 12 times and the Min/Max/Avg times of these test runs for different settings of ACU are presented above.
You can see there is some variation between min and max times. I would expect to have even higher outliers, so plan for an activation time of more than a minute as a worst case scenario.
Also note that there is an interesting difference in the activation time between instance sizes. While in my tests the smallest possible size (2 ACU) consistently took longer to activate compared to the medium size (8 ACU), the even bigger size (64 ACU) was the slowest of all.
So make no assumptions about how long it would take for instance of given size to wake up with your workload, but rather test it if it is important consideration for you.
In some (rare) cases I also observed some internal timeouts during the resume process:
[root@ip-172-31-16-160 serverless]# mysqladmin ping -h serverless-test.cluster-XXXX.us-east-2.rds.amazonaws.com -u user -ppassword mysqladmin: connect to server at 'serverless-test.cluster-XXXX.us-east-2.rds.amazonaws.com' failed error: 'Database was unable to resume within timeout period.'
What about Autoscaling?
Finally, you may wonder how such Amazon Aurora Serverless pausing plays with Amazon Aurora Serverless Autoscaling ?
In my tests, I observed that resume always restores the instance size to the same ACU as it was before it was paused. However, this is where pausing configuration matters a great deal. According to this document, Amazon Aurora Serverless will not scale down more frequently than once per 900 seconds. While the document does not clarify over what period of time the conditions initiating scale down – cpu usage, connection usage etc – have to be met for scale down to be triggered, I can see that if the instance is idle for five minutes the scale down is not performed – it is just put to sleep.
At the same time, if you change this default five minute period to a longer time, the idle instance will be automatically scaled down a notch every 900 seconds before it finally goes to sleep. Consequently, when it is awakened it will not be at the last stage at which the load was applied, but instead at the stage it was at when it was scaled down. Also, scaling down is considered an event by itself, which resets the idle counter and delays the pause. For example: if the initial instance scale is 8, and the pause timer is set to 1h, it takes 1h 30 minutes for the pause to actually happen – 30 minutes to do scale down twice, plus 1 hour at the minimum size for pause to trigger
Here is a graph to illustrate this:
This also shows that when the load is re-applied at about 13:47, it recovers to the last number of ACU it had before the pause.
This means that a pause time of more than 15 minutes makes the pause behavior substantially different to the default.
Summary
- Amazon Aurora Serverless automatic pause is a great for test/dev environments.
- Resume time is relatively long, can reach as much as one minute.
- Consider disabling automatic pausing for low traffic production applications, or at least let your users know they need to wait when they wake up the application.
- Pause and Resume behavior is different in practice for a pause timeout of more than 15 minutes. Sticking to the default 5 minutes is recommended unless you really know what you’re doing.
04
2019
Amazon RDS Aurora MySQL – Differences Among Editions
Amazon Aurora with MySQL Compatibility comes in three editions which, at the time of writing, have quite a few differences around the features that they support. Make sure you don’t assume the newer Aurora 2.x supports everything in Aurora 1.x. On the contrary, right now Aurora 1.x (MySQL 5.6 based) supports most Aurora features. The serverless option was launched for this version, and it’s not based on the latest MySQL 5.7. However, the serverless option, too, has its own set of limitations
I found a concise comparison of what is available in which Amazon Aurora edition hard to come by so I’ve created one. The table was compiled based mostly on documentation research, so if you spot some mistakes please let me know and I’ll make a correction.
Please keep in mind, this is expected to change over time. For example Amazon Aurora 2.x was initially released without Performance_Schema support, which was enabled in later versions.
There seems to be lag porting Aurora features from MySQL 5.6 compatible to MySQL 5.7 compatible – the current 2.x release does not include features introduced in Aurora 1.16 or later as per this document
A comparison table
MySQL 5.6 Based | MySQL 5.7 Based | Serverless MySQL 5.6 Based | |
Compatible to MySQL | MySQL 5.6.10a | MySQL 5.7.12 | MySQL 5.6.10a |
Aurora Engine Version | 1.18.0 | 2.03.01 | 1.18.0 |
Parallel Query | Yes | No | No |
Backtrack | Yes | No | No |
Aurora Global Database | Yes | No | No |
Performance Insights | Yes | No | No |
SELECT INTO OUTFILE S3 | Yes | Yes | Yes |
Amazon Lambda – Native Function | Yes | No | No |
Amazon Lambda – Stored Procedure | Yes | Yes | Yes |
Hash Joins | Yes | No | Yes |
Fast DDL | Yes | Yes | Yes |
LOAD DATA FROM S3 | Yes | Yes | No |
Spatial Indexing | Yes | Yes | Yes |
Asynchronous Key Prefetch (AKP) | Yes | No | Yes |
Scan Batching | Yes | No | Yes |
S3 Backed Based Migration | Yes | No | No |
Advanced Auditing | Yes | Yes | No |
Aurora Replicas | Yes | Yes | No |
Database Cloning | Yes | Yes | No |
IAM database authentication | Yes | Yes | No |
Cross-Region Read Replicas | Yes | Yes | No |
Restoring Snapshot from MySQL DB | Yes | Yes | No |
Enhanced Monitoring | Yes | Yes | No |
Log Export to Cloudwatch | Yes | Yes | No |
Minor Version Upgrade Control | Yes | Yes | Always On |
Data Encryption Configuration | Yes | Yes | Always On |
Maintenance Window Configuration | Yes | Yes | No |
Hope this is helps with selecting which Amazon Aurora edition is right for you, when it comes to supported features.
—
Photo by Nathan Dumlao on Unsplash
20
2018
Percona Database Performance Blog 2018 Year in Review: Top Blog Posts
Let’s look at some of the most popular Percona Database Performance Blog posts in 2018.
The closing of a year lends itself to looking back. And making lists. With the Percona Database Performance Blog, Percona staff and leadership work hard to provide the open source community with insights, technical support, predictions and metrics around multiple open source database software technologies. We’ve had nearly 4 million visits to the blog in 2018: thank you! We look forward to providing you with even better articles, news and information in 2019.
As 2018 moves into 2019, let’s take a quick look back at some of the most popular posts on the blog this year.
Top 10 Most Read
These posts had the most number of views (working down from the highest):
When Should I Use Amazon Aurora and When Should I use RDS MySQL?
Now that Database-as-a-service (DBaaS) is in high demand, there is one question regarding AWS services that cannot always be answered easily : When should I use Aurora and when RDS MySQL?
ZFS has many very interesting features, but I am a bit tired of hearing negative statements on ZFS performance. It feels a bit like people are telling me “Why do you use InnoDB? I have read that MyISAM is faster.” I found the comparison of InnoDB vs. MyISAM quite interesting, and I’ll use it in this post.
Linux OS Tuning for MySQL Database Performance
In this post we will review the most important Linux settings to adjust for performance tuning and optimization of a MySQL database server. We’ll note how some of the Linux parameter settings used OS tuning may vary according to different system types: physical, virtual or cloud.
As the MyRocks storage engine (based on the RocksDB key-value store http://rocksdb.org ) is now available as part of Percona Server for MySQL 5.7, I wanted to take a look at how it performs on a relatively high-end server and SSD storage.
How to Restore MySQL Logical Backup at Maximum Speed
The ability to restore MySQL logical backups is a significant part of disaster recovery procedures. It’s a last line of defense.
Why MySQL Stored Procedures, Functions and Triggers Are Bad For Performance
MySQL stored procedures, functions and triggers are tempting constructs for application developers. However, as I discovered, there can be an impact on database performance when using MySQL stored routines. Not being entirely sure of what I was seeing during a customer visit, I set out to create some simple tests to measure the impact of triggers on database performance. The outcome might surprise you.
AMD EPYC Performance Testing… or Don’t get on the wrong side of SystemD
Ever since AMD released their EPYC CPU for servers I wanted to test it, but I did not have the opportunity until recently, when Packet.net started offering bare metal servers for a reasonable price. So I started a couple of instances to test Percona Server for MySQL under this CPU. In this benchmark, I discovered some interesting discrepancies in performance between AMD and Intel CPUs when running under systemd.
Tuning PostgreSQL Database Parameters to Optimize Performance
Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. Default values are set to ensure that PostgreSQL runs everywhere, with the least resources it can consume and so that it doesn’t cause any vulnerabilities. It is primarily the responsibility of the database administrator or developer to tune PostgreSQL according to their system’s workload. In this blog, we will establish basic guidelines for setting PostgreSQL database parameters to improve database performance according to workload.
Using AWS EC2 instance store vs EBS for MySQL: how to increase performance and decrease cost
If you are using large EBS GP2 volumes for MySQL (i.e. 10TB+) on AWS EC2, you can increase performance and save a significant amount of money by moving to local SSD (NVMe) instance storage. Interested? Then read on for a more detailed examination of how to achieve cost-benefits and increase performance from this implementation.
Why You Should Avoid Using “CREATE TABLE AS SELECT” Statement
In this blog post, I’ll provide an explanation why you should avoid using the CREATE TABLE AS SELECT statement. The SQL statement “create table <table_name> as select …” is used to create a normal or temporary table and materialize the result of the select. Some applications use this construct to create a copy of the table. This is one statement that will do all the work, so you do not need to create a table structure or use another statement to copy the structure.
Honorable Mention:
Is Serverless Just a New Word for Cloud-Based?
Top 10 Most Commented
These posts generated some healthy discussions (not surprisingly, this list overlaps with the first):
- A Look at MyRocks Performance
- Tuning InnoDB Primary Keys
- Tuning Autovacuum in PostgreSQL and Autovacuum Internals
- About ZFS Performance
- How to Speed Up Pattern Matching Queries
- Using AWS EC2 instance store vs EBS for MySQL: how to increase performance and decrease cost
- 40 million tables in MySQL 8.0 with ZFS
- Tune Linux Kernel Parameters For PostgreSQL Optimization
- Fsync Performance on Storage Devices
- One Billion Tables in MySQL 8.0 with ZFS
Posts Worth Revisiting
Don’t miss these great posts that have excellent information on important topics:
- Scaling PostgreSQL with PgBouncer: You May Need a Connection Pooler Sooner Than You Expect
- Percona Monitoring and Management: Look After Your pmm-data Container
- Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL
- Running Percona XtraDB Cluster in Kubernetes/OpenShift
- Installing MySQL 8.0 on Ubuntu 16.04 LTS in Five Minutes
- Setting up PMM on Google Compute Engine in 15 minutes or less
Have a great end of the year celebration, and we look forward to providing more great blog posts in 2019.