Speeding up mysql dumps and imports [closed]

Assuming that you’re using InnoDB…

I was in the situation of having a pile of existing mysqldump output files that I wanted to import in a reasonable time. The tables (one per file) were about 500MB and contained about 5,000,000 rows of data each. Using the following parameters I was able to reduce the insert time from 32 minutes to under 3 minutes.

innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT

You’ll also need to have a reasonably large innodb_buffer_pool_size setting.

Because my inserts were a one-off I reverted the settings afterwards. If you’re going to keep using them long-term, make sure you know what they’re doing.

I found the suggestion to use these settings on Cedric Nilly’s blog and the detailed explanation for each of the settings can be found in the MySQL documentation.

Leave a Comment