Monday, September 26, 2016

Mysql shutdown, dirty pages, and innodb_page_cleaners in 5.7

In a busy Mysql environment, often the shutdown is delayed for a long time by the innodb page cleaner.  On some very busy systems this delay can be hours and you will get tired of watching this message in your error log:
                   [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool

Prior to 5.7 all you could do was watch the dirty pages and see if you could force the number down by setting the sever to flush pages thusly:
                   set global innodb_max_dirty_pages_pct = 0;

You can then watch the dirty page count drop until it reaches a low point (likely never near zero in a busy system) where you can shut it down by monitoring the dirty pages thusly:
                     mysqladmin ext -i10 |grep pages_dirty

But in 5.7 you can now have multiple page cleaners (the above trick still works) to help get the server in a state for a quick shutdown by setting
                     innodb_page_cleaners=n

Like all good things there is no free lunch, so if you kill the server rather than waiting for the page cleaner to do it's job, you will have to pay on the startup.

Enjoy

No comments:

Post a Comment