Recently I had to import a 7 Gb MySQL dump file to a MySQL 5.6 server. The import took around 7 hours on a mono-core CPU with 1 Gb of RAM.

Someone else tested the import on a MySQL server which has, amongst others, the following settings:

innodb_buffer_pool_size = 8G
query_cache_size = 300M

I'm a bit skeptical about the relevancy of these settings (and yes, I even think that setting such a large query cache is bad). Would that make a difference? Aren't these settings used only when querying the database, and hence irrelevant for an import?

If yes, which settings should be set to speed up the import of a large dump file?

According to the official documentation these values should be set temporarily:

unique_checks = 0
foreign_key_checks = 0

I've read here that it should be set also

innodb_flush_log_at_trx_commit = 2

but I don't think it would help, because autocommit mode (flushing logs to disk for every insert) is already disabled by default in the mysqldump command (--opt option).

1 Answer 1



No need to run unique_checks = 0 and foreign_key_checks = 0. See my 3-year-old post Speeding up mysqldump / reload (ASPECT #2 shows a standard header of a mysqldump. Lines 13 and 14 handle the disabling of those checks for you)


Please note the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

InnoDB Plumbing

If you want to reload a MySQL Instance you should temporarily disable the Double Write Buffer.


Login to the Target Server and run

SET GLOBAL innodb_fast_shutdown = 0;


Restart mysqld by setting to innodb_doublewrite to OFF

service mysql restart --innodb-doublewrite=OFF --innodb-fast-shutdown=0


Load the mysqldump into the Target Server


Restart mysqld normally (Double Write buffer will be enabled again)

service mysql restart

Since the name "Double Write Buffer" implies two writes, InnoDB will only write data and indexes straight to the table files and bypass writing to the Double Write Buffer within ibdata1. Maybe this will double the import time (pun intended)


The default innodb_log_buffer_size is 8M. You need a bigger Log Buffer.

Please add this line to my.cnf under the [mysqld] group header

innodb_log_buffer_size = 128M

Then, restart mysqld before the reload of the mysqldump.


  • 2
    Hey Rolando, one question about increasing innodb_log_buffer_size - should this be set lower for normal operation or is having it that large beneficial in general production use? Is there any drawback to having it that large? Nov 14, 2019 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.