Solved

[Hyper-V 2012] MySQL slow in Linux VM

Posted on 2013-01-08
17
776 Views
Last Modified: 2015-01-07
During the creation of a few Linux VM's (openSUSE) I noticed the import script was taking a long time creating the data tables. The other INSERT queries progressed at normal speed so I didn't think any of it.

Later it appeared that some parts of the web application were very slow. Some page requests took several seconds to complete.

Now it appears some other SQL queries are very slow too. With a typical SELECT or INSERT there's nothing noteworthy. However, an ALTER TABLE query (such as changing the AUTO_INCREMENT value or ENGINE) was always slow:

ALTER TABLE t ENGINE=MyISAM;
-- Query OK, 0 rows affected (0.23 sec)
ALTER TABLE t ENGINE=InnoDB;
-- Query OK, 0 rows affected (0.52 sec)

Open in new window

Normally such a query takes about 0.00 seconds for MyISAM and up to 0.02 seconds for InnoDB (tested on several servers). On this Linux VM the queries took 0.20 - 0.50 seconds. The values are not always the same but always like these figures and always far more than you'd expect.

Server configuration:
Windows Server 2012 Hyper-V (Intel Core i7 3770 @ 3.4Ghz)
-------------------------------------
OS: Suse 12.2 x64
Harddisk: 32GB IDE
Memory: 1024MB
CPU: 1 core
Network: Network Adapter
-------------------------------------
Setup: 
Dekstop: Minimal Server Selection (Text Mode)
Partitioning:
  - Create swap volume /dev/sda1 (1.46GB)
  - Create root volume /dev/sda2 (30.54 GB) with ext4
Firewall: disabled
SSH: enabled

Open in new window

After installation the following steps were executed to install MySQL:

# reboot
# zypper up
# zypper in mysql-community-server
# service mysql start
# mysql

Open in new window

And the queries to test MySQL:

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE  test;
CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM;
ALTER TABLE t ENGINE=MyISAM;
ALTER TABLE t ENGINE=InnoDB;

Open in new window

I've tried a lot: configuration of InnoDB, Hyper-V, Linux, for example:
Optimized InnoDB configuration
More VM memory
more VM cores
Ext2 in stead of ext4
LVM
/boot on IDE, / on SCSI
OS: OpenSUSE 12.2 / Debian / CentOS 6.3 / SLES

SLES and Debian are both a bit faster, but no where near 0.02s.

As far as I know, the integration tools are installed and loaded:

# zypper search hyper-v
S | Name    | Summary                 | Type
--+---------+-------------------------+-----------
i | hyper-v | Microsoft Hyper-V tools | package
  | hyper-v | Microsoft Hyper-V tools | srcpackage

Open in new window

# lsmod | grep hv_
hv_utils               13493  0
hv_netvsc              31355  0
hv_storvsc             17524  2
hv_vmbus               42608  4 hid_hyperv,hv_utils,hv_netvsc,hv_storvsc

Open in new window


I did not help. When I repeat the exact same steps on a standalone, Proxmox or ESXi server, the queries are fast. And that's with the same default MySQL installation and standard Linux installation.

On a second Hyper-V 2012 server (Core i5 @ 3.1Ghz) the results are almost the same as on the first Hyper-V server.

When I create the same VM on a Hyper-V 2008 R2 server the results are fine! The results are the same as under Proxmox/ESXi/Stand alone. This seems also to be the case on several other 2008 server I tested.

I also run the test in Windows Azure with SLES (small: 1 core/1.75GB). The ALTER TABLE queries were executed in about 0.04s.

I've repeated the tests several times on several servers and each time only the VM's running on Hyper-V 2012 are very slow. One would expect that the Linux support in 2012 would be improved over 2008?

Does anyone has any clue about how this is possible? Can Hyper-V 2012 achieve the same permformance as 2008/Proxmox/ESXi?
0
Comment
Question by:Hoenderdos
  • 5
  • 4
  • 2
  • +1
17 Comments
 
LVL 14

Expert Comment

by:robincm
ID: 38758547
Might be to do with unbuffered storage IO, see this technet forum thread:
http://social.technet.microsoft.com/Forums/en-US/winserver8gen/thread/885b9246-6912-494d-b8c5-518181c67731/
0
 
LVL 1

Author Comment

by:Hoenderdos
ID: 38758774
Thank you for your answer. However, we are using a stand alone server. All VHD(X)'s are stored on the same server (somewhere on C:\).

So I don't think this is causing the slow performance.
0
 
LVL 14

Assisted Solution

by:robincm
robincm earned 166 total points
ID: 38759386
do you have a hardware RAID controller in the server? write cache enabled on it?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:Hoenderdos
ID: 38760306
One of the Hyper-V 2012 servers is using software RAID 1 (2x 3TB), the other is using a single 1 TB disk.

On the second server (the one using the single 1TB drive), I've added an extra 250GB disk. I then created a new VM and selected this drive as physical hard disk (passthrough, attached as IDE 0). This was the only drive (except the CD-ROM) that was assigned to this VM.

The results are much better. The installation of OpenSUSE is blazing fast, as if it was installed on a physical server (and as fast as Proxmox/KVM). Booting the server is a few times faster too. Especially the waiting after the line 'mount -o rw,acl,user_xattr -t ext4 /dev/root /root' before the line 'SMBus base address unitialized - upgrade BIOS or use force_addr=0xaddr'. It now waits for one second in stead of half minute.

MySQL is also much faster: the 'ALTER TABLE t ENGINE=InnoDB' took about 0.10s-0.20s, the MyISAM variant did it under 0.10s.

However, this is still 10 times slower than you'd expect. To make matters worse, using a physical disk for each VM is not an option in production unfortunately.

Maybe there are other software components that have bad performance. I just stumbled upon MySQL and wouldn't know if that's the only thing experiencing problems.
0
 
LVL 14

Expert Comment

by:robincm
ID: 38762143
Glad it's a bit faster. Aside from strongly recommending hardware RAID with write cache (possibly software RAID if you're using SSD) I think that's probably the only help I'm able to give on this question.
I'm assuming if you had the funds you'd be talking to Microsoft for support.
0
 
LVL 1

Author Comment

by:Hoenderdos
ID: 38763331
I don't think using hardware RAID or SSD's would solve this problem: using Hyper-V 2008, (or Proxmox/KVM/ESXi) on the same hardware shows significant better performance.

I also tried using CentOS and the latest Linux Integration Services (3.4 as only 3.1 is available on Suse). But the results weren't any faster.
0
 
LVL 1

Accepted Solution

by:
Hoenderdos earned 0 total points
ID: 38808971
I finally found the solution: turning on the option 'Turn off Windows write-cache buffer flushing on the device'. You can find this option in the properties of the harddisk, select the tab 'Write-caching policy'.

There are two options:

[x] Enable write caching on the device
[x] Turn off Windows write-cache buffer flushing on the device

Check both. The result are directly noticable: all queries now run in 0.00s :)
0
 
LVL 8

Assisted Solution

by:jbvernej
jbvernej earned 334 total points
ID: 38840003
There are two options:

[x] Enable write caching on the device
[x] Turn off Windows write-cache buffer flushing on the device

yes but CAUTION : your data on this disk will be corrupt while power outage.
"To prevent data loss, do not select this check box unless the device has a separate power supply that allows the device to flush its buffer in case of power failure. "

Look at this warning from the MS blog:
http://blogs.msdn.com/b/oldnewthing/archive/2010/09/09/10059575.aspx
0
 
LVL 14

Expert Comment

by:robincm
ID: 38841636
Which goes back nicely to my hardware RAID controller suggestion (assuming it has a battery or flash-backed write cache - which all decent ones should).
Interesting that those settings should make such a difference between OSs on the same hardware.
0
 
LVL 1

Author Comment

by:Hoenderdos
ID: 38841675
I suppose you're both right. However, I'm not in the position to add extra hardware (SSD or BBU RAID controllers) to the server.

I'll have to make due with the hardware I've got. And in my situation I'm running a lot of Linux servers with MySQL installed on it. Altough disabling the writing cache would be much saver, the perfomance drain it causes is unacceptable. Thereby, if the server happens to shut down at any given moment, due to a power failure or a crash, chances are some files are already in an inconssitent state, be it regular files or SQL data. Just everyting can be stored in a single transaction, unfortunately. I figure the power supplied by the datacenter is pretty stable and advanced. On top of that we're making regulare backups (using Veeam).

Just one question left: how do other OS-es and hypervisors threat the write cache? Do they also disable it by default?
0
 
LVL 8

Assisted Solution

by:jbvernej
jbvernej earned 334 total points
ID: 38842824
These settings are only usefull when you have IDE/SATA basic and standalone HDD drive.
It is about management of the internal RAM Cache included on the physical controler INSIDE the HDD.

With RAID or with SAN storage solution, it is unavailable because it is managed internally by RAID hardware layer between Windows and HDD drives, so windows hide these settings. Cache Management made by Hardware is more efficient than the one made by Windows.

These settings are usually found/used on desktop PC not on Servers. Servers are rarely implemented without RAID or SAN.
0
 
LVL 12

Expert Comment

by:S00007359
ID: 39828555
is your vm using dynamic memory?
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question