3

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.

Improve this question
4
  • Disk issue? You make no mention of disk - database operations are frequently disk-bound.
    – Vérace
    Commented Aug 22, 2015 at 13:57
  • The host has a raid 5 set of 3 internal 15k rpm SCSI drives. This is just something I am playing around with, not a production system so I just installed MySQL on the C:\ drive of the virtual machine. I've literally turned off all other VM's on this host.
    – NewToMySQL
    Commented Aug 22, 2015 at 20:54
  • I feel certain that something is misconfigured in the my.ini file. I played around with it a bit and now I have 4 MySQL.exe process running and each are using exactly 4,124K RAM
    – NewToMySQL
    Commented Aug 23, 2015 at 1:12
  • On different ports? Must be - why run 4 instances? Are they accessing the same datadir? Trouble! Check your disk usage using the relevant Microsoft tools when you're performing your import with only 1 running instance of MySQL.
    – Vérace
    Commented Aug 23, 2015 at 1:17

1 Answer 1

Reset to default
0

  • 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.

Improve this answer
8
  • The 4 dump files I am trying to insert are 1.5, 12, 12 and 4GB each. I let one of the 12 GB ones run for 12 hours and it did not complete. Doing a show processlist show 4 insert statements running (I am trying to import them all at once since they don't seem to be taxing the server in the least). My datadir is currently about 4.5Gb. The dump files are outside of the data dir and total about 25Gb in all.
    – NewToMySQL
    Commented Aug 23, 2015 at 1:19
  • Hmmm... Please show us part of the INSERTs you spotted. I suspect they are very long; only need to see some part. Plus the SHOW CREATE TABLE.
    – Rick James
    Commented Aug 23, 2015 at 2:13
  • INSERT INTO Table1 VALUES (2964325,'nsatime','$2a$12$wo6MLBOWGJABqLu921PfGeCJ4dXndr/7PjES.8m INSERT INTO Table1 VALUES (5140779,0,0,'1976-04-13','me',1,74844,168,4,1,0,2),(5140780,0,0 INSERT INTO table3 VALUES (1078876,1,'0CA3B8D687D06374EB61E72BC4E95A38','24.23.106.13',0,'n INSERT INTO table1 VALUES (2517252,'2008-09-11 14:35:56',0,'2011-10-21 13:16:40',0,2,2,NULL, INSERT INTO table4 VALUES (4569727,'obfuscateddataforprivacy',1,0,0,0,0,0,0),(4569728,'ex
    – NewToMySQL
    Commented Aug 23, 2015 at 13:44
  • Show Create Table times out after 600 seconds.
    – NewToMySQL
    Commented Aug 23, 2015 at 14:01
  • Even just trying to get 10 rows from a table with a select statement times out. On a side note I also have a MS SQL 2012 server on this host and I only allocated 12GB of RAM and 1 CPU to that one and it runs just gets. (it's powered off right now)
    – NewToMySQL
    Commented Aug 23, 2015 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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