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.
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 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
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.
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.
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.
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?
http://social.technet.microsoft.com/Forums/en-US/winserver8gen/thread/885b9246-6912-494d-b8c5-518181c67731/