Speeding up InnoDB table imports
We switched to InnoDB tables a while ago. They offer transactions, foreign key constraints and a few other goodies that are missing from MyISAM. We knew writes would be slower due to foreign key checks, etc, but we didn’t imagine that importing a table with about 160,000 records would take almost an hour to import, while it only took about 15 seconds to import using MyISAM. I did some digging and figured out the solution. Add these lines to your dumps.
SET AUTOCOMMIT=0;
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
... Your dump here ...
COMMIT;
SET AUTOCOMMIT=1;
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;
This sped up InnoDB imports to pretty much the same speed as MyISAM imports for the table in question.
A warning about REPLACE INTO and InnoDB
From time to time I get lazy and use REPLACE INTO instead of checking for records and then only inserting the ones that don’t exist. It works really well for importing records where some records might be already in the database. Today, I ran into a little snag due to my laziness.
MySQL uses the following algorithm for
REPLACE (and LOAD DATA ...
REPLACE):
- Try to insert the new row into the table
- While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
- Delete from the table the conflicting row that has the duplicate key value
- Try again to insert the new row into the table
When you combine this with foreign keys that are defined with ON DELETE CASDCADE you end up nuking child records when you REPLACE INTO a parent record. It only took me an hour and a half to debug this insanity.
