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

  • How many VMs are running on the host? What are those other VMs doing? – mrdenny Mar 5 '13 at 11:57
  • I migrated nearly all VMs to another host, this VM is alone with another testing server on the host. The host is pretty much bored. – duenni Mar 5 '13 at 11:59
  • What's the storage latency in the guest showing you? What's the CPU running at when you run the query on the VM? Same question for when it's a physical box. – mrdenny Mar 5 '13 at 12:01
  • 1
    Yes, I'm not a MySQL guy either, but thanks for participating! The MySQL guys point the finger at "storage problems" and the storage guys point the finger at "database problems", thats a thing we all must live with. – duenni Mar 5 '13 at 13:20
  • 1
    No problem. Sorry I couldn't be more helpful. I'm not seeing anything on the storage side that says problem. And I am a database guy, so I love blaming the storage team when ever possible. – mrdenny Mar 5 '13 at 14:25

Your problem is the storage backend.

From you graph, it is clear that your SAN it is not capable of high random IOPS values (see both aver. iops and aver. queue depth).

To ameliorate the situation, try with the following:

  1. increase the innodb buffer pool size, by editing the my.cnf file and adding (or changing) the line innodb_buffer_pool_size = 8589934592
  2. if it is possible, run a test with a directly attached disk (local to the R720 server) and not via your SAN

This looks like it's the VMWare storage stack causing you issues. Even though you've moved the host onto its own hardware, remember that on shared VMWare storage, all workload using the same datastore uses the same storage resources. VMWare has gotten better at passing the raw performance of the storage up to its VMs, but it's not ever as good as a raw device would be.

Test this theory this way: create a raw device map to (maybe a clone of) your VM and rerun the test. If it's a lot closer to the physical server's results, there's your problem. Edit: If you can't use a raw device from Windows 7, you'd have to try it using a server OS. If that fixes the issue before you get to the raw device, then you'll have another answer :P

In my shop, we're still running prod on VMWare 4, so we have to use RDM for all our databases. You might not have to go that far as you're on a better version of VMWare, but maybe try dedicating a datastore with its own luns on different storage ports for this database.

  • Thanks. I already have mapped a dedicated datastore to the VM. Thats what I meant with raw device in the first post. This is a seperate volume not managed by VMWare, it is mapped with the Windows iSCSI-Initiator with a seperated NIC. We tested to run the DB from this drive and also from drive c: (which is the default VM datastore with VMFS-5) – duenni Mar 5 '13 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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