In that test I would like to measure the impact of a failover of an Azure Database for MySQL. Unfortunately there is no mechanism to force the crash of the Azure Database for MySQL instance. So how can I test how long it takes to failover and what is the impact on a server activity. Fortunately there are other circumstances where a switchover will be triggered by Azure.
For example if you decide to scale up your instance it will require (going from 2 vCores to 4 vcores in this case) a restart of the Azure MySQL container. This restart will also happen without any action on your side when Azure decides to upgrade your MySQL version.
How does this switchover behave under an heavy workload. To generate an heavy workload we will use sysbench with the report interval mechanism that allows to continuously track transactions per second(tps).
We will add 2 extra options when running sysbench:
--mysql-ignore-errors=all : this will ignore connection errors and trigger a reconnection. The normal behavior of sysbench is to not use the failed connection anymore
--db-ps-mode=disable : this is to disable prepared statement as it is incompatible with reconnection
To see if we get benefit from a scaleup We will use a workload that is above what can be handled by the initial server(2 vCores). The scale up aim is to have a new server that can sustain the initially required level of activity. A real life situation would be to scale the MySQL database that is behind your ecommerce/Magento website when seasonal high traffic happens.
What we see here is a positive result :
- The scale up took place in a reasonable amount of time (around 10s)
- The new scaled up server having more vCores(4 vCores) was able to offer a higher number of transactions per second(almost twice).
Scaling up only when the monitored activity requires it can greatly reduce the cost of ownership of an Azure based platform. This can be accomplished in a very simple way.
This scaling operation can also be automated with Azure Runbooks an Triggers :
https://techcommunity.microsoft.com/t5/azure-database-support-blog/how-to-auto-scale-an-azure-database-for-mysql-postgresql/ba-p/369177