From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 13:43:59 2007 Return-Path: Delivered-To: freebsd-performance@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 689EA16A421; Sun, 30 Dec 2007 13:43:59 +0000 (UTC) (envelope-from gergely.czuczy@harmless.hu) Received: from marvin.harmless.hu (marvin.harmless.hu [195.56.55.204]) by mx1.freebsd.org (Postfix) with ESMTP id C1EF313C45B; Sun, 30 Dec 2007 13:43:58 +0000 (UTC) (envelope-from gergely.czuczy@harmless.hu) Received: from localhost (marvin-mail [192.168.0.2]) by marvin.harmless.hu (Postfix) with ESMTP id DA9937C0BC6; Sun, 30 Dec 2007 14:43:56 +0100 (CET) X-Virus-Scanned: by amavisd-new-2.4.2 (20060627) (Debian) at harmless.hu Received: from marvin.harmless.hu ([192.168.0.2]) by localhost (marvin.harmless.hu [192.168.0.2]) (amavisd-new, port 10024) with ESMTP id EFL6DDtVeI35; Sun, 30 Dec 2007 14:43:56 +0100 (CET) Received: from marvin.harmless.hu (localhost [127.0.0.1]) by marvin.harmless.hu (Postfix) with ESMTP id 64DBF7C0B9D; Sun, 30 Dec 2007 14:43:54 +0100 (CET) Date: Sun, 30 Dec 2007 14:43:54 +0100 From: Gergely CZUCZY To: Kris Kennaway Message-ID: <20071230134354.GA63555@harmless.hu> 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> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=x-unknown; protocol="application/pgp-signature"; boundary="FL5UXtIhxfXey3p5" Content-Disposition: inline In-Reply-To: <47779EBC.5020900@FreeBSD.org> User-Agent: mutt-ng/devel-r804 (FreeBSD) Cc: Jeff Roberson , freebsd-performance@freebsd.org Subject: Re: mysql scaling questions X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 30 Dec 2007 13:43:59 -0000 --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--