As a heavy user of RDS Aurora cluster, I had some chances to test it and find a few problems.
I'm going to summarize and show in which case the Aurora can have a very bad behaviour in case it is in a chain of replica servers.
I was keeping my data on a mysql server on EC2 instance, and it was a single point of failure, and I made my mind up to migrate my data to an resilient and efficient platform as Aurora cluster is.
In my opinion, Aurora has a limitation usage of its Read Replica, either the RR can't have a ELB in front of them and the Reader endpoint distributes the load across all read replica instances without giving you the availability to remove a RR from this internal load balancer.
The basic is that you can not isolate a read replica to no OLTP transactions without the risk to slow down the requests from your application that should query the database and get a reply in a short time.
The nature of the Aurora's storage doesn't seem to support long and heavy queries that can end up worst performance even for the writer and readers.
Application like back office, reports and user-queries need to access to the data and their queries are not always tuned and optimal.
A workaround to avoid bad performance, was to include in my DB topology a second Aurora instance that replicates from my Aurora primary and redirect the "bad guys" to the second Aurora cluster.
That seemed to me a nice and clear idea provide an extra instance for all not live queries, that would save me to tune all user's queries or retrieve access from not mysql-friendly users.
My first worry was to cut off the Mysql instance and promote the Aurora-M ( Aurora master ) with reduced downtime.
I figured out my DB infrastracture:
Mysql Server 5.5 => Aurora-M 5.6 => Aurora-S 5.6
Alias:
- Mysql-M = Mysql Server 5.5
- Aurora-M 5.6 = Aurora-M
- Aurora-S 5.6 = Aurora-S
Step#1 Importing Data to Aurora-M
The first step was to setup the Aurora cluster as slave of the main Mysql server, to do so I followed the best practice of Amazon and I set up the replication stream between Mysql-M and Aurora-M and I was worried about the high writes happen on the Mysql-M. The first test was to get a feedback about if the Aurora-M could keep up with the replication stream coming from its master.The Aurora still uses a single replica thread, it does serial writes.
I had to give more power to my writer changing the instance type from r3.xlarge to r3.2xlarge but after then the writer kept up the workload. I left this configuration running for a week in order to let it go through the busiest period. It copied very well.
The CPU wasn't too high, I tend to keep the threshold CPU under 60%, and it was around 5%.
I could understand better the meaning of "Queries" chart of the Aurora DB in Cloudwatch. Without reads or writes directly on the Aurora, I figured out that the queries graph showed the requests made by the replication thread on itself even that doesn't same right.
The replication was based on the STATEMENT binlogs and due to some compatibly issues happened, I wanted to keep STATEMENT type.
At the end of this stage I was happy about the good Aurora performance.
Step#2 Importing Data to Aurora-S
The idea of using Aurora-S for the heavy and long queries, it is very good and I believed that I could use even an external Mysql server on EC2, just for quick set up I turned on to Aurora cluster.
Following again the best Aurora practice, I dumped the database on the Aurora-S and started the replication from its master which instead it was Aurora-M.
A useful feature of RDS Aurora is that you can snapshot your database and then use the snapshot to quickly create another Aurora cluster, that makes things quickly manageable and reliable.
By the time I created the snapshot and created the new Aurora-S cluster, the two Auroras increased their gap, the new Aurora just attached to the Aurora-M needed to catch up the replica stream missing. If the application does a lot writes, I recommend to increase the number of binlogs, for my case I needed to multiple the default value by 10.
You can manage the retention internally in Aurora cluster with the command
CALL mysql.rds_set_configuration('binlog retention hours', 144);
Step#3 Monitoring performance Aurora Clusters
The Aurora-M was a couple hours ahead of the Aurora-S, so the Aurora-S started to read the oldest binlogs and slowly catching up with its master.
During this phase, the two clusters didn't have heavy CPU utilization or increase in latency or the Queries drop, they were fine .
Closer the Aurora-S was with the Aurora-M and bigger replication delay of the Aurora-M with its Mysql master!
When the Aurora-S reached out Aurora-M and it was fully behind, the Aurora-M was slowed down by this situation, and this bad performance were showed by the replication lag increasing, double the CPU utilization, from 5 to 12% and the Queries chart showed a upper limit of queries per second,roughly 750 per second.
The culprit was the Aurora-S replication process that once it was stopped the Aurora-M felt better and it could recover the lag with lower CPU and higher Queries.
Step#4 Tuning and workaround tried.
I started to tune some Aurora-M and Aurora-S variables and that it was what I have done:
Some variables have been tuned without making any differences:
- The "max_allowed_packet" variable has to be the larger than the biggest packet that can be transferred from the master to the slave, otherwise it raises a error network code, I didn't have any issues so that value didn't need to be tuned.
- Sync_binlog changed on the Aurora-M but not enhanced was gained.
- Innodb_flush_log_at_trx_commit changed to 1 no effect.
- I thought Aurora couldn't keep up the high load writes since the replication is a single thread but that was disproved because I left the Aurora-M running without any slaves attached and it was behind its master without lags.
- Due to the nature of some statements, I could not change the binlog type from STATEMENT to ROW or MIXED.
I tried this time to replace the Aurora-S with a Mysql 5.6 server and set up the delay feature offered by the replication setup.
I hoped that could avoid the hub to read the last Aurora binlog, but the replication delay feature of mysql 5.6 acts on the SQL_thread, so the IO_thread still reads the last Aurora binlog and that doesn't mitigate my issue.
Step#5 Conclusion
Aws Aurora specialist came back to me and it sees it is last investigation:
The reason you see a spike in CPU utilization and Aurora Replica Lag is due to mutex lock issue. When Aurora instance is trying to read from its master (MySQL 5.5 on EC2) and writing into its own binlogs (for its slave Aurora),these two operations must acquire the same binlog mutex.
When introducing a new read replica ( Aurora-S ), this increased the mutex contention, causing the CPU utilization and replica lag increase. So this is an expected behavior.
Due to the mutex lock due to who is reading and writing the same logical file, the Aurora-M suffers and the CPU and Replica Lag increase.
I ended up that is the nature how Aurora is created and I can't mitigate that issue.
From the chart it is easily understandable when the Aurora-S started to read the same binlog that was being writeen by the Aurora-M.
Highlighting timing:
17:00 Aurora-S replication swithed on
18:00 Aurora-S caught up its master
18:00 Aurora-M higher CPU, ReplicaLag increased, Queries upper limited.
