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