Right now Aurora only allows a single master, with up to 15 read-only replicas.
Master/Replica Failover
We love testing failure scenarios, however our options for such tests with Aurora are limited (we might get back to that later). Anyhow, we told the system, through the RDS Aurora dashboard, to do a failover. These were our observations:
Role Change Method
Both master and replica instances are actually restarted (the MySQL uptime resets to 0).
This is quite unusual these days, we can do a fully controlled role change in classic asynchronous replication without a restart (CHANGE MASTER TO …), and Galera doesn’t have read/write roles as such (all instances are technically writers) so it doesn’t need role changes at all.
Failover Timing
Failover between running instances takes about 30 seconds. This is in line with information provided in the Aurora FAQ.
Failover where a new instance needs to be spun up takes 15 minutes according to the FAQ (similar to creating a new instance from the dash).
Instance Availability
During a failover operation, we observed that all connections to the (old) master, and the replica that is going to be promoted, are first dropped, then refused (the connection refusals will be during the period that the mysqld process is restarting).
According to the FAQ, reads to all replicas are interrupted during failover. Don’t know why.
Aurora can deliver a DNS CNAME for your writer instance. In a controlled environment like Amazon, with guaranteed short TTL, this should work ok and be updated within the 30 seconds that the shortest possible failover scenario takes. We didn’t test with the CNAME directly as we explicitly wanted to observe the “raw” failover time of the instances themselves, and the behaviour surrounding that process.
Caching State
On the promoted replica, the buffer pool is saved and loaded (warmed up) on the restart; good! Note that this is not special, it’s desired and expected to happen: MySQL and MariaDB have had InnoDB buffer pool save/restore for years. Credit: Jeremy Cole initially came up with the buffer pool save/restore idea.
On the old master (new replica/slave), the buffer pool is left cold (empty). Don’t know why. This was a controlled failover from a functional master.
Because of the server restart, other caches are of course cleared also. I’m not too fussed about the query cache (although, deprecated as it is, it’s currently still commonly used), but losing connections is a nuisance. More detail on that later in this article.
Statistics
Because of the instance restarts, the running statistics (SHOW GLOBAL STATUS) are all reset to 0. This is annoying, but should not affect proper external stats gathering, other than for uptime.
On any replica, SHOW ENGINE INNODB STATUS comes up empty. Always. This seems like obscurity to me, I don’t see a technical reason to not show it. I suppose that with a replica being purely read-only, most running info is already available through SHOW GLOBAL STATUS LIKE ‘innodb%’, and you won’t get deadlocks on a read-only slave.
Multi-Master
Aurora MySQL multi-master was announced at Amazon re:Invent 2017, and appears to currently be in restricted beta test. No date has been announced for general availability.
We’ll have to review it when it’s available, and see how it works in practice.
Conclusion
Requiring 30 seconds or more for a failover is unfortunate, this is much slower than other MySQL replication (writes can failover within a few seconds, and reads are not interrupted) and Galera cluster environments (which essentially delivers continuity across instance failures – clients talking to the failed instance will need to reconnect to the loadbalancer/cluster to continue).
I don’t understand why the old master gets a cold InnoDB buffer pool.
I wouldn’t think a complete server restart should be necessary, but since we don’t have insight in the internals, who knows.
On Killing Connections (through the restart)
Losing connections across an Aurora cluster is a real nuisance that really impacts applications. Here’s why:
When MySQL C client library (which most MySQL APIs either use or are modelled on) is disconnected, it passes back a specific error to the application. When the application makes its next query call, the C client will automatically reconnect first (so the client does not have to explicitly reconnect). So a client only needs to catch the error and re-issue its last command, and all will generally be fine. Of course, if it relies on different SESSION settings, or was in the middle of a multi-statement transaction, it will need to do a bit more.
So, this means that the application has to handle disconnects gracefully without chucking hissy-fits at users, and I know for a fact that that’s not how many (most?) applications are written. Consequently, an Aurora failover will make the frontend of most applications look like a disaster zone for about 30 seconds (provided functional instances are available for the failover, which is the preferred and best case scenario).
I appreciate that this is not directly Aurora’s fault, it’s sloppy application development that causes this, but it’s a real-world fact we have to deal with. And, perhaps importantly: other cluster and replication options do not trigger this scenario.