Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
Can you post output from mysql --version –  davey May 19 '10 at 6:42
    
I don't think the problem is about CPU/Memory consumptions, but about Disk I/O... –  Kedare May 19 '10 at 10:03

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
    
+1 I've found the same thing :) –  Marco Ramos May 19 '10 at 18:10
    
Should 0 be even faster than 2? –  Alex R May 20 '10 at 2:55

You can try mk-parallel-restore

share|improve this answer
    
the manual says Do not use mk-parallel-restore for backups. –  Alex R May 20 '10 at 13:01

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.

share|improve this answer
    
These are good suggestions but no thanks, I prefer mysqldump for its simplicity, open source, open file format, and "zero configuration" approach on the production server side. –  Alex R May 20 '10 at 12:55
    
xtrabackup is pensource too. –  sumar May 22 '10 at 19:30

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.