Take the 2-minute tour ×

I import a database, and when i run mytop i see only one thread running an insert. On a big database of 80G that will take really big time, is there any tools or may be an option to increase number of threads ?

share|improve this question
Put your database and log files on faster disk? –  EEAA Jan 31 '12 at 14:49
Without seeing the format of the import and what locks, if any, are being used it's a bit hard to directly answer the question. Most often import performance can be improved by improving the import file, so you really need to look at how that is created. –  John Gardeniers Jan 31 '12 at 23:09

5 Answers 5

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
Those tools are deprecated, for reasons explained in the documentation. –  Baron Schwartz Jan 31 '12 at 22:18
Tools are usually deprecated in favour of newer and better tools. Now that I know about Mydumper, I heartily recommend it instead of the Maatkit tools. –  Ladadadada Jan 31 '12 at 22:31
I don't suppose you would know someone who could add a mention of Mydumper to the Maatkit docs do you? –  Ladadadada Jan 31 '12 at 22:37
I could, in my copious spare time :) But I would rather not. There may be an even better solution in the future, and I want people to find what's best for them, not the specific tool that some possibly stale documentation recommends. –  Baron Schwartz Feb 20 '12 at 13:46

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.

share|improve this answer
There isn't enough information in the OP to make this suggestion. He didn't even state whether he's using MyISAM. –  Baron Schwartz Jan 31 '12 at 22:18

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.

share|improve this answer

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.

share|improve this answer
of course you should just replace /var/lib/mysql according to your datadir setting in my.cnf –  mlaug Feb 1 '12 at 9:53

Your Answer


