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 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 roll change in classic async replication without a restart (CHANGE MASTER TO …), and Galera doesn’t have read/write roles as such (all instances are technically writers).
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 refused 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.
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.
On the old master (new replica/slave), the buffer pool is left cold (empty). Don’t know why.
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.
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.
Conclusions
I don’t understand why the old master gets a cold InnoDB buffer pool.
I wouldn’t think a complete server restart is necessary, but since we don’t have insight in the internals, who knows.
Losing connections across the cluster is a real nuisance that really impacts applications. Here’s why. When a C client (on which most MySQL APIs are based, or modelled) 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). However, this does mean 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. Consequentially, an Aurora failover will make the frontend of an application look like a disaster zone for about 30 seconds. I appreciate that this is not Aurora’s fault, it’s sloppy application development that causes this, but it’s a real-world fact we have to deal with, and our other cluster and replication options do not trigger this scenario.