troubleshooting Question

[Hyper-V 2012] MySQL slow in Linux VM

Avatar of Hoenderdos
HoenderdosFlag for Netherlands asked on
MySQL ServerMicrosoft Server OSMicrosoft Virtual ServerVirtualizationWindows Server 2012
12 Comments4 Solutions1247 ViewsLast Modified:
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)
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
After installation the following steps were executed to install MySQL:

# reboot
# zypper up
# zypper in mysql-community-server
# service mysql start
# mysql
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;
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
# 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

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?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 4 Answers and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros