MySQL – Innodb Slow Shutdown – Dirty Buffer Pages
If you have a MySQL server that reserves a large amount of memory for the Innodb buffer pool, you may have seen that it can often take a very long time to shutdown. During the shutdown process, the server will not be able to serve any requests. From my experience, I have seen that it can take 1-5 minutes per GB of buffer to perform a shutdown. So, for example, if you have a server with 50G of memory allocated to the Innodb Buffer Pool, and 50% of the buffer is dirty (unwritten to disk), it can take anywhere from 25-125 minutes to shutdown. Multiple hour shutdowns on large, busy Innodb MySQL servers is not unheard of.
Luckily, there is a fairly easy way to speed up shutdown while still allowing the server to stay up and handle requests. The secret is to lower the maximum percentage of dirty pages allowed. This is controlled by the global variable innodb_max_dirty_pages_pct. On my servers I usually have this set at 75%. This variable can be changed at run-time, which allows us to modify this variable to speed up system shutdown.
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 rlike ‘dirty’;
To monitor the number of dirty pages that have not been written to disk, enter the following:
show global status where variable_name rlike ‘dirty’;
You will see this number begin to drop, although it is unlikely to ever reach 0, once it drops to a low level and reaches a plateau, you can then shutdown the server. You will find that the server will shutdown very quickly, allowing you to minimize downtime.