Days ago one of our customers with a very large InnoDB database was having a strange error while trying to restart MySQL server.
So our tech team started investigating and found it was caused due to the very big size of the InnoDB database and the dirty pages.
What are dirty pages after all?
First things first: having dirty pages is totally normal for InnoDB Databases. When your database rows are updated in the buffer memory pool, but not on disk, this is where the page is generated as ‘dirty’.
On large databases this can cause really slow shutdowns for InnoDB databases while restarting / shutting down the MySQL service.
Flush dirty pages to Speedup MySQL InnoDB shutdown
In order to speed up MySQL InnoDB shutdown we applied a pre-flush to the dirty pages just right before the server shutdown. This were the two commands they used.
First one is useful to find out how many dirty pages you have:
mysqladmin ext -i10 | grep dirty
This just a test output example:
And the last one, is to flush data from the buffer pool and set it to 0. The default value is always 75.
mysql> set global innodb_max_dirty_pages_pct = 0;
After applying this, let the MYSQL server run for a while and then try to restart again, you will notice a better speed in the whole restart process.
Further reading: