Sometimes a MySQL server running InnoDB takes a long time to shut down. If you have a MySQL server that reserves a large amount of memory for the Innodb buffer pool, you must have noticed that it can often take a very long time to shutdown. And the problem is that, during the shutdown process, the server will not be able to serve any requests. The usual culprit is flushing dirty pages from the buffer pool. These are pages that have been modified in memory, but not on disk.Generally, 50% of the buffer is dirty (unwritten to disk). If you kill the server before it finishes this process, it will just go through the recovery phase on startup, which can be even slower in stock InnoDB than the shutdown process, for a variety of reasons.
There is a way to speed up shutdown while still allowing the server to stay up and handle requests. The trick is to lower the maximum percentage of dirty pages allowed and this is controlled by the global variable innodb_max_dirty_pages_pct.
First, enter the command :
set global innodb_max_dirty_pages_pct=0;
You can then enter the following to verify the new value:
show global variables where variable_name like ‘dirty’;
To monitor the number of dirty pages that have not been written to disk, enter the following:
show global status where variable_name like ‘dirty’;
You will see this number begin to drop, although it is unlikely to ever reach 0 (If the server is being actively used, it won’t get to zero.), once it drops to a low level, you can then shutdown the server. You will find that the server will shutdown very quickly, allowing you to minimize downtime.
If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!