Jun 3 2013

Faster InnoDB Imports

In dealing with Magento over the past few years, I regularly wait on multi-gigabyte MySQL dump files to import into my development database. While MyISAM tables are much quicker to copy and reload, there's doesn't seem to be much that can speed up InnoDB imports.

After browsing Stack Overflow far too long for answers and culling together piecemeal tips, I discovered that MySQL published the same information in the documentation. What a novel idea!

Before starting your import, run the first statement below via the MySQL command line tool to disable autocommit, unique and foreign key checks. Run your import and then the last line to enable them again:

mysql> set autocommit=0; set unique_checks=0; set foreign_key_checks=0;
mysql> source /path/to/dump.sql
mysql> commit; set unique_checks=1; set foreign_key_checks=1;

I've seen InnoDB import times improve dramatically with just those three statements. That said, ensure your database contains no duplicate keys before disabling uniqueness checks.

There is an additional setting you can tweak, innodb_autoinc_lock_mode, but it's not dynamic. It might be worth it to edit my.cnf, restart MySQL, process the import and then revert the settings for massive, one-off imports. However, that is one too many hoops to jump through for my own day to day usage.

Lastly, speaking as someone who previously relied heavily on our mutual friend, phpMyAdmin, I'd recommend to get into the habit of importing from the MySQL command line:

$ mysql -u user -p
mysql> use database_name
mysql> source /path/to/dump.sql

Side note: For the more adventurous developers out there, Percona opens up the possiblity of copying actual .idb files to Percona Server and MySQL 5.6 with XtraBackup. However, that's beyond what I choose to delve into at this time and I've seen some issues reported with Magento and MySQL 5.6.

6 comments

This is a very good post. Thank you for sharing. I managed to get very good import throughput with your tip.

All the best

Kubilay

Kubilay — Jan 15 2014

@Kubilay Glad to hear it! It cuts down my import times for something like a medium-sized Magento database to a third of the original.

Robbie — Jan 16 2014

Very good hint! I saved much time!

Martynas — Apr 8 2014

@Martynas Awesome!

Robbie Player — Apr 15 2014

Is it possible to get a progress output? Previously I was using `pv` but now that seems impossible.

Samuel Williams — Jan 25 2016

@Samuel Using `source` in the MySQL shell doesn't allow for a progress bar, but it does print the query results. I unfortunately don't have a lot of experience with `pv`.

Robbie Player — Feb 18 2016