Mysql – Troubleshooting MySQL Performance Issues

innodbMySQLperformancestoragevmware-vsphere

Environment:

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.

EDIT

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

Best Answer

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
Related Topic