1 physical machine
- 8GB RAM
- some older Core2Duo CPU
- 1 HDD
- Windows Vista 64 bit
1 Virtual Machine
- 16 GB RAM
- 2 vCPUs
- Windows 7 64bit
Both machines running MySQL 5.5.28 (64Bit) with identical databases. I am administrating the VMWare environment and another user is administrating the database part. The physical machine should be replaced with the virtual machine, so all data was migrated. Problem here is: the performance on the VM is horrible. The DB-admin runs a big query directly on both machines and the physical one is 2-3 times faster than the VM. So we tried a couple of things with the VM: more RAM, more CPUs, i also attached a RAW device with 16kb blocksize, with no effort. Physical always outperforms the VM.
Our VMWare environment consists of 3 Hosts with:
- Host – Dell PowerEdge R720, 2x E5-2640, 8x 8GB RAM, Broadcom BCM57711 10GB HBAs
- Switching – Dell Powerconnect 8024F
- Storage – Dell Equallogic PS4100X iSCSI
- VMWare 5.1, Clustred, HA, no distributed vSwitch
We found some configuration issues an I did several things to try to improve performance:
- Firewall within the VM is off
- Virus scanning is off
- IPV6 is off
On the Hosts there was a latency issue I read about at Dell regarding TCP delayed ack and LRO - it is recommended to turn these off and so I did and it boosted throughput within the VMs a bit (did a quick test with IOMeter). The MySQL database is kind of heavy (120GB file), if i copy it within the VM from one volume to another with Windows Explorer I get constant 130mb/s (VM drive c: - Windows, drive e: - raw device). If the query is run I can see in the Windows Ressource Monitor that the file is read with ~500kb/s. What could be the problem here?
The DBA also told me he tried different database settings within my.ini, tried to split up the huge db file in smaller ones, all to no effort (personally I am not a MySQL expert so I have to believe him).
I know that Windows 7 is not the best OS to run as a DB server but this should be a quick test for a couple of days, later we will use 2008 R2. I will try and do some testing with ioping and/or IOMeter (any reccommendations for this?). Thanks in advance.
Monitoring the raw device directly on the SAN:
WHile doing DB Query: http://s1.directupload.net/file/d/3185/x5rpsmg5_png.htm
While doing Filecopy with Windows Explorer: http://s14.directupload.net/file/d/3185/ug6zlpki_png.htm
CPU Load of VM while doing the above: http://s14.directupload.net/file/d/3185/2qgmbx9q_png.htm