up vote 2 down vote favorite
1
share [g+] share [fb]

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 ?

link|improve this question
43% accept rate
Put your database and log files on faster disk? – ErikA Jan 31 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 at 23:09
feedback

5 Answers

up vote 4 down vote

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.

link|improve this answer
feedback
up vote 1 down vote

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.

link|improve this answer
Those tools are deprecated, for reasons explained in the documentation. – Baron Schwartz Jan 31 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 at 22:31
1  
I don't suppose you would know someone who could add a mention of Mydumper to the Maatkit docs do you? – Ladadadada Jan 31 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 at 13:46
feedback
up vote 0 down vote

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.

link|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 at 22:18
feedback
up vote 0 down vote

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.

link|improve this answer
feedback
up vote 0 down vote

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.

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

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.