InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes

Question

I'm currently working on creating an environment to test performance of an app; I'm testing with MySQL and InnoDB to find out which can serve us best. Within this environment, we'll automatically prepare the database (load existing dumps) and instrument our test tools.

I'm preparing to test the same data dump with MySQL and InnoDB, but I'm already failing to bring the initial import to an usable speed for the InnoDB part. The initial dump took longer, but that didn't concerned me yet:

$ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i > $i.sql; done  real    0m38.152s user    0m8.381s sys     0m2.612s  real    1m16.665s user    0m6.600s sys     0m2.552s 

However, the import times were quite different:

$ for i in  testdb_myisam testdb_innodb; do time mysql $i < $i.sql; done  real    2m52.821s user    0m10.505s sys     0m1.252s  real    87m36.586s user    0m10.637s sys     0m1.208s 

After research I came over Changing tables from MyISAM to InnoDB make the system slow and then used set global innodb_flush_log_at_trx_commit=2:

$ time mysql testdb_innodb < testdb_innodb.sql  real    64m8.348s user    0m10.533s sys     0m1.152s 

IMHO still shockingly slow. I've also disabled log_bin for these tests and here's a list of all mysql variables.

Do I've to accept this long InnoDB times or can they be improved? I've full control over this MySQL server as it's purely for this test environment.

I can apply special configurations only for initial import and change them back for applications tests so they better match production environments.

Update:

Given the feedback, I've disabled autocommit and the various checks:

$ time ( echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" \ ; cat testdb_innodb.sql ; echo "COMMIT;" ) | mysql testdb_innodb;date  real    47m59.019s user    0m10.665s sys     0m2.896s 

The speed improved, but not that much. Is my test flawed?

Update 2:

I was able to gain access to a different machine were imports only took about 8 minutes. I compared the configurations and applied the following settings to my MySQL installation:

innodb_additional_mem_pool_size = 20971520 innodb_buffer_pool_size = 536870912 innodb_file_per_table innodb_log_buffer_size = 8388608 join_buffer_size = 67104768 max_allowed_packet = 5241856 max_binlog_size = 1073741824 max_heap_table_size = 41943040 query_cache_limit = 10485760 query_cache_size = 157286400 read_buffer_size = 20967424 sort_buffer_size = 67108856 table_cache = 256 thread_cache_size = 128 thread_stack = 327680 tmp_table_size = 41943040 

With these settings I'm now down to about 25 minutes. Still far away from the few minutes MyISAM takes, but it's getting more usable for me.

Answer 1

Did you try the Bulk Data Loading Tips from the InnoDB Performance Tuning Tips (especially the first one):

• When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

  SET autocommit=0; ... SQL import statements ... COMMIT; 

  If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.

• If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

  SET unique_checks=0; ... SQL import statements ... SET unique_checks=1; 

  For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

• If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

  SET foreign_key_checks=0; ... SQL import statements ... SET foreign_key_checks=1; 

  For big tables, this can save a lot of disk I/O.

IMO, the whole chapter is worth the read.

Answer 2

Have you tried starting a transaction at the outset and committing it at the end? From the question you linked: "Modify the Insert Data step to start a transaction at the start and to commit it at the end. You will get an improvement, I guarantee it."

Remember that InnoDB is transactional, MyISAM is not. Transactional engines treat every statement as an individual transaction if you don't explicitly control the transaction. This can be costly.