Restating MySQL can be really annoying. You just want to disable the goddamn query cache and it takes forever (read 5-10 minutes) to shutdown, not to mention the warm-up time. Yes, with MySQL 5.7 you can do many changes online, so you won’t necessarily be restarting that often, but you still need to do upgrades, occasionally increase redo log size and, admit it, enable skip-grant-tables. Here’s how you can make this process way less painful.

Why is MySQL so slow to restart?

Before we go any further, let me tell you right away that when I’m speaking about MySQL here, I’m actually speaking about InnoDB, or rather, a MySQL server that’s running InnoDB as the main storage engine. And if that’s not your case, do not read any further. You’ve been warned!

Now.. ah yes. Restart. So, restarting MySQL involves two slow stages. I have already mentioned them, but repetition is the mother of skill, so let me say …

MySQL Cluster 6.3.28b and 7.0.9b contain optmizations which can greatly reduce the time taken for data nodes to restart – this includes restarting a single node, performing a rolling restart or a full system restart.

The benefits you see will depend on many factors including including the size of your database and the frequency, size and complexity of your transactions. As an experiment, I re-ran some data node restart timings from an earlier post (http://www.clusterdb.com/mysql-cluster/mysql-cluster-data-node-restart-times/)

The headline figure from my results is that for a 6 Gbyte database, with modest traffic I saw a 2.2x improvement. This is using very simple transactions and so you may get a much better improvement – 70x is being seen by some!!! The best news is that the slower your …

Restarts are required for certain, infrequent maintenance activities. Note that there is no loss of service while a single node restarts.

When a data node restarts, it first attempts to load the data into memory from the local log files and then it will catch up with any subsequent changes by retrieveing them from the surviving node(s) in its node group.

 Based on this, you would expect the time taken to restart a data node to be influenced by:

1. The amount of data that was stored on the data node before the restart
2. Rate of updates being made to the data during the restart
3. Network performance (assuming the data is being updated during recovery)

The times will also be influenced bycertain configuration parameters, performance of the host machine and whether the multi-threaded data node (ndbmtd) is being used.

To provide some insight into how these …