Solved

[Hyper-V 2012] MySQL slow in Linux VM

Posted on 2013-01-08
17
734 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
Comment Utility
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
Comment Utility
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
Comment Utility
do you have a hardware RAID controller in the server? write cache enabled on it?
0
 
LVL 1

Author Comment

by:Hoenderdos
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Accepted Solution

by:
Hoenderdos earned 0 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
is your vm using dynamic memory?
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this Micro Tutorial viewers will learn how they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now