Date: Thu, 23 Jun 2005 15:46:11 +0100 From: Steve Roome <steve@pepcross.com> To: David Sze <dsze@distrust.net> Cc: performance@FreeBSD.org Subject: Re: FreeBSD MySQL still WAY slower than Linux Message-ID: <20050623144611.GA64879@bibipentium.lonres.com> In-Reply-To: <6.2.1.2.2.20050620105116.06376688@wheresmymailserver.com> References: <6.2.1.2.2.20050620105116.06376688@wheresmymailserver.com>
next in thread | previous in thread | raw e-mail | index | archive | help
On Mon, Jun 20, 2005 at 10:51:30AM -0400, David Sze wrote:
> This is what I'll be trying:
>
> FreeBSD/amd64 4.11-RELEASE, linuxthreads
> FreeBSD/amd64 5.4-RELEASE, libpthread
> FreeBSD/amd64 5.4-RELEASE, libpthread, process scope
> FreeBSD/amd64 6.0-CURRENT, libpthread
> FreeBSD/amd64 6.0-CURRENT, libpthread, process scope
> FreeBSD/amd64 6.0-CURRENT, libthr, system scope
> FreeBSD/amd64 6.0-CURRENT, libthr, process scope
> CentOS/amd64 4.0
>
> The different threading libraries are more for completeness. In my last
> test I saw <10% difference between them on amd64.
Well, I finally got some tests out for FreeBSD/i386 with -current,
Here we go with a bunch of results of FreeBSD 6 with mysql and
different threading libraries, as for you, there's sadly not enough in
it so far. Maybe I need to tune a whole bunch of stuff I've missed ?
This is with a fairly basic build of mysql (dynamic and local data
directories specified, IMHO the options have made not a lot of
difference so far in changing these numbers.)
The test run is always:
date
foreach f (1 2 3 4 5) {
/data/supersmack-1.3/bin/super-smack select-key.smack 50 1000 | grep select_index
}
date
with vmstat -systat 35 started before each count and the screen
grabbed at the end (each run takes just under 30 seconds and there's
no other considerable load on the, so vmstat is a rough, but not too
bad guide as to what's going on.)
Roughly speaking:
pthread as system scope, process scope or whatever out of
the box is, is about ~16500 queries per second.
libthr, as it comes out of the box is about 17600 qps.
i.e. on 6.0, with a plain vanilla setup libthr+mysql is about 6%
faster than plain pthreads for this test.
I was kind of hoping for more, any suggested tweaks as this is
still the same speed for me as FreeBSD 5.
I've attached the config file I've used, make.conf and a few other
bits and bobs.
The full results are below:
Steve Roome
pthread:
6tst1: root@unoctbium 3 1 # ldd /data/mysql/libexec/mysqld
6tst1: /data/mysql/libexec/mysqld:
6tst1: libz.so.2 => /lib/libz.so.2 (0x884b9000)
6tst1: libwrap.so.3 => /usr/lib/libwrap.so.3 (0x884c9000)
6tst1: libcrypt.so.2 => /lib/libcrypt.so.2 (0x884d0000)
6tst1: libstdc++.so.4 => /usr/lib/libstdc++.so.4 (0x884e9000)
6tst1: libm.so.3 => /lib/libm.so.3 (0x885b8000)
6tst1: libpthread.so.1 => /usr/lib/libpthread.so.1 (0x885ce000)
6tst1: libc.so.6 => /lib/libc.so.6 (0x885f5000)
6tst1:
6tst1: root@unoctbium 27 0 # foreach f (1 2 3 4 5) {
/data/supersmack-1.3/bin/super-smack select-key.smack 50 1000|grep select_index
}
6tst1: select_index 100000 4 0 17745.75
6tst1: select_index 100000 2 0 16793.02
6tst1: select_index 100000 3 0 16461.78
6tst1: select_index 100000 2 0 16426.02
6tst1: select_index 100000 4 0 16363.65
6tst1:
6tst1: 2 users Load 19.18 7.60 3.44 23 Jun 15:07
6tst1:
6tst1: Mem:KB REAL VIRTUAL VN PAGER SWAP PAGER
6tst1: Tot Share Tot Share Free in out in out
6tst1: Act 1488784 3588 1536232 4432 186704 count
6tst1: All 3328012 5692 17474532 7296 pages
6tst1: Interrupts
6tst1: Proc:r p d s w Csw Trp Sys Int Sof Flt 287 cow 4003 total
6tst1: 71 31592 404 526k 15115712 372 200564 wire 1: atkb
6tst1: 1487616 act 6: fdc0
6tst1: 46.5%Sys 0.0%Intr 40.1%User 0.0%Nice 13.4%Idl 1467508 inact 13: npx
6tst1: | | | | | | | | | | 174776 cache 14: ata
6tst1: =======================>>>>>>>>>>>>>>>>>>>> 11928 free 46: amr
6tst1: daefr 1 64: em0
6tst1: Namei Name-cache Dir-cache 322 prcfr 2001 lapic0: ti
6tst1: Calls hits % hits % react 2001 lapic6: ti
6tst1: 1734 1734 100 pdwake
6tst1: 50 zfod 604 pdpgs
6tst1: Disks amrd0 amrd1 pass0 27 ofod intrn
6tst1: KB/t 16.00 0.00 0.00 54 %slo-z 114464 buf
6tst1: tps 1 0 0 401 tfree 12 dirtybuf
6tst1: MB/s 0.01 0.00 0.00 100000 desiredvnodes
6tst1: % busy 0 0 0 51030 numvnodes
6tst1: 24175 freevnodes
6tst1:
------------------------------------------------------------------------
6tst2: pthread system scope
6tst2: LIBPTHREAD_SYSTEM_SCOPE=yes sh /usr/local/etc/rc.d/420.mysql-server.sh start
6tst2:
6tst2: Thu 23 Jun 2005 15:09:54 BST
6tst2: select_index 100000 4 0 16409.75
6tst2: select_index 100000 2 0 16385.46
6tst2: select_index 100000 3 0 16419.39
6tst2: select_index 100000 2 0 16449.73
6tst2: select_index 100000 7 0 16308.51
6tst2: Thu 23 Jun 2005 15:10:24 BST
6tst2:
6tst2:
6tst2: 2 users Load 20.93 9.89 4.92 23 Jun 15:10
6tst2:
6tst2: Mem:KB REAL VIRTUAL VN PAGER SWAP PAGER
6tst2: Tot Share Tot Share Free in out in out
6tst2: Act 1488732 3588 1536072 4432 186336 count
6tst2: All 3327784 5696 17474672 7296 pages
6tst2: Interrupts
6tst2: Proc:r p d s w Csw Trp Sys Int Sof Flt 293 cow 4004 total
6tst2: 75 30143 408 557k 13014975 377 200968 wire 1: atkb
6tst2: 1487560 act 6: fdc0
6tst2: 47.1%Sys 0.0%Intr 40.4%User 0.0%Nice 12.4%Idl 1467528 inact 13: npx
6tst2: | | | | | | | | | | 174284 cache 14: ata
6tst2: ========================>>>>>>>>>>>>>>>>>>>> 12052 free 1 46: amr
6tst2: daefr 1 64: em0
6tst2: Namei Name-cache Dir-cache 329 prcfr 2001 lapic0: ti
6tst2: Calls hits % hits % react 2001 lapic6: ti
6tst2: 1905 1905 100 pdwake
6tst2: 52 zfod pdpgs
6tst2: Disks amrd0 amrd1 pass0 28 ofod intrn
6tst2: KB/t 14.11 0.00 0.00 54 %slo-z 114464 buf
6tst2: tps 1 0 0 381 tfree 21 dirtybuf
6tst2: MB/s 0.01 0.00 0.00 100000 desiredvnodes
6tst2: % busy 0 0 0 51031 numvnodes
6tst2: 24179 freevnodes
------------------------------------------------------------------------
6tst3: pthread process scope
6tst3: LIBPTHREAD_PROCESS_SCOPE=yes sh /usr/local/etc/rc.d/420.mysql-server.sh start
6tst3:
6tst3:
6tst3: Thu 23 Jun 2005 15:14:34 BST
6tst3: select_index 100000 6 0 15782.35
6tst3: select_index 100000 12 2 16668.64
6tst3: select_index 100000 11 2 16667.71
6tst3: select_index 100000 12 3 16605.03
6tst3: select_index 100000 12 2 16620.37
6tst3: Thu 23 Jun 2005 15:15:05 BST
6tst3:
6tst3: 2 users Load 3.61 5.05 3.99 23 Jun 15:15
6tst3:
6tst3: Mem:KB REAL VIRTUAL VN PAGER SWAP PAGER
6tst3: Tot Share Tot Share Free in out in out
6tst3: Act 1488776 3588 1536232 4432 185820 count
6tst3: All 3327496 5696 17475168 7296 pages
6tst3: Interrupts
6tst3: Proc:r p d s w Csw Trp Sys Int Sof Flt 289 cow 4004 total
6tst3: 29 3191415496 392k 11614723 372 201436 wire 1: atkb
6tst3: 1487600 act 6: fdc0
6tst3: 47.3%Sys 0.0%Intr 39.5%User 0.0%Nice 13.2%Idl 1467536 inact 13: npx
6tst3: | | | | | | | | | | 173480 cache 14: ata
6tst3: ========================>>>>>>>>>>>>>>>>>>> 12340 free 1 46: amr
6tst3: daefr 1 64: em0
6tst3: Namei Name-cache Dir-cache 326 prcfr 2001 lapic0: ti
6tst3: Calls hits % hits % react 2001 lapic6: ti
6tst3: 1877 1877 100 pdwake
6tst3: 52 zfod pdpgs
6tst3: Disks amrd0 amrd1 pass0 30 ofod intrn
6tst3: KB/t 13.24 0.00 0.00 57 %slo-z 114464 buf
6tst3: tps 1 0 0 378 tfree 26 dirtybuf
6tst3: MB/s 0.01 0.00 0.00 100000 desiredvnodes
6tst3: % busy 0 0 0 51032 numvnodes
------------------------------------------------------------------------
6tst4: pthread, libthr
6tst4:
6tst4: root@unoctbium 12 0 # ldd /data/mysql/libexec/mysqld
6tst4: /data/mysql/libexec/mysqld:
6tst4: libz.so.2 => /lib/libz.so.2 (0x884b9000)
6tst4: libwrap.so.3 => /usr/lib/libwrap.so.3 (0x884c9000)
6tst4: libcrypt.so.2 => /lib/libcrypt.so.2 (0x884d0000)
6tst4: libstdc++.so.4 => /usr/lib/libstdc++.so.4 (0x884e9000)
6tst4: libm.so.3 => /lib/libm.so.3 (0x885b8000)
6tst4: libpthread.so.1 => /usr/lib/libthr.so.1 (0x885ce000)
6tst4: libc.so.6 => /lib/libc.so.6 (0x885e0000)
6tst4:
6tst4: root@unoctbium 37 0 # cat /etc/libmap.conf
6tst4: [mysqld]
6tst4: libpthread.so.1 libthr.so.1
6tst4: libpthread.so libthr.so
6tst4:
6tst4:
6tst4: Thu 23 Jun 2005 15:20:12 BST
6tst4: select_index 100000 6 1 17613.29
6tst4: select_index 100000 5 0 17589.25
6tst4: select_index 100000 8 0 17645.14
6tst4: select_index 100000 3 0 17712.81
6tst4: select_index 100000 4 0 17620.33
6tst4:
6tst4: Thu 23 Jun 2005 15:20:41 BST
6tst4:
6tst4:
6tst4: 2 users Load 28.65 11.20 6.37 23 Jun 15:20
6tst4:
6tst4: Mem:KB REAL VIRTUAL VN PAGER SWAP PAGER
6tst4: Tot Share Tot Share Free in out in out
6tst4: Act 1457916 3512 1504052 4360 215256 count
6tst4: All 3298108 5620 17443856 7224 pages
6tst4: Interrupts
6tst4: Proc:r p d s w Csw Trp Sys Int Sof Flt 316 cow 4004 total
6tst4: 75 29760 427 501k 12514703 397 201964 wire 1: atkb
6tst4: 1456924 act 6: fdc0
6tst4: 44.7%Sys 0.0%Intr 36.6%User 0.0%Nice 18.6%Idl 1468236 inact 13: npx
6tst4: | | | | | | | | | | 173492 cache 14: ata
6tst4: ======================>>>>>>>>>>>>>>>>>>> 41764 free 1 46: amr
6tst4: daefr 1 64: em0
6tst4: Namei Name-cache Dir-cache 352 prcfr 2001 lapic0: ti
6tst4: Calls hits % hits % react 2001 lapic6: ti
6tst4: 1722 1722 100 pdwake
6tst4: 51 zfod pdpgs
6tst4: Disks amrd0 amrd1 pass0 23 ofod intrn
6tst4: KB/t 14.61 0.00 0.00 44 %slo-z 114464 buf
6tst4: tps 1 0 0 397 tfree 11 dirtybuf
6tst4: MB/s 0.01 0.00 0.00 100000 desiredvnodes
6tst4: % busy 0 0 0 51117 numvnodes
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20050623144611.GA64879>