Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!):

enter image description here

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

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

For your dump command, you could use the following

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

Extended inserts are better.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction The Single Transaction option is better for dumping InnoDB tables.

In your my.cnf file, make the following changes temporarily

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_doublewrite

innodb_doublewrite=0

and also

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 2

Restart MySQL and then remove these options after your import and restart MySQL again.

Memory Tables

If you're entire database could fit into memory, you could change the ENGINE=InnoDB to ENGINE=MEMORY in your dump sql file for every table or just the tables that are taking the longest to import, import the dump file back into your database and then change the engine back to innodb.

ALTER TABLE name_of_table ENGINE=InnoDB;

mysqlimport command

http://linux.die.net/man/1/mysqlimport

I have used mysqlimport to import data backed up using the tab format option in mysqldump. It has proven faster than importing via the mysql command.

Dump the database using mysqldump

mysqldump --tab=/some_directory/ -u [USERNAME] -p [DBNAME] [TABLENAME]

Import the data into your database.

mysqlimport -u [USERNAME] -p [DBNAME] /some_directory/tablename.sql

share|improve this answer
add comment

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:

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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