none
[Hyper-V 2012] MySQL slow in Linux VM RRS feed

  • Question

  • During the creation of a few Linux VM's (openSUSE) I noticed one of the import scripts was taking a long time creating the data tables needed for a web application. 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 fast, 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 a 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?

    • Moved by Lawrence, Friday, January 11, 2013 8:03 AM
    Wednesday, January 9, 2013 2:10 PM

Answers

  • 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 :)

    • Marked as answer by Priet Tuesday, January 22, 2013 3:57 PM
    Tuesday, January 22, 2013 3:57 PM

All replies

  • Hi,

    What’s the OS version of your openSUSE?

    Have you installed latest Integration Service in the guest Virtual Machine?

    You can download it from:

    Linux Integration Services Version v3.3 for Hyper-V
    http://www.microsoft.com/en-us/download/details.aspx?id=29991

    For Linux related Hyper-V questions we recommend you post in “Linux Integration Services for Microsoft Hyper-V" forum, members in that forum are more professional in Linux related Hyper-V questions.

    Linux Integration Services for Microsoft Hyper-V
    http://social.technet.microsoft.com/Forums/en-US/linuxintegrationservices/threads

    For more information please refer to following MS articles:

    Linux on Hyper-V
    http://blogs.technet.com/b/seanearp/archive/2008/06/29/linux-on-hyper-v.aspx
    Linux Integration Services 3.1 for Hyper-V
    http://blogs.msdn.com/b/virtual_pc_guy/archive/2011/08/04/linux-integration-services-3-1-for-hyper-v.aspx


    Lawrence

    TechNet Community Support

    Thursday, January 10, 2013 7:29 AM
  • Hi, thanks for your response. Is it possible to move this topic to the "Linux Integration Services for Microsoft Hyper-V" forum?

    I'm using OpenSUSE 12.2 x64:

    # cat /etc/os-release
    NAME=openSUSE
    VERSION="12.2 (Mantis)"
    VERSION_ID="12.2"
    PRETTY_NAME="openSUSE 12.2 (Mantis) (x86_64)"
    ID=opensuse
    ANSI_COLOR="0;32"
    CPE_NAME="cpe:/o:opensuse:opensuse:12.2"
    
    # cat /etc/SuSE-release
    openSUSE 12.2 (x86_64)
    VERSION = 12.2
    CODENAME = Mantis

    And apparently version 3.1 is installed:

    # modinfo hv_vmbus
    filename:       /lib/modules/3.4.11-2.16-desktop/kernel/drivers/hv/hv_vmbus.ko
    version:        3.1
    license:        GPL
    srcversion:     16611C8E7737C01FC0124A7
    alias:          acpi*:VMBus:*
    alias:          acpi*:VMBUS:*
    depends:
    intree:         Y
    vermagic:       3.4.11-2.16-desktop SMP preempt mod_unload modversions

    Installing the latest version of the Linux Integration Services did not succeed: OpenSUSE or SLES is not supported. Only RHEL, so I installed CentOS 6.3 just to test if the new version would make any difference.

    Using CentOS 6.3 this LIS installed without any problems. However, the performance was still poor.

    I also tried to use a physical disk (passthrough) for the OS. Though the installation and booting of the VM is blazing fast (much, much faster than using VHD) the MySQL performance was still behind what you'd see on a Hyper-V 2008 server.

    Thursday, January 10, 2013 3:51 PM
  • Hi,

    What’s the OS version of your openSUSE?

    Have you installed latest Integration Service in the guest Virtual Machine?

    You can download it from:

    Linux Integration Services Version v3.3 for Hyper-V

    ~

    To Lawrence :

    ~

    1) SUSE ( both OpenSUSE, SLES) _allways_ contain integrated support of Hyper-V !

    This is first Linux distributive with it!

    ~

    !)  LIS only for RHEL ( and CentOs, SL, etc.)

    ~

    2) For CentOS 6.3 need be LIS v3.4

    v3.3 -- for 6.2, on 6.3 simplyfy can not install: errors

    ~

     

    ==

    # 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

    ==

     If all

     hv_vmbus,hv_utils,hv_netvsc,hv_storvsc,hid_hyperv

    present -- support Hyper-V  allready "On" 







    Friday, January 11, 2013 3:06 PM
  •  Can Hyper-V 2012 achieve the same permformance as 2008/Proxmox/ESXi?

    #

    Q1:

     .VHDX? or .VHD?

    Q2:

     fixed? Or dynamic?

    #

    Possible Answer:

     Try Linux kernel v3.8

    ==

    ----- Original Message -----
    From: "K. Y. Srinivasan" <kys@microsoft.com>
    Sent: Saturday, December 01, 2012 5:46 PM

    Subject: [PATCH 00/28] Drivers: hv: Update the Vmbus protocol

    The current vmbus protocol supported in the vmbus driver for Linux is
    a version of the protocol that shipped with Windows Server 2008. Since
    then a lot of enhancements have been made in the protocol to
    specifically address performance and scalability of the communication
    infrastructure between the host and the guest.
    This patch-set brings our driver to the Win8 (Windows Server 2012) level.
    Furthermore, this code will dynamically negotiate the most efficient
    protocol that the host can support - the same code can be deployed on all
    supported hosts (WS2008, WS2008R2 and WS2012).
    Following are some of the key features implemented in this patch-set:

    1. More efficient signaling protocol between the host and the guest
    2. Distribution of interrupt load across available CPUs in the guest;
       currently all vmbus interrupts are delivered to the boot CPU (CPU 0)
       in the guest.
    3. Per- channel interrupt binding (as part of item 2)
    4. More efficient demultiplexing of incoming interrupts
    5. Per-channel signaling mechanism for host to guest communication


    K. Y. Srinivasan (28):
      Drivers: hv: Implement routines for read side signaling optimization
      Drivers: hv: Add state to manage batched reading
      Drivers: hv: Turn off batched reading for util drivers
      Drivers: hv: Optimize signaling in the read path
      Drivers: hv: Optimize the signaling on the write path
      Drivers: hv: Get rid of hv_get_ringbuffer_interrupt_mask()
      Drivers: hv: Support handling multiple VMBUS versions
      Drivers: hv: Update the ring buffer structure to match win8

      . . .

    ==

    ~

    IMHO, simple way :

      Arch Linux with linux-mainline-3.8rc3-1-x86_64.pkg.tar.xz  from repository http://arch.miffe.org/ 



    P.S. 

    2013-01-23 :

    [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 :)

    ~

     IMHO, this mode is _very_ dangerous for database in case power off, BSOD ( "blue screen") , etc

    ~


    • Edited by Victor Miasnikov Wednesday, January 23, 2013 7:03 AM Add: this mode is _very_ dangerous for DB
    Friday, January 11, 2013 3:17 PM
  • 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 :)

    • Marked as answer by Priet Tuesday, January 22, 2013 3:57 PM
    Tuesday, January 22, 2013 3:57 PM