Slow MySQL Database Import with mysqldump and USE .. SOURCE

I have about 12 tables in MySQL innoDB, one of them has 11 million records in it.

I used this command to back things up:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

And this command to import things on the new server:

USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;

Here is the pain I am afflicted with (the time is going up with each query!):

What can I do to speed things up? Is something wrong with my mysqldump command?

Bulk loading InnoDB can be very daunting if your do not have InnoDB properly tuned.

Here are the settings you need to be concerned with:

• innodb_buffer_pool_size

• innodb_log_file_size (See How to safely change MySQL innodb variable 'innodb_log_file_size'?)

• innodb_read_io_threads (MySQL 5.5 only)

• innodb_write_io_threads (MySQL 5.5 only)

• innodb_io_capacity (MySQL 5.5 only)

• innodb_thread_concurrency (Default of 0 is best)

Up to half of the Buffer Pool can be used for bulk inserts. This pushes out cached data. During a reload of a mysqldump into InnoDB tables, the Buffer Pool becomes a open war zone between newly loaded InnoDB data and changes to secondary indexes. In light of this, increasing innodb_buffer_pool_size and innodb_log_file_size is vital and imperative.

Also, make sure these are set off. A mysqldump should have these variables being set to 0.

UNIQUE_CHECKS=0;
FOREIGN_KEY_CHECKS=0;

You can check for that with a simple head -20 on the mysqldump file.

There is one more import aspect: innodb_change_buffering

According to the MySQL Documentation, you can dictate what gets buffered during any DML operations. For full clarification on this, see Controlling InnoDB Change Buffering.

I recommend the following settings:

[mysqld]
innodb_buffer_pool_size=2G
innodb_log_file_size=512M
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000
innodb_thread_concurrency=0

StackExchange Database Administrators Q#20832, answer score: 7