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.