Date: Mon, 24 May 2004 08:02:59 +0800 From: David Xu <davidxu@freebsd.org> To: JG <amd64list@jpgsworld.com> Cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? Message-ID: <40B13BB3.3030807@freebsd.org> In-Reply-To: <5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com> References: <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com>
next in thread | previous in thread | raw e-mail | index | archive | help
JG wrote: > >> last pid: 19842; load averages: 7.32, 1.70, >> 0.67 up 5+10:01:49 >> 21:33:44 >> 110 processes: 1 running, 99 sleeping, 10 lock >> CPU states: 55.6% user, 0.0% nice, 39.4% system, 0.8% interrupt, >> 4.2% idle >> Mem: 312M Active, 64M Inact, 103M Wired, 18M Cache, 60M Buf, 992K Free >> Swap: 3906M Total, 164K Used, 3906M Free >> >> PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU >> COMMAND >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 2.34% 2.34% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 2.25% 2.25% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.15% 2.15% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 2.15% 2.15% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 2.15% 2.15% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.05% 2.05% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.90% 1.90% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.81% 1.81% >> mysqld >> 646 mysql 97 0 285M 282M *Giant 0 400:31 1.81% 1.81% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.71% 1.71% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 1.71% 1.71% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% >> mysqld >> 646 mysql 97 0 285M 282M *Giant 0 400:31 1.56% 1.56% >> mysqld >> 646 mysql 4 0 285M 282M sbwait 0 400:31 1.46% 1.46% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.37% 1.37% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.22% 1.22% >> mysqld >> 646 mysql 4 0 285M 282M sbwait 0 400:31 1.22% 1.22% >> mysqld >> 19813 pete 4 0 2600K 1712K sbwait 0 0:00 1.19% 0.68% >> super-smack >> 19835 pete 4 0 2600K 1712K sbwait 0 0:00 1.11% 0.63% >> super-smack >> 19831 pete 96 0 2600K 1712K *Giant 0 0:00 1.11% 0.63% >> super-smack >> 19837 pete 4 0 2600K 1712K sbwait 1 0:00 1.11% 0.63% >> super-smack >> >> Pete > > > Ok. Well this is definitely a significant discovery. > > Maybe it's because I'm using SCHED_4BSD for the results I posted.. > lets see.. > > /me switches kernels..... > > Using SMP enabled kernel with SCHED_ULE I get: > > last pid: 724; load averages: 1.43, 0.46, 0.17 > up 0+00:18:28 11:11:23 > 64 processes: 4 running, 60 sleeping > CPU states: 0.0% user, 0.0% nice, 50.2% system, 0.0% interrupt, > 49.8% idle > Mem: 66M Active, 6372K Inact, 59M Wired, 124K Cache, 25M Buf, 1846M Free > Swap: 4096M Total, 4096M Free > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 139 0 63568K 50164K CPU0 0 1:41 49.22% 49.22% > mysqld > 581 mysql 139 0 63568K 50164K RUN 0 1:41 42.19% 42.19% > mysqld > 581 mysql 76 0 63568K 50164K select 0 1:41 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K ksesig 0 1:41 0.00% 0.00% > mysqld > 681 root 8 0 9300K 2088K wait 0 0:00 0.00% 0.00% > super-smack > (only 4 myqld's up there while its running... the rest are > super-smack in wait states) > > > But then I ran it again, same command, doing nothing different.... > > And now I get: > > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 77 0 63584K 50628K RUN 0 3:23 60.16% 60.16% > mysqld > 742 root 88 0 9316K 2108K RUN 0 0:07 12.50% 12.50% > super-smack > 732 root 4 0 9316K 2108K sbwait 1 0:07 8.59% 8.59% > super-smack > 734 root 90 0 9316K 2108K RUN 0 0:05 7.81% 7.81% > super-smack > 581 mysql 76 0 63584K 50628K select 1 3:23 0.00% 0.00% > mysqld > 581 mysql 20 0 63584K 50628K ksesig 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > ... many more mysqld's running in the beginning, but after a couple > minutes > the results flip around to look like this: > > last pid: 759; load averages: 3.91, 4.24, 2.10 > up 0+00:23:41 11:16:36 > 64 processes: 3 running, 59 sleeping, 2 zombie > CPU states: 1.8% user, 0.0% nice, 48.6% system, 0.6% interrupt, > 49.0% idle > Mem: 65M Active, 6336K Inact, 60M Wired, 124K Cache, 25M Buf, 1846M Free > Swap: 4096M Total, 4096M Free > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 139 0 63584K 50180K RUN 0 6:49 46.88% 46.88% > mysqld > 581 mysql 139 0 63584K 50180K RUN 0 6:49 42.97% 42.97% > mysqld > 581 mysql 76 0 63584K 50180K select 1 6:49 0.00% 0.00% > mysqld > 581 mysql 20 0 63584K 50180K ksesig 0 6:49 0.00% 0.00% > mysqld > 732 root 4 0 9316K 2108K sbwait 1 0:16 0.00% 0.00% > super-smack > 756 root 4 0 9316K 2108K sbwait 1 0:10 0.00% 0.00% > super-smack > 739 root 4 0 9316K 2108K sbwait 1 0:06 0.00% 0.00% > super-smack > 737 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% > super-smack > 729 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% > super-smack > 744 root 4 0 9316K 2108K sbwait 1 0:02 0.00% 0.00% > super-smack > 746 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% > super-smack > 733 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 731 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 730 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 735 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% > super-smack > 741 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% > super-smack > 750 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 755 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 747 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 751 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 745 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > (+more super-smacks in sbwait state like above) > > > When it's idle with no mysql load / benchmarking going on, it looks > like this: > > last pid: 759; load averages: 1.40, 3.40, 1.98 > up 0+00:24:59 11:17:54 > 33 processes: 1 running, 32 sleeping > CPU states: 0.2% user, 0.0% nice, 0.0% system, 0.4% interrupt, > 99.4% idle > Mem: 60M Active, 5936K Inact, 58M Wired, 124K Cache, 25M Buf, 1854M Free > Swap: 4096M Total, 4096M Free > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 76 0 63568K 50164K select 1 7:27 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K kserel 0 7:27 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K ksesig 0 7:27 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K kserel 0 7:27 0.00% 0.00% > mysqld > 677 root 76 0 6712K 1680K CPU0 0 0:00 0.00% 0.00% top > > > Is this normal behavior? > > Note that default mysql table type is mysql, which uses Giant Lock when doing update on table, it does not support concurrent update, each thread wants to update the table will be serialized, fix me if I am wrong, but some years ago, mysql book tells me the fact, you might need to look other types, for example, BDB or innodb. 50% cpu usage on SMP machine is normal for MySQL server using mysql table type. David Xu
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?40B13BB3.3030807>