Speedup MySQL InnoDB Shutdown by Flushing Dirty Pages

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:

Flushing Dirty Pages to Speedup Innodb Shutdown
Screenshot of InnoDB Dirty Pages output from mysqladmin

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:

 

About the Author: Martin Keler

Leave a Reply

Your email address will not be published. Required fields are marked *