2

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?

2
  • Put your database and log files on faster disk?
    – EEAA
    Jan 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. Jan 31, 2012 at 23:09

5 Answers 5

4

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.

1

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.

4
  • Those tools are deprecated, for reasons explained in the documentation.
    – user48802
    Jan 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.
    – Ladadadada
    Jan 31, 2012 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, 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.
    – user48802
    Feb 20, 2012 at 13:46
1

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.

1
  • There isn't enough information in the OP to make this suggestion. He didn't even state whether he's using MyISAM.
    – user48802
    Jan 31, 2012 at 22:18
0

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.

0

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.

1
  • of course you should just replace /var/lib/mysql according to your datadir setting in my.cnf
    – mlaug
    Feb 1, 2012 at 9:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .