Calling all MySQL admins! When you’re working with InnoDB tables you may have noticed that MySQL takes an extraordinary amount of time to shutdown. Don’t panic! It’s normal.
InnoDB has extensive logs that it must run through and if your server is at all busy these logs can be quite big.
After you’ve started the equivalent of “/etc/init.d/mysql stop” it may not return for quite a while. Simply open another terminal and examine the system log. On a Debian or Ubuntu box, look at /var/log/daemon.log and you’ll see the “InnoDB: Starting shutdown…” message.
Now, go off and make a cup of tea because this could take a while. On a fairly large and busy database it might be half an hour or more!
Please, please, please don’t be tempted to killall -9 mysqld because bad things will happen! Even if your boss is looking over your shoulder, and you’ve promised that the company webserver will be online in 2 minutes, don’t do it.
However, cases where it is ok to kill the database with a “kill -9”:
- International terrorists are planning on robbing the World Bank and you’re the only secret agent who can bring down the database which will stop them.
- Lassie will be run over by the evil farmer who plagued the local village with his monster cows unless you can corrupt his database in time.
- Or finally, you like hard work and restoring from backups!
thanks, i did a kill -9 and ended up with a wonderful situation as illustrated in point 3. 2nd time around, I decided to let it shut down cleanly and referred everyone waiting on me to this post.
Just a note on optimizing InnoDB tablespace:
1. read up on using innodb_file_per_table option
2. in order to optimize disk usage of innodb tables with this option set, you can run ‘alter table `your_table` engine=innodb’ to perform the equivalent of an innodb defrag đŸ˜‰