Slow Mysql import

The best option for parallel dump/load is mydumper. which is written by people who know their stuff.

This is off-topic, but innotop is a more capable tool than mytop, by the way.

You could try the Maatkit parallel dump and restore tools.

The Maatkit tools were rolled into the Percona toolkit however I can't find these two tools in the docs.

You may see no improvement in speed depending on how your disks are configured. Some people have seen some significant improvements.

You can attempt to increase value of key_buffer_size variable in my.cnf to 20-25% of available RAM. Its normally set quite low and increasing it will allow for much faster import/dump rates. More info at: http://www.notesbit.com/index.php/web-mysql/mysql/mysql-tuning-optimizing-my-cnf-file/

My limited knowledge doesnt cover multithreading, so I cant help you there.

From other databases, not MySql especially, I know, that it can improve your speed, if you delete indexes before importing, and recreating them afterwards.

Of course this depends on mainly 2 questions: Can you take the database offline while importing, and is the data you try to import proven not to violate restrictions, which are guaranteed by the indexes.

Insert- and update triggers and stored procedures might play a similar role.

You could also just rsync your /var/lib/mysql folder to any other machine. The best scenario for that would be, if you had a slave running. This one you could just stop and shutdown the mysql service. That way the files under /var/lib/mysql won't be changed.

rsync -avhzr ssh user@yourmachine:/var/lib/mysql .

That way I could import my 40G Database within 20 minutes, just depending on the bandwidth of your internet.