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 …

In the InnoDB plugin, a new variable was added named innodb_io_capacity, which controls the maximum number of I/O operations per second that InnoDB will perform (which includes the flushing rate of dirty pages as well as the insert buffer (ibuf) batch size).

First off, let me just say this is a welcome addition (an addition provided by the Google Team, fwiw).

However, before this was configurable, the internal hard-coded value for this was 100. But when this became configurable, the default was increased to 200.

For many systems, this is not an issue (i.e., the overall system can perform 200 IOPS).

However, there are still many disks (which is often the bottleneck) out there that are …