Avatar of Hoenderdos
Hoenderdos
Flag for Netherlands asked on

[Hyper-V 2012] MySQL slow in Linux VM

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?
Microsoft Virtual ServerWindows Server 2012MySQL ServerMicrosoft Server OSVirtualization

Avatar of undefined
Last Comment
Roshan Mohammed

8/22/2022 - Mon
Robin CM

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/
Hoenderdos

ASKER
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.
SOLUTION
Robin CM

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Hoenderdos

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Robin CM

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

ASKER
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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Robin CM

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Hoenderdos

ASKER
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?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Roshan Mohammed

is your vm using dynamic memory?