* 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