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

Question

I am currently working on creating an environment to test application performance; I am testing MySQL and InnoDB to find out which ones can best serve us. In this environment, we will automatically prepare the database (load the existing dumps) and apply our test tools.

I am ready to test the same data dump with MySQL and InnoDB, but I can no longer bring the initial import into the usable part of InnoDB. The initial landfill took longer, but this did not bother 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 time was completely 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. Changing tables from MyISAM to InnoDB makes the system slow , and then 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 is still terribly slow. I also disabled log_bin for these tests and here is a list of all mysql variables .

Should I take these long InnoDB times or can they be improved? I have full control over this MySQL server, as it is exclusively for this test environment.

I can only apply custom configurations for initial import and modify them for application tests to better suit production environments.

Update:

Given the feedback, I turned off auto-messaging and 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 

Speed ​​has improved, but not so. Is my test broken?

Update 2:

I managed to access another machine, the import was only 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 reached about 25 minutes. It’s still far from the few minutes MyISAM takes, but it becomes more convenient for me.

Answer 1

Have you tried Intensive data loading tips from InnoDB performance tuning tips (especially the first one):

• When importing data into InnoDB make sure that MySQL does not have autosave enabled because it requires writing a log to disk for each insert. To turn off auto-exchange during your import operation, surround it with SET autocommit and COMMIT statements:

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

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

• If you have UNIQUE restrictions for secondary keys, you can speed up the import table by temporarily disabling uniqueness checking during the import session:

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

  For large tables, this saves a lot of disk I / O because InnoDB can use its insert buffer to write secondary index entries in a batch. Be sure that the data does not contain duplicate keys.

• If you have FOREIGN KEY in your tables, you can speed up the import of tables by foreign key checking the duration of the import session:

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

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

IMO, the whole chapter is worth reading.

Answer 2

Have you tried to start a transaction from the very beginning and complete it at the end? From a question related to us: “Change the“ Insert data ”step to start the transaction at the beginning and commit it at the end. You will get an improvement, I guarantee."

Remember that InnoDB is transactional, MyISAM is not. Transactional mechanisms treat each statement as a separate transaction, unless you explicitly control the transaction. It can be expensive.

Answer 3

I am having problems with bulk import and recommend the accepted answer. I found that you can also significantly speed up the process:

• Delete all indexes (except the primary key), load data, and then re-add indexes
• Checking your innodb_log_file_size * innodb_log_files_in_group enough to avoid writing to disk with less than a second frequency

In relation to No. 2, the default values ​​for 5M * 2 will not be sufficient for a modern system. See innodb_log_file_size and innodb_log_files_in_group

Answer 4

I found that the hard drive is a bottleneck - old-fashioned drives are hopeless, SSDs are fine, but still far from perfect. Importing to tmpfs and copying data is faster, details: https://dba.stackexchange.com/a/89367/56667