[SOLVED] Win7 x64 guest with SQLServer 2012 = High CPU usage

Discussion in 'Proxmox VE: Installation and configuration' started by mat02, Aug 28, 2013.

  1. mat02

    mat02 New Member

    Joined:
    Aug 28, 2013
    Messages:
    7
    Likes Received:
    1
    Hello,
    I've installed Proxmox 3.1-3 on my HP Proliant with 12GB of RAM, 1x Xeon E5120 @1.86GHz (2 cores).
    At the moment I have 5 CT and 2VM - one VM is Pfsense Gateway and another one is Windows 7 x64 HP.

    On that Windows machine I've installed SQLServer 2012 Express and VNC server (UltraVNC) as well as virtio drivers.
    The problem is that even in idle state, guest CPU usage is about 30-40% (proxmox reports very similiar values) and there are spikes to 100%. What can be the cause of such problem?

    Here's my VM .conf file:
    Code:
    balloon: 2048boot: cdn
    bootdisk: virtio0
    cores: 2
    cpu: host
    memory: 6144
    name: Windows
    net0: virtio=62:46:A0:EB:AE:83,bridge=vmbr1
    onboot: 1
    ostype: win7
    sockets: 1
    startup: order=3
    tablet: 0
    virtio0: local:104/vm-104-disk-1.qcow2,format=qcow2,cache=writeback,size=57G
    And here is what dstat reports when this machine is off:
    Code:
    ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
    usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
     17  14  69   0   0   0| 280k  408k|   0     0 | 125B  230B|6745    14k
     10   7  83   0   0   0|   0    40k| 454k  458k|   0     0 |3451  6679 
     10   8  82   0   0   0|   0   128k|  53k   50k|   0     0 |2659  5422 
     11   9  80   0   0   0|   0   128k| 557k  521k|   0     0 |3667  7097 
     24   6  70   0   0   1|   0    40k| 132k  226k|   0     0 |3145  5373 
     11   7  83   0   0   0|8192B  184k| 414k  440k|   0     0 |3254  6122 
      6   4  90   0   0   0|   0     0 | 162k  176k|   0     0 |2904  6134 
      9   6  85   0   0   0|   0    96k| 560k  554k|   0     0 |3390  6931 
      5   4  92   0   0   0|   0   112k|  48k   50k|   0     0 |2406  4514 
      3   3  94   0   0   0|   0     0 |  44k   41k|   0     0 |2320  4252 
      4   3  93   0   0   0|   0     0 |  71k   65k|   0     0 |2364  4408 
      4   3  93   0   0   1|   0     0 |  39k   47k|   0     0 |2236  4192 
      6   8  85   0   0   0|   0    72k|  55k   50k|   0     0 |2609  5480 
      3   3  94   1   0   0|   0   656k|  33k   38k|   0     0 |2280  4285 
      6   3  91   0   0   0|   0     0 |  61k   56k|   0     0 |2342  4273 
      5   4  91   0   0   0|   0     0 |  68k   69k|   0     0 |2445  4417 
      5   3  92   0   0   0|   0     0 |  34k   36k|   0     0 |2314  4252 
      7   4  89   1   0   0|  40k   72k|  64k   68k|   0     0 |2495  4472 
     14   8  78   0   0   0|   0    72k| 622k  552k|   0     0 |3998  7537
    
    And here's with Windows VM running. Look at the increase in int and csw :/

    Code:
    ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
     17  14  69   0   0   0| 282k  408k|   0     0 | 125B  249B|6751    14k
     32  18  29  21   0   0|  16M   80k| 360k  212k|   0     0 |6739    14k
     36  18  25  20   0   1|  11M  576k| 297k  325k|   0     0 |7105    16k
     30  17  35  18   0   0|7656k    0 | 297k  253k|   0     0 |6777    15k
     45  34  13   7   0   0|  13M   64k| 607k  913k|   0    60k|9570    14k
     26  20  30  23   0   1|  17M  224k| 553k  281k|   0   216k|8392    16k
     21  16  38  26   0   0|  16M   48k| 115k   69k|   0     0 |5567    15k
     32  25  23  19   0   0|  13M  856k| 387k  623k|   0     0 |8201    18k
     22  16  37  25   0   0|  14M   80k| 390k  158k|   0    76k|6339    15k
     22  16  61   1   0   1| 184k  344k| 232k  277k|   0     0 |5981    14k
     36  17  29  18   0   0|  15M    0 | 198k  199k|   0     0 |6721    14k
     27  17  32  23   0   0|  16M  152k| 110k  136k|   0   100k|6442    14k
     25  17  26  32   0   0|  14M  800k| 156k  132k|   0    16k|6232    14k
     34  27  27  14   0   0|  13M   40k|  85k  136k|   0     0 |8868    16k
     54  36   6   4   0   1|  15M  256k| 124k   90k|   0     0 |  29k   16k
     50  41   5   3   0   1|  17M    0 |  92k  121k|   0     0 |  31k   17k
     58  35   5   2   0   1|8464k  200k| 146k  129k|   0     0 |  31k   11k
     53  35   8   5   0   0|  13M  960k| 227k  192k|   0     0 |  24k   18k
     52  38   7   3   0   1|  14M    0 | 137k  167k|   0     0 |  28k   18k
     79  21   1   0   0   1|  10M  164k|  64k   97k|   0     0 |6132    11k
     83  17   1   0   0   0| 368k  664k|  41k   48k|   0     0 |5617  6146 
     83  17   0   0   0   0|6800k  344k|  88k   76k|   0     0 |6085  6537 
     82  18   0   0   0   1|9376k  108k| 133k  102k|   0     0 |5678  7933
    pveversion --verbose
    Code:
    proxmox-ve-2.6.32: 3.1-109 (running kernel: 2.6.32-23-pve)pve-manager: 3.1-3 (running version: 3.1-3/dc0e9b0e)
    pve-kernel-2.6.32-20-pve: 2.6.32-100
    pve-kernel-2.6.32-19-pve: 2.6.32-95
    pve-kernel-2.6.32-16-pve: 2.6.32-82
    pve-kernel-2.6.32-22-pve: 2.6.32-107
    pve-kernel-2.6.32-23-pve: 2.6.32-109
    lvm2: 2.02.98-pve4
    clvm: 2.02.98-pve4
    corosync-pve: 1.4.5-1
    openais-pve: 1.1.4-3
    libqb0: 0.11.1-2
    redhat-cluster-pve: 3.2.0-2
    resource-agents-pve: 3.9.2-4
    fence-agents-pve: 4.0.0-1
    pve-cluster: 3.0-7
    qemu-server: 3.1-1
    pve-firmware: 1.0-23
    libpve-common-perl: 3.0-6
    libpve-access-control: 3.0-6
    libpve-storage-perl: 3.0-10
    pve-libspice-server1: 0.12.4-1
    vncterm: 1.1-4
    vzctl: 4.0-1pve3
    vzprocps: 2.0.11-2
    vzquota: 3.1-2
    pve-qemu-kvm: 1.4-17
    ksm-control-daemon: 1.1-1
    glusterfs-client: 3.4.0-2
    
    
    
     
    #1 mat02, Aug 28, 2013
    Last edited: Aug 28, 2013
  2. spirit

    spirit Well-Known Member
    Proxmox VE Subscriber

    Joined:
    Apr 2, 2010
    Messages:
    3,196
    Likes Received:
    110
    Hi, this is because of interrupts generated by sqlserver timer.

    http://pve.proxmox.com/wiki/Performance_Tweaks

    Trace Flag T8038

    Setting the trace flag -T8038 will drastically reduce the number of context switches when running SQL 2005 or 2008.
    To change the trace flag:

    1. Open the SQL server Configuration Manager
    2. Open the properties for the SQL service typically named MSSQLSERVER
    3. Go to the advanced tab
    4. Append ;-T8038 to the end of the startup parameters option
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. mat02

    mat02 New Member

    Joined:
    Aug 28, 2013
    Messages:
    7
    Likes Received:
    1
    Hi, thank you for your reply.
    Unfortunately, this doesn't seem to work on SQL 2012, as I've added this option to Startup parameters beforme my OP (stats in OP are with this parameter and several VM reboots).
    Although, I can't add this parameter in Advanced tab, so I've did it like this:
    https://www.dropbox.com/s/xydw91ez16jixf6/Zrzut ekranu 2013-08-28 (godz. 10.35.33).png
    https://www.dropbox.com/s/t3oxhdbpxmr3wbm/Zrzut ekranu 2013-08-28 (godz. 10.36.03).png
    On that last screenshot you can see, that CPU usage fluctuates, and it's at 7-8% for brief moments, but then System process starts to hog the CPU.
     
  4. spirit

    spirit Well-Known Member
    Proxmox VE Subscriber

    Joined:
    Apr 2, 2010
    Messages:
    3,196
    Likes Received:
    110
    Indeed, this is strange. I don't think it's related to interrupts in this case, as you see high cpu inside the vm.
    If you stop the sqlserver service, does the system cpu go down ?
    Do you use proxmox 3.1 ?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. mat02

    mat02 New Member

    Joined:
    Aug 28, 2013
    Messages:
    7
    Likes Received:
    1
  6. spirit

    spirit Well-Known Member
    Proxmox VE Subscriber

    Joined:
    Apr 2, 2010
    Messages:
    3,196
    Likes Received:
    110

    Do you use ballooning and dynamic memory ?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. mat02

    mat02 New Member

    Joined:
    Aug 28, 2013
    Messages:
    7
    Likes Received:
    1
    Yes, I do, with virtio Balloning Driver & installed service.

    UPDATE:
    I've disabled balloning and now it's about 6-15% with SQL :) I think that solves the problem.
    Thank you :D
     
    #7 mat02, Aug 28, 2013
    Last edited: Aug 28, 2013
  8. spirit

    spirit Well-Known Member
    Proxmox VE Subscriber

    Joined:
    Apr 2, 2010
    Messages:
    3,196
    Likes Received:
    110
    Great :)

    Never use balloning with databases, sql server try to alloc all memory by default, and with balloning in the game, it'll go up and down all the time. (and use cpu)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice