How to decrease InnoDB shutdown times

How to decrease InnoDB shutdown times
Posted by Baron Schwartz
Sometimes a MySQL server running InnoDB takes a long time to shut down. The usual culprit is flushing dirty pages from the buffer pool. These are pages that have been modified in memory, but not on disk.

If you kill the server before it finishes this process, it will just go through the recovery phase on startup, which can be even slower in stock InnoDB than the shutdown process, for a variety of reasons.


One way to decrease the shutdown time is to pre-flush the dirty pages, like this:

PLAIN TEXTCODE:
mysql> set global innodb_max_dirty_pages_pct = 0;

Now run the following command:

PLAIN TEXTCODE:
$ mysqladmin ext -i10 | grep dirty
| Innodb_buffer_pool_pages_dirty | 1823484 |
| Innodb_buffer_pool_pages_dirty | 1821293 |
| Innodb_buffer_pool_pages_dirty | 1818938 |

And wait until it approaches zero. (If the server is being actively used, it won't get to zero.)

Once it's pretty low, you can perform the shutdown and there'll be a lot less unfinished work to do, so the server should shut down more quickly.

Posted by Baron Schwartz @ 8:21 am :: Innodb, storage engine, tips
Print This Post del.icio.us :: digg
Comment RSS
Related posts: :Confusing MySQL Replication Error Message::Beware large Query_Cache sizes::Innodb Fuzzy checkpointing woes:

11 Comments »
1. ihanick
Thanks, amazing advice!

Comment :: April 15, 2009 @ 9:30 am

2. Mark Callaghan
Baron - you can update this article to include a link to a Percona binary that makes this faster.

It is one thing to ask InnoDB to lower the percentage of dirty buffers, it is another thing for InnoDB to do that on a busy server.

If writes are slow on your server, then you really need the Percona patches or features from the v3 Google patch to make the writing use the available IO capacity on a server. The patches to use include innodb_io_capacity, more background IO threads and a few others.

Writes to files in the OS buffer cache are fast. Writes may be slow when:
* using innodb_flush_method=O_DIRECT
* disabling SATA writeback cache
* not using a HW RAID disk cache
* using NFS or some other remote storage service that doesn’t buffer writes in the OS buffer cache

Comment :: April 15, 2009 @ 10:04 am

3. Baron Schwartz

Good point — there is some documentation on those things here: http://www.percona.com/docs/wiki/patches:start Unfortunately that documentation is poor quality, but it gives an idea.

Comment :: April 15, 2009 @ 10:39 am

4. Kevin Burton

We made this a standard part of /etc/init.d/mysql stop.

It just sets this and waits for MySQL to finish writing and then allows the stop to happen.

The main problem is that the mysql control port is closed to there’s now way to figure out what MySQL is doing while you’re waiting…

Comment :: April 15, 2009 @ 10:45 pm

5. Simon Mudd

You mention Innodb. There’s also a very nasty option which can affect MyISAM users, especially if you use MyISAM for datawarehouse issues. (smaller memory and disk footprint than innnobase)

This is something taken from our wiki, and discovered after discussing in detail with the MySQL support team.

delay_key_write=ON http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_delay-key-write

This global configuration variable can be used (delay_key_writes=ALL) to stop writes from the MySQL key buffer to disk. It’s only been used on slave servers, not the masters.

This option sounds great but DO NOT USE IT. It has a very nasty effect of leaving the key data for MyISAM tables in memory and not written to disk. When you want to shutdown the server the server is forced to do a FLUSH TABLES and this can take a long time. On the XXX servers we’ve seen this take between 20 and 50 minutes.

NOTE: These are boxes running the MyISAM engine exclusively and having 32GB of RAM. (key_buffer 12GB)

So something to remember if you have a server setup this way.

Comment :: April 16, 2009 @ 10:46 am

6. pat
Is this really shutting down my server any faster in wall clock time?

Without setting the max_dirty_pages flag to zero, say it takes me 15 minutes to shut down.

If I do set it, doesn’t it just take me 14 minutes for dirty pages to drop to zero and 1 minute for the shutdown?

Seems like it might make the shutdown *command* take less time, but the actual elapsed time from the moment you decide to shut down the server to the time mysqld terminates isn’t decreasing is it?

Comment :: April 16, 2009 @ 4:47 pm

7. Kevin Burton

pat,

If you’re new to MySQL and your DB is taking 12 minutes to shutdown, and the port is closed, one might assume that the DB is broken and kill -9 it..

Our script actually prints that it’s flushing the buffer pool and prints percentage until complete numbers.

Comment :: April 16, 2009 @ 5:14 pm

8. Baron Schwartz

Pat,

If your server takes 15 minutes to shut down, you can keep the application online and responding for some portion of that time, while dirty pages are flushed from the buffer pool. The alternative is to take the app offline for the whole 15 minutes. The point is to reduce the amount of time that the server is *actually* shutting down and restarting, by *preparing* it for shutdown.

Kevin, can you post your shutdown script? Maybe put it on your blog and link to it here? Maybe contribute it to your favorite distro? It sounds useful.

Comment :: April 16, 2009 @ 7:39 pm

9. Coolyou
Kevin, we were about to write one of these scripts ourselves until we realized that we may have an issue with figuring out how far we are with the flushing. Could you please provide us all with the script? It may prove very useful

Pat, the whole idea of this is to keep the application online for a longer period of time than having MySQL refuse all connections for that 15 minutes.

However I have noticed that MySQL performance decreases dramatically when innodb_max_dirty_pages_pct = 0 is in effect. That’s not unexpected considering that the dirty pages are immediately flushed to disk. But those with a busy site may want to consider this limitation because this option may even increase the time required to shut down because of the increased load from the application itself and the increased time it takes for the application to respond.

Comment :: April 16, 2009 @ 8:29 pm

10. Kevin Burton

Hey Baron….

Here’s the script we’re using:

http://pastebin.com/f5ec2f70d

If we were to write it again it would almost certainly be written in python.

Kevin

Comment :: April 17, 2009 @ 9:49 am

11. Kevin Burton

Another note… we have three replicas of everything.

When we restart an InnoDB box it is taken out of production and does not serve requests.

Then we flush the innodb buffer pools, then restart it.

The script I linked to above is just added as a one liner to our /etc/init.d/mysql script…. if InnoDB isn’t running on this box it isn’t run.

We have about 1/2 of our cluster on MyISAM without InnoDB enabled so this would break otherwise.

Comment :: April 17, 2009 @ 9:51 am