Date: Sun, 30 Dec 2007 14:43:54 +0100 From: Gergely CZUCZY <phoemix@harmless.hu> To: Kris Kennaway <kris@FreeBSD.org> Cc: Jeff Roberson <jroberson@chesapeake.net>, freebsd-performance@freebsd.org Subject: Re: mysql scaling questions Message-ID: <20071230134354.GA63555@harmless.hu> In-Reply-To: <47779EBC.5020900@FreeBSD.org> References: <20071201163334.GA21709@harmless.hu> <200712012055.lB1Kt5IQ005728@lava.sentex.ca> <20071201205609.GA54238@harmless.hu> <200712012108.lB1L8qAd005766@lava.sentex.ca> <20071201211012.GA55519@harmless.hu> <20071201122122.S884@192.168.1.107> <20071204130810.GA77186@harmless.hu> <47779AA7.2060801@FreeBSD.org> <20071230132451.GA61295@harmless.hu> <47779EBC.5020900@FreeBSD.org>
next in thread | previous in thread | raw e-mail | index | archive | help
--FL5UXtIhxfXey3p5
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
On Sun, Dec 30, 2007 at 02:35:56PM +0100, Kris Kennaway wrote:
> Gergely CZUCZY wrote:
>=20
> >>I appreciate that you might be constrained by local requirements, but i=
t's really not meaningful to compare different mysql versions=20
> >>if your goal is to study OS performance.
> >It'd be a PITA to install the both versions. Maybe now, that the ports f=
reeze is over, i can
> >do something. But honestly, every mysql version was faster on linux, tha=
n eny on FreeBSD,
> >even the .22 one was faster. We start work on Jan02, I will see what can=
I do.
>=20
> Regardless, we need a stable baseline to compare to.
Cannot do it sooner then january. Any recommended versions?
>=20
> >>* What database engine are you using? I have only tested with innodb b=
ut maybe you are using myisam? Please provide your exact=20
> >>sysbench command lines.
> >MyISAM, of course. InnoDB is not any good with replication, and we need =
backup.
>=20
> OK. I tested briefly and InnoDB is almost 20 times faster on my tests wi=
th default myisam settings. myisam runs into the usual=20
> serious mysql scaling problems at concurrency > 8 threads (contention wit=
hin the mysql application, not a FreeBSD issue).
So, that won't my work easier :) Currently it's impossible to make this box=
to
run FreeBSD this time, because my workplace suffers in heroic linuxism, i j=
ust
took a temporalily downed box to do some tests.
> Still waiting for your sysbench command lines :)
There were 2 scripts and a sources file with options:
# cat sysbench.common
#!/bin/sh
#dbtype=3Dmysql
dbtype=3Dpgsql
tablesize=3D"10000000"
case $dbtype in
mysql)
connect=3D"--mysql-host=3D10.0.0.1 --mysql-port=3D3306 --mysql-db=
=3Dtest --mysql-user=3Droot"
common=3D"--test=3Doltp --db-driver=3Dmysql --oltp-table-size=3D${t=
ablesize}"
;;
pgsql)
connect=3D"--pgsql-host=3D10.0.0.1 --pgsql-port=3D5432 --pgsql-db=
=3Dtest --pgsql-user=3Droot"
common=3D"--test=3Doltp --db-driver=3Dpgsql --oltp-table-size=3D${t=
ablesize}"
;;
*)
echo "Unknown dbtype";
exit 1;
;;
esac
# cat sysbench.common
#!/bin/sh
#dbtype=3Dmysql
dbtype=3Dpgsql
tablesize=3D"10000000"
case $dbtype in
mysql)
connect=3D"--mysql-host=3D10.0.0.1 --mysql-port=3D3306 --mysql-db=
=3Dtest --mysql-user=3Droot"
common=3D"--test=3Doltp --db-driver=3Dmysql --oltp-table-size=3D${t=
ablesize}"
;;
pgsql)
connect=3D"--pgsql-host=3D10.0.0.1 --pgsql-port=3D5432 --pgsql-db=
=3Dtest --pgsql-user=3Droot"
common=3D"--test=3Doltp --db-driver=3Dpgsql --oltp-table-size=3D${t=
ablesize}"
;;
*)
echo "Unknown dbtype";
exit 1;
;;
esac
sol:~# cat sysbench.prepare
#!/bin/sh
=2E ~/sysbench.common
cmd=3D"sysbench ${common} ${connect} prepare"
echo $cmd
$cmd
# cat sysbench.test
#!/bin/sh
=2E ~/sysbench.common
steps=3D"1 2 4 8 16 32 64 128 192 256 384 512"
requests=3D"10000"
# system setup
# - fbsd7-ufs, fbsd7-zfs
# - linux26-cdb2
#flavor=3D"linux26-mysql5041-tcmalloc"
#flavor=3D"linux26-mysql5041"
#flavor=3D"fbsd7-zfs-8k"
#flavor=3D"fbsd7-ufs-pgsql-skiptxn"
#flavor=3D"fbsd7-zfs-pgsql-4BSD"
flavor=3D"fbsd7-zfs-pgsql-4BSD-nosync"
#flavor=3D"fbsd7-ufs-4BSD"
# test type, ro/rw
testtype=3D"rw"
# resultdir
resultdir=3D'/root/results/'
# args
args=3D"${common} ${connect} --max-time=3D60 --max-requests=3D0 --oltp-test=
-mode=3Dcomplex --oltp-dist-iter=3D16"
case ${testtype} in
ro)
args=3D"${args} --oltp-read-only --oltp-skip-trx=3Don"
;;
rw)
test ${dbtype} =3D "mysql" && args=3D"${args} --oltp-skip-trx=3Don"
;;
esac
resultfile() {
local threads=3D$1
echo ${resultdir}/${flavor}.${testtype}.${threads}
}
plotdata=3D`echo ${resultdir}/${flavor}.${testtype}.plot | tr '.-' _`
rm -f ${plotdata}
rm -f ${resultdir}/${flavor}.${testtype}.*
#cmd=3D"sysbench --num-threads=3D16 ${args} run"
#echo $cmd
#$cmd
#exit
echo -n > ${plotdata}
for threads in ${steps}
do
cmd=3D"sysbench --num-threads=3D${threads} ${args} run"
out=3D`resultfile ${threads}`
echo ${cmd} > ${out}
echo "Testing with ${threads}"
success=3D0
while test ${success} -eq 0;
do
date
date >> ${out}
$cmd >> ${out} && success=3D1
test ${success} -eq 0 && (echo "Test failed, restarting")
done
cat ${out}
awk '/read.write requests/ {sub("^.", "", $4); print $4}' ${out} >> ${p=
lotdata}
done
basicall that's everything. I know it's a but complex, but this was all beh=
ind it.
Sincerely,
Gergely Czuczy
mailto: gergely.czuczy@harmless.hu
--=20
Weenies test. Geniuses solve problems that arise.
--FL5UXtIhxfXey3p5
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4 (FreeBSD)
owHtWM2PHEcVN46QUElIhD8APWZXWa89PTszuzu2dzU2/gjWxli2so4sggSu7q7Z
qWx31biqemfHy0QcOXBASCABUpQLRySQENdcIiFx4C/gAGeOnDiF36uerzV2EoG4
ZS2Puqvqffze+71XVf3Tr7524eLrf/ndH7535Sc/+/WXfvv1f6RXyioEc5SU0p1o
k3Ta7U7S297d3U128HJd9a62e93rada7vt29N/7wl3esCcqE5PFkpPYoqNOwNSqk
NvuUDaXzKvSrMEiuifm6u9qPrNdBW7NH2hTaqMXcYyeNHyiXvGkym2tztEfPKhtU
noycNkGmhRLioaHDyjTprspou92kbrt9lWSgdndve3dvt/foAV1pw+0m3Xfa031l
jBzLCY0dNO2JG3RPuSNVTOjOu+/cefe7i/F+t43JGzcOSI5GTmVaBkVhCM0TW1Gp
j4aBUkWZNT44AFQ5pRMqbCYLcupZpZ0qAcI3Ka0C6b4IGx4TsoApYwOVShpAGlQF
BQs15Ug6RbkeADDkqJz4ZwWdKOcRGj/3Rg/YvKMjCzOAA1EfqnxCDw9ppNzAulKa
TLV48UHYyNlFSY8OHt/ipRq+wgHAUJTaMFyob9EDOcFSY8fNGiQvGVkXPA36win1
XLE5C4EmacqkYQs5rNtShSGAtOg2cA6tUT4UkyYpLJ2cR0FjCXXSB+UIb8h1dRrN
9YUhZSY8+G3Yun14t8nqocJER1rdLubUinyLniggly7Q2LpjlnxLmna3SQc01oDo
FZYzELjaFxjNbWue1LfVkXR5oTySMwZohdxJ1gZCUSq9YhauZiVA9o40nDZA1gGo
4Y5j3wy9J00l3aRFt4AARLEl8p5D5Ty4N8WSTZfpCTuVSxiDIaA+YltshGlVeUTy
JsHdoTxRQAWyBAQU2sYa+dLwIU8p7QvEuowpYzEWj6KIt/ayhIZHhWL9I2dPdK5q
0qhTmYW5J37iU2WyIYMEZ3JOh/KROA8mB4e3HjTJDjBZoWhbdADDd28zBTgIEkiP
rJ055dSo0JnkEm4Sa5rHtC9SmR1Xo0XgH95vMbYZotRpNQBAFlnql0VpfUAZU9Cl
WuVLWcfCwyqqaUi5GsiqCDPMyHgICAG4PB9xlfEIGRLJJKp8JYvaD6+ctpWfsdOj
ZDl2iBUIUHruHijrrHIoxGxCN+gaFKB0c0+Xsro3RTbr0BegftReq0KrWIYiRmpO
aEDzldpsiUM7q7CxNRvsfE1gZEsD5t4m3Yl2UUMgGlqGLtEfvWZqAkgpj7kLIU6p
PQWvgxVAuTASZzhu6Doqk5VXcwNowhkqpuL2wkGhoXJWZ3URal9yVb8HWvkggrXH
cDwoWHYIDTzJ7Tg2OHvKTszKvs5GC+ymw8A1N5aaM0DoQjXhXs4xgBSPYR4Fyj9d
JMDpEfLKK1CHkMw48RqQI8HsiAPq94RYQzmHhdoWq7VGrH1zK9Vmyw+xIE8Ddp7+
9t2YELF4HR3xq4g17vVzHmpgV4h/DSEyLpb1ejWiI4ioTulmfOQ/ZN4o1A8EkyTO
JUMwFe+ddov/dWg+zo0T49vb7d5iLEfRYoxjthhDfhzGnLWhsWKHQdVmeDWebBFG
EMrTJHf6JMrUfEsSnkoiqmQGa/0MtJzDnC717u/HxxiIV6CKcy9BVY/PUO3ubHcX
Y+dR1WP/A6qo4L9BdXmJSGVDS413zDE2M0N1Shv7y+lT9O/O/qoC5WX2Bbe+4Nb/
jVveFnvvv8AvnCn5bLFKsH73TXp/60UGiqzMGduima6f1TPT+BRDPKWZuoaILq5D
RsSfF2nN8fkcNrHnjnzkMfrzDjbATo+Qmt4Odbp4ud6l7i5Gru3QbqfbEHzg5c1g
wfwG7EIp9hDel6sRXhMapD6/mlQDHLvqx+cDHyfiJtTtJVmedsXaoJAnljPWmI9H
Qu62dzpJyEocYG3W+NRl52YXppJrxy+ZgDszbvlj7EKn5hXC9Zod7LIN8enzibF+
YrJX2Ko1rMXNk5g/TXJ2y40FD8waSsONeYlTHuebXDuxeMLkxhazf6se8lsbWCjd
kRf8w7IvpQe6gjxN+GSAJb327H0lbe1FZXABiaS0OS/lE3ChTueTufYh0SHWU6e3
6G1nc9+n8/7m7LJolo7x03Suiw9USTzhzgY4/klwp1y+5j+q0I2XGmPs1s/q+psS
BKgRk9+gN954lb1Xq6/7fx1QPnVc2qSzOF/f5mZnP24UHbHoAutni6RMt9bP6lRP
Wyux4OdadCqmYlTYwMd+qHn6eRWwDP2QgqONVrJBP3gKL0tKBpCdq5suRj5T22U0
gxd7SZKYqkyWCFHmNI8bDpZg4bKfrNW/3ORE3WYSg8Pxqi+Cj37zszIOmetnsZFM
RW5j5D7L/DJi571gWVtxY3+6TBMtVz9dzQuMTKNfkJguJxqPEQk+nsYz5VK21u6r
DIfOWAnxfTxkCzOizSZBJfWM2jUfZ4jiowzq3AuuV6vm+Y9jtxxmmi4tdl4k9nl7
vPjSEgJuRHAtb3J74AswIDU2Zy6Z2g/u+Cv25fiY0DWAtjV2qF6a1/0WwVR6qfH9
VqNJDfxf39ncp/hxBY/Tjbm30e9RXyzyHC0JXO88bjvxg4Lku0q888++BhwQb5L1
HUbGbyCzXlJ/EIk3Fb7Ps3iqhn2hcfrXAdeJQ20yXAyKSVOIxceZ51X2fCJKQA92
j47q4VYWh781lK7k63xrWAmRJHzJe6KU0bhFcERbdA8vODp4XC6KE7W86cWLmHQa
V1zx45uvffkCf8Kaf/96/WJ2duHD33xl863u8Z8+SX/UP/3Fz93bf//nNz668MHV
i/e+9tePf/Xx3z76ffrBH/8lv3P45+G/AQ==
=dgWr
-----END PGP SIGNATURE-----
--FL5UXtIhxfXey3p5--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20071230134354.GA63555>