Every now and then you’ll have no choice but to restart the mysql server, whether it be for editing server variables, upgrading, etc.. When the server is restarted, among the operations performed, it commits all active transactions, it flushes all dirty pages (data in memory) to disk and on restart, the buffers are wiped and re-caches data as needed. On a busy server, this set of events is not trivial, as you’ll find it can take quite a bit of time for the pages to flush, and an increased load on the server to re-populate the cache with it’s working data set. There are several things you can do to help with this process.
Be sure there are no active transactions
the server can’t shutdown if there is a running transaction. Do a ‘show processlist’ to be sure.
Flush pages before server shutdown
Have you ever shutdown the server, watching the dots add up on the command line, wondering what it’s doing and when it’s going to actually shutdown? Most of the time is taken by the flushing of dirty pages. Innodb caches a certain amount of dirty pages, loosely specified by,
the default as of 5.5.24 is 75 (%). The reason it keeps a certain percentage of dirty pages is to group together data and rearrange it in sequential order so it can be written in one in go on disk, resulting in much less I/O. You can change this setting dynamically. To cause innodb to flush as frequently as possible, you can issue the following on the command line,
set option global innodb_max_dirty_pages_pct=0
keep in mind this will cause additional load on the server, as now you’ve directed innodb to essentially randomly write the data as it’s requested in real time. You can monitor the current amount of dirty pages with the following,
mysqladmin extended -r -i 1 | grep Innodb_buffer_pool_pages_dirty
you’ll see the amount of dirty pages drop until it levels out (you’ll never hit 0 on a busy server). You are now at the minimum amount of dirty pages and you can issue your restart command.
repopulating the buffer pool
If you are worried about the extra load on the server when restarting due to all pages needing to be read from disk instead of memory, you can quickly repopulate the cache if you have percona’s flavour of mysql. No reason to repeat information here, go to,
