Re: Strange MySQL behaviour

[Avi Kivity <avi@redhat.com>]

On 07/28/2011 02:25 AM, Boris Dolgov wrote:
> Hello!
>
> I am using Fedora 14 with kernel and qemu-kvm from Fedora 15:
> [root@serv ~]# qemu-kvm --help | head -1
> QEMU emulator version 0.14.0 (qemu-kvm-0.14.0), Copyright (c)
> 2003-2008 Fabrice Bellard
> [root@serv ~]# uname -a
> Linux serv 2.6.38.8-35.fc15.x86_64 #1 SMP Wed Jul 6 13:58:54 UTC 2011
> x86_64 x86_64 x86_64 GNU/Linux
>
> I have a guest with latest CentOS6:
> [root@localhost ~]# uname -a
> Linux localhost.localdomain 2.6.32-71.29.1.el6.x86_64 #1 SMP Mon Jun
> 27 19:49:27 BST 2011 x86_64 x86_64 x86_64 GNU/Linux
>
> When I try to load a small MySQL database dump (nearly 10k lines), it
> loads a lot of time.
> Guest cpu states:
> Cpu(s):  0.3%us,  0.6%sy,  0.0%ni, 61.9%id, 37.2%wa,  0.0%hi,  0.0%si,  0.0%st
> MySQL Processlist:
> mysql>  show full processlist;
> +----+------+-----------+--------+---------+------+---------------+------------------------------------------------------------+
> | Id | User | Host      | db     | Command | Time | State         |
> Info                                                       |
> +----+------+-----------+--------+---------+------+---------------+------------------------------------------------------------+
> |  4 | root | localhost | NULL   | Query   |    0 | NULL          |
> show full processlist                                      |
> | 11 | root | localhost | zabbix | Query   |    0 | freeing items |
> INSERT INTO applications VALUES (41,10002,'Performance',0) |
> Guest top:
>    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>   1330 mysql     20   0  560m  29m 5900 S  1.3  6.0   0:05.61 mysqld
>    336 root      20   0     0    0    0 D  0.7  0.0   0:02.39 kdmflush

Looks like you are blocked on disk.  What does iostat say about disk 
utilization (in both guest and host)?

>   1367 root      20   0 14928 1212  924 S  0.7  0.2   0:08.42 top
>    355 root      20   0     0    0    0 D  0.3  0.0   0:03.34
> jbd2/dm-0-8
>   1421 root      20   0 14932 1220  920 R  0.3  0.2   0:00.01 top
>
> Guest is installed inside LVM.
>
> Host cpu states:
> Cpu(s):  2.3%us,  4.0%sy,  0.0%ni, 93.1%id,  0.4%wa,  0.0%hi,  0.1%si,  0.0%st
>
> QEMU command:
> /usr/bin/qemu-kvm -enable-kvm -name VM7 -nographic -vnc 0.0.0.0:7 -vga
> std -m 512 -smp 4 -boot c -pidfile /home/vms/run/7.pid -monitor
> unix:/home/vms/run/7.monitor,server,nowait -serial
> unix:/home/vms/run/7.serial,server,nowait -net
> nic,vlan=0,macaddr=FE:E1:DE:AD:00:07,model=virtio -net
> tap,vlan=0,ifname=tap_7_0,script=/home/vms/ifup,downscript=/home/vms/ifdown
> -drive media=disk,if=virtio,index=0,file=/dev/vg0/cent6,cache=none,boot=on
>
> I can provide any additional debug information.
>
> What is the reason of this behaviour?

Likely, slow disk flushes.

Try s/cache=none/cache=unsafe/ as an experiment.  Does it help?

Try s/cache=none/cache=none,aio=native/.  Does it help?  This one is 
safe, you can keep it if it works.

-- 
error compiling committee.c: too many arguments to function