I import a database, and when I run mytop I see only one thread running an insert. On a big database of 80GB that will take a really long time. Are there any tools or maybe an option to increase the number of threads?
-
Put your database and log files on faster disk?– EEAAJan 31, 2012 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 GardeniersJan 31, 2012 at 23:09
5 Answers
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.
-
Those tools are deprecated, for reasons explained in the documentation.– user48802Jan 31, 2012 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. Jan 31, 2012 at 22:31
-
1I don't suppose you would know someone who could add a mention of Mydumper to the Maatkit docs do you? Jan 31, 2012 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.– user48802Feb 20, 2012 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.
-
There isn't enough information in the OP to make this suggestion. He didn't even state whether he's using MyISAM.– user48802Jan 31, 2012 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.
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.
-
of course you should just replace /var/lib/mysql according to your datadir setting in my.cnf– mlaugFeb 1, 2012 at 9:53