MySQL Import Super Slow

importMySQL

I'm running MySQL on a HyperV virtual machine. The host has 384Gb of RAM and is nowhere near being fully committed. I've tried giving the VM 128G of RAM and 8 CPU and I've tried 2 CPU's and 32GB of RAM. Same symptoms on both configs. The server simply acts like it's not doing anything. It uses only 1-2% of CPU and only 3GB of RAM. Is there some config I should change?

My command to import: 

MySQL.exe -u username -p databasename < filename.dump

I've even tried importing several dumps at the same time and still the server uses little to no resources.

Best Answer

  • If there is less than a few GB of data in all the tables and indexes, then mysqld may use only 3GB of RAM. Look on disk in the datadir; see what the total size if that dir tree is. That will be an approximation of how much RAM might be used.

  • A well tuned mysql will run with under 5% CPU. That includes tuning of the VARIABLES, adding necessary INDEXes, and formulating queries optimally. (Plus other things.)

I once made a 1-line change to a SELECT and it changed from 50% CPU all the time (there were 2 cores) to under 2%.

You say the import is slow? How big is the import file? How long would it take to read that file? The import will be only a small factor slower than the time to read the file.

Check the import file -- does it have INSERT statements that each have lots of rows? That is critical to the speed of import.

Do you have lots of FOREIGN KEYs? Lots of secondary indexes? These may impact the speed.

The import is single-threaded. Multi-threading it (which is not easily done) is mostly useless, since you are probably nearly I/O-bound.

If it has been running for a long time without filling up much disk, then connect and do SHOW PROCESSLIST; to see what it is doing. That might be a useful clue.

Related Question