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