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).