On backing up and restoring large mysql databases

In order to improve the performance of mysql when dealing with large databases, there's a couple of settings that can be tweaked in your my.cnf file (or through a MySQL administration tool). The following line has proven to be the most useful.

innodb_buffer_pool_size = 1G

I've been meaning to write about this improvement for the last few weeks, but I wanted to test it myself first. So far I'm very pleased with the performance improvement (database restore time is down from about 3 hours to about 45 minutes on a ~1.2G database). I haven't tested how this setting affects MySQL during normal execution, but it certainly helps improve performance during backups and restores. So if you find yourself backing up and restoring large MySQL databases, you should test this out. You should (obviously) not set innodb_buffer_pool_size to a value greater than the available memory on your machine.

Thanks to Marc Harrison for the tip.

If you want to read some more about this configuration, I found a couple of blog entries from a few years ago Enjoy!

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/