* Strange MySQL behaviour
@ 2011-07-27 23:25 Boris Dolgov
2011-07-28 7:34 ` Avi Kivity
0 siblings, 1 reply; 4+ messages in thread
From: Boris Dolgov @ 2011-07-27 23:25 UTC (permalink / raw)
To: kvm
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
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?
--
Boris Dolgov.
^ permalink raw reply [flat|nested] 4+ messages in thread
* Re: Strange MySQL behaviour
2011-07-27 23:25 Strange MySQL behaviour Boris Dolgov
@ 2011-07-28 7:34 ` Avi Kivity
2011-07-29 11:18 ` Boris Dolgov
0 siblings, 1 reply; 4+ messages in thread
From: Avi Kivity @ 2011-07-28 7:34 UTC (permalink / raw)
To: Boris Dolgov; +Cc: kvm
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
^ permalink raw reply [flat|nested] 4+ messages in thread
* Re: Strange MySQL behaviour
2011-07-28 7:34 ` Avi Kivity
@ 2011-07-29 11:18 ` Boris Dolgov
2011-07-31 15:16 ` Avi Kivity
0 siblings, 1 reply; 4+ messages in thread
From: Boris Dolgov @ 2011-07-29 11:18 UTC (permalink / raw)
To: Avi Kivity; +Cc: kvm
Hello!
On Thu, Jul 28, 2011 at 11:34, Avi Kivity <avi@redhat.com> wrote:
> Looks like you are blocked on disk. What does iostat say about disk
> utilization (in both guest and host)?
I also thought so, but host cpu states doesn't show any disk blocking.
iostat 5 with cache=none:
Guest:
avg-cpu: %user %nice %system %iowait %steal %idle
0,10 0,00 0,25 36,48 0,00 63,17
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
vda 177,00 76,80 1308,80 384 6544
dm-0 167,00 76,80 1308,80 384 6544
dm-1 0,00 0,00 0,00 0 0
Host:
avg-cpu: %user %nice %system %iowait %steal %idle
5,62 0,00 1,47 0,50 0,00 92,40
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 185,40 0,00 1212,80 0 6064
sdb 188,60 25,60 1212,80 128 6064
md1 198,40 25,60 1212,80 128 6064
[skip]
dm-12 195,00 25,60 1177,60 128 5888
time mysql ...:
real 7m13.876s
user 0m0.338s
sys 0m0.182s
> Try s/cache=none/cache=unsafe/ as an experiment. Does it help?
With cache=unsafe:
Guest:
avg-cpu: %user %nice %system %iowait %steal %idle
3,15 0,00 8,60 11,00 0,00 77,24
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
vda 3827,60 782,40 20587,20 3912 102936
dm-0 2638,60 779,20 20576,00 3896 102880
dm-1 0,00 0,00 0,00 0 0
Host:
avg-cpu: %user %nice %system %iowait %steal %idle
10,41 0,00 7,73 0,00 0,00 81,86
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 5,40 307,20 40,00 1536 200
sdb 5,40 460,80 40,00 2304 200
md1 99,00 768,00 27,20 3840 136
dm-12 96,00 768,00 0,00 3840 0
4 times followed by
avg-cpu: %user %nice %system %iowait %steal %idle
10,48 0,00 8,18 0,03 0,00 81,31
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 29,00 256,00 16236,80 1280 81184
sdb 27,40 0,00 16236,80 0 81184
md1 2057,80 256,00 16224,00 1280 81120
dm-12 2050,80 256,00 16150,40 1280 80752
2 times
time:
real 0m19.133s
user 0m0.429s
sys 0m0.271s
> Try s/cache=none/cache=none,aio=native/. Does it help? This one is safe,
> you can keep it if it works.
With aio=native:
Guest:
avg-cpu: %user %nice %system %iowait %steal %idle
0,20 0,00 0,50 37,08 0,00 62,22
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
vda 192,00 78,40 1038,40 392 5192
dm-0 133,60 78,40 1040,00 392 5200
dm-1 0,00 0,00 0,00 0 0
Host:
avg-cpu: %user %nice %system %iowait %steal %idle
2,58 0,00 5,44 0,20 0,00 91,77
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 176,80 22,40 1096,00 112 5480
sdb 174,40 0,00 1096,00 0 5480
md1 181,60 22,40 1096,00 112 5480
dm-12 175,20 22,40 1038,40 112 5192
Time:
real 7m7.770s
user 0m0.352s
sys 0m0.217s
If the same mysql command is executed on host directly:
Host:
avg-cpu: %user %nice %system %iowait %steal %idle
3,96 0,00 7,54 8,90 0,00 79,60
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 1273,40 164,80 10252,80 824 51264
sdb 1304,60 352,00 10252,80 1760 51264
md1 1345,00 516,80 10252,80 2584 51264
dm-0 1343,60 516,80 10232,00 2584 51160
Time:
real 0m10.161s
user 0m0.294s
sys 0m0.284s
This is completely testing server, so I can try some new versons or patches.
--
Boris Dolgov.
^ permalink raw reply [flat|nested] 4+ messages in thread
* Re: Strange MySQL behaviour
2011-07-29 11:18 ` Boris Dolgov
@ 2011-07-31 15:16 ` Avi Kivity
0 siblings, 0 replies; 4+ messages in thread
From: Avi Kivity @ 2011-07-31 15:16 UTC (permalink / raw)
To: Boris Dolgov; +Cc: kvm
On 07/29/2011 02:18 PM, Boris Dolgov wrote:
> Hello!
>
> On Thu, Jul 28, 2011 at 11:34, Avi Kivity<avi@redhat.com> wrote:
> > Looks like you are blocked on disk. What does iostat say about disk
> > utilization (in both guest and host)?
> I also thought so, but host cpu states doesn't show any disk blocking.
How do you figure?
Please use iostat -x 1 and report %util.
> time:
> real 0m19.133s
> user 0m0.429s
> sys 0m0.271s
>
Much faster, no? So it looks like slow syncing.
--
error compiling committee.c: too many arguments to function
^ permalink raw reply [flat|nested] 4+ messages in thread
end of thread, other threads:[~2011-07-31 15:16 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2011-07-27 23:25 Strange MySQL behaviour Boris Dolgov
2011-07-28 7:34 ` Avi Kivity
2011-07-29 11:18 ` Boris Dolgov
2011-07-31 15:16 ` Avi Kivity
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox