What settings to use for fastest reloading of a MySQL backup?

Question

I have a MySQL database which dumps to a 3.5 GB backup (mysqldump) in about 10 minutes.

But reloading this backup on a standby / test server takes nearly 2 hours [originally 12 before some tuning].

What are some settings that would maximize reloading performance?

The most promising appear to be innodb_buffer_pool_size, innodb_additional_mem_pool_size, and innodb_log_buffer_size... but I'm reaching the limits of my trial-and-error approach. Which of these settings "should" be the most important?

Through trial-and-error I was not able to get more than 70% CPU utilization and 63% memory utilization. I'd like both at 100% during a reload.

All tables are InnoDB.

UPDATE

I was able to reduce the load time from 12 hours to 1h55min with these settings:

innodb_buffer_pool_size=192M
innodb_additional_mem_pool_size=96M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=64M
innodb_file_per_table
key_buffer              = 32M
max_allowed_packet      = 32M

The test server is a 512MB Rackspace Cloud Server.

The production server is a 2GB Athlon 64 X2 4200.

mysql Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2

I'm not sure how much more this can be tuned now... but it still bugs me that only 63% of available RAM is utilized my mysqld during the load.

I don't think this is a disk I/O bound problem. The same server can copy 20MB/s from a zip file. So, using some rough math, only 6 minutes of the 1h55m can be attributed to disk latency. So this really should be CPU-bound or memory-bound, but neither of those are going to 100%.

Answer 1

i found that innodb_flush_log_at_trx_commit has teh biggest influence on the restore speed. Make sure it is set to 2, at least during restore.

Other that that, check the maatkit tools as suggested by lg above, dump/restore is simple, but not very parallel, only mk_parallel_restore can fully utilize your cpu

Answer 2

You can try mk-parallel-restore

Answer 3

Maybe different backup method? I assume, that you want to restore full database server on another server. If you have LVM on your box, you could use LVM snapshot and copy raw database files. Another (and possibly better) option is use xtrabackup instead of mysqldump.