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