Home / server / Questions / 484800
In Process
duenni
Asked: 2013-03-06 03:53:05 +0800 CST

Troubleshooting MySQL Performance Issues

  • 772

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

mysql

2 Answers

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

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

    • 0