Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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
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 '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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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