How to tune MySQL for restoration from MySQL dump?

Question

My little 5GB database which takes 5 minutes to dump via mysqldump, takes 9 hours to restore. Luckily I found this out during a test-run, not an actual emergency situation.

What are the best parameters to optimize to speed this up?

I have tried the following settings on my server with 2GB of RAM:

innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=50M
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=1G
innodb_log_buffer_size=1G

The weird thing is that even with these aggressize settings, top only shows mysqld is barely using a fraction of the assigned memory:

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
4421 mysql     20   0  247m  76m 5992 S   91  3.7   4:09.33 mysqld

Answer 1

You should be able to compute these numbers before the mysqldump.

Concerning the settings you gave in the question,

innodb_log_file_size=1G

This setting is WAY TOO BIG!!!

The innodb_log_file_size is supposed to be 25% of innodb_buffer_pool_size

innodb_log_file_size=128M

Once you set this in /etc/my.cnf, you must do the following to resize your InnoDB log files:

1. service mysql stop
2. rm -f /var/lib/mysql/ib_logfile[01]
3. service mysql start

As for the other setting

innodb_log_buffer_size=1G

You never want to cache a ton of data in here before sending them to the InnoDB Log Files, especially for mysqldump reloads or heavy transactional COMMITs. The should be an order of magnitude smaller.

innodb_log_buffer_size=32M

BTW You should disable binary logging before reloading. Otherwise, all the data lands in your binary logs. Please do one of the following:

1. Make this -> SET SQL_LOG_BIN=0; the first line of the mysqldump file.
2. From MySQL command line, run SET SQL_LOG_BIN=0; then run source < mysqldumpfile >
3. Comment out log-bin from /etc/my.cnf and restart MySQL 5.1, load the mysqldump file, uncomment log-bin, and restart MySQL.

UPDATE 2011-07-24 20:30

If you have a mysqldump file /root/MyData.sql, you can still run the commands like this

SET SQL_LOG_BIN=0;
source /root/MyData.sql

This falls under option 2.

Answer 2

I faced a similar situation with a dump restoration taking too long. In my case I was able to speed up the restoration 3-10 times by postponing the MySQL index creation.

In short, the index is recalculated on every insert. So it's better to calculate the index for the whole table all at once.

Here is an example, how MySQL exports data:

CREATE TABLE `SOME_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SOME_COLUMN` varchar(255) NOT NULL,
  `OTHER_COLUMN` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`),
  KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`)
);

INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`)
VALUES (...), (...), ... ;

If you have millions of rows in a table with indexes it will be restored much longer as if indices were applied after inserts.

You will get higher import speed if you just move index creation statements after inserts:

CREATE TABLE `SOME_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SOME_COLUMN` varchar(255) NOT NULL,
  `OTHER_COLUMN` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `SOME_TABLE` (`id`, `SOME_COLUMN`, `OTHER_COLUMN`)
VALUES (...), (...), ... ;

ALTER TABLE `SOME_TABLE`
ADD UNIQUE KEY `UK_SOME_COLUMN` (`SOME_COLUMN`),
ADD KEY `IDX_OTHER_COLUMN` (`OTHER_COLUMN`);

Check my repository for a simple python script, which does the above transformation.

Answer 3

Your problem is I/O, not memory. If you profile the disk, you'll find it thrashing madly to read and write, especially if it's on the same disk as the backup.

I'd move the file to a different physical drive and see if that helps.