MySQL settings useful to speed up a mysqldump import

Question

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

Answer 1

SUGGESTION #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)

SUGGESTION #2

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

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

STEP #1

Login to the Target Server and run

SET GLOBAL innodb_fast_shutdown = 0;

STEP #2

Restart mysqld by setting to innodb_doublewrite to OFF

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

STEP #3

Load the mysqldump into the Target Server

STEP #4

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)

SUGGESTION #3

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

[mysqld]
innodb_log_buffer_size = 128M

Then, restart mysqld before the reload of the mysqldump.

GIVE IT A TRY !!!