From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 13:18:33 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 5EB9A16A41A for ; Sun, 30 Dec 2007 13:18:33 +0000 (UTC) (envelope-from kris@FreeBSD.org) Received: from weak.local (freefall.freebsd.org [IPv6:2001:4f8:fff6::28]) by mx1.freebsd.org (Postfix) with ESMTP id 6030D13C43E; Sun, 30 Dec 2007 13:18:32 +0000 (UTC) (envelope-from kris@FreeBSD.org) Message-ID: <47779AA7.2060801@FreeBSD.org> Date: Sun, 30 Dec 2007 14:18:31 +0100 From: Kris Kennaway User-Agent: Thunderbird 2.0.0.9 (Macintosh/20071031) MIME-Version: 1.0 To: Gergely CZUCZY References: <20071129101729.GA57985@harmless.hu> <20071130143023.I884@192.168.1.107> <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> In-Reply-To: <20071204130810.GA77186@harmless.hu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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:18:33 -0000 Gergely CZUCZY wrote: > On Sat, Dec 01, 2007 at 12:22:34PM -1000, Jeff Roberson wrote: >> On Sat, 1 Dec 2007, Gergely CZUCZY wrote: >> >>> On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote: >>>> At 03:56 PM 12/1/2007, Gergely CZUCZY wrote: >>>>> I don't quite understand the question. It's the very same box, with >>>>> a dualboot configuration. >>>> Fire up the 3ware controller's RAID management software and make sure the same write caching strategy is set for FreeBSD and Linux. The >>>> driver my default to different values. >>>> >>>> i.e. under "controller settings" make sure "write cache" and "queuing" are the same values for linux and freebsd. >>> Let's get back to this on monday. I'm at home now, and the >>> box is at me workplace, still running a test (i can't reboot it). >> Also, can you verify with a read-only test to see where it's at? I have not tested writes with that many threads. I notice mysql goes much faster >> with a fresh table too. So can you blow away and recreate the sysbench tables and then do read-only? If that is much slower we'll know there is some >> configuration problem or similar. > It will all be available here: > http://phoemix.harmless.hu/mysql/ Hi, Sorry for the late reply, I was on vacation. I have a couple of comments about this: * You are using some very old mysql versions and trying to compare between diferent versions. New minor releases in the 5.0 series had very different performance, and you really need to be using at least 5.0.45 (I didn't notice a performance change between 5.0.45 and 5.0.51 but there was a big improvement in every release up to .45. In addition, versions older than (I think) .37 have serious performance bugs). I appreciate that you might be constrained by local requirements, but it's really not meaningful to compare different mysql versions if your goal is to study OS performance. * What database engine are you using? I have only tested with innodb but maybe you are using myisam? Please provide your exact sysbench command lines. * Compare to my config file here: http://people.freebsd.org/~kris/scaling/my.cnf The default mysql config has very poor performance for innodb (you need at least innodb_thread_concurrency = 0 to disable some mysql brain-death). Maybe tuning is required for myisam also. * Also make sure you are using identical config settings on the two systems. * With the above, please compare read-only mode also (I think Jeff already asked you about this but I didnt see a reply). That will allow us to calibrate what is going on. Kris From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 13:24:56 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 B7AC116A418; Sun, 30 Dec 2007 13:24:56 +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 1F9DD13C455; Sun, 30 Dec 2007 13:24:55 +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 7BE047C0078; Sun, 30 Dec 2007 14:24:53 +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 j+fr6mPMlUWv; Sun, 30 Dec 2007 14:24:53 +0100 (CET) Received: from marvin.harmless.hu (localhost [127.0.0.1]) by marvin.harmless.hu (Postfix) with ESMTP id 119647BFFB3; Sun, 30 Dec 2007 14:24:51 +0100 (CET) Date: Sun, 30 Dec 2007 14:24:51 +0100 From: Gergely CZUCZY To: Kris Kennaway Message-ID: <20071230132451.GA61295@harmless.hu> References: <20071129101729.GA57985@harmless.hu> <20071130143023.I884@192.168.1.107> <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> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=x-unknown; protocol="application/pgp-signature"; boundary="yrj/dFKFPuw6o+aM" Content-Disposition: inline In-Reply-To: <47779AA7.2060801@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:24:56 -0000 --yrj/dFKFPuw6o+aM Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Sun, Dec 30, 2007 at 02:18:31PM +0100, Kris Kennaway wrote: > Gergely CZUCZY wrote: > >On Sat, Dec 01, 2007 at 12:22:34PM -1000, Jeff Roberson wrote: > >>On Sat, 1 Dec 2007, Gergely CZUCZY wrote: > >> > >>>On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote: > >>>>At 03:56 PM 12/1/2007, Gergely CZUCZY wrote: > >>>>>I don't quite understand the question. It's the very same box, with > >>>>>a dualboot configuration. > >>>>Fire up the 3ware controller's RAID management software and make sure= the same write caching strategy is set for FreeBSD and Linux.=20 > >>>>The > >>>>driver my default to different values. > >>>> > >>>>i.e. under "controller settings" make sure "write cache" and "queuing= " are the same values for linux and freebsd. > >>>Let's get back to this on monday. I'm at home now, and the > >>>box is at me workplace, still running a test (i can't reboot it). > >>Also, can you verify with a read-only test to see where it's at? I hav= e not tested writes with that many threads. I notice mysql=20 > >>goes much faster with a fresh table too. So can you blow away and recr= eate the sysbench tables and then do read-only? If that is=20 > >>much slower we'll know there is some configuration problem or similar. > >It will all be available here: > >http://phoemix.harmless.hu/mysql/ >=20 > Hi, >=20 > Sorry for the late reply, I was on vacation. I have a couple of comments= about this: >=20 > * You are using some very old mysql versions and trying to compare betwee= n diferent versions. New minor releases in the 5.0 series had=20 > very different performance, and you really need to be using at least 5.0.= 45 (I didn't notice a performance change between 5.0.45 and=20 > 5.0.51 but there was a big improvement in every release up to .45. In ad= dition, versions older than (I think) .37 have serious=20 > performance bugs). >=20 > I appreciate that you might be constrained by local requirements, but it'= s really not meaningful to compare different mysql versions if=20 > your goal is to study OS performance. It'd be a PITA to install the both versions. Maybe now, that the ports free= ze is over, i can do something. But honestly, every mysql version was faster on linux, than e= ny on FreeBSD, even the .22 one was faster. We start work on Jan02, I will see what can I = do. >=20 > * What database engine are you using? I have only tested with innodb but= 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 bac= kup. > * Compare to my config file here: >=20 > http://people.freebsd.org/~kris/scaling/my.cnf >=20 > The default mysql config has very poor performance for innodb (you need a= t least innodb_thread_concurrency =3D 0 to disable some mysql=20 > brain-death). Maybe tuning is required for myisam also. >=20 > * Also make sure you are using identical config settings on the two syste= ms. http://phoemix.harmless.hu/mysql/ verify yourself. Only the paths are updated, but the main parameters are just the same. > * With the above, please compare read-only mode also (I think Jeff alread= y asked you about this but I didnt see a reply). That will=20 > allow us to calibrate what is going on. http://phoemix.harmless.hu/mysql/ro-all.png The graph is mistitled as Read-write, but it's a readonly test. Sincerely, Gergely Czuczy mailto: gergely.czuczy@harmless.hu --=20 Weenies test. Geniuses solve problems that arise. --yrj/dFKFPuw6o+aM Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4 (FreeBSD) owGFV8+PHEcVNokSiZaCFA5wLa1AayczvTOzP+JMNGvWu8TZJIut7MKScLCqu6u7 i+2ualdVz2z7wBEhwQEhDkgGFIkTBwQH/g4O/AVIXPgHuPO9qu6ZMVJkS7Znuqte fe+97/teza/fev3Oa2//429//8m7v/rNi6/95RunyTt165wqxjU3S6nG08lkOj6a zt4/HB+N35/l9w8ns6P8KEnupwePVr/fOdXKCeXGV10j5syJW7fXVFyqD1hacmOF W7QuH9+PhnVn0jbaSie1mjOpKqnE+t2V4crmwoy/r1KdSVXM2bNWO5GNGyOV40kl ouixYpetGrEzkbL9yYjNJpP3GHdsMptP78/3p08u2LsToB6xT4y07BOhFF/xjq0M Is2jY/ZImEJUHTv94oenX3y+eX5MkbkLkSfTTeTpbD6bzfcPEJnKgcgfizxnn+lE GKvVVoR1iKkPQgFGX33esf/nK4+dHMwnR/PDQzp2ckjHXsgbwa64Si3fDnN8fILV +/PDI4a109nedO9VJ+PPOcu02nWosHSCtSpDMo6rjLlS4KGw1KKYnbtd6x8themY 5bVgib4dsZV05RCKs6zlVaK1Y6lWuSxaw/3ufsGH0uCExofZX3F8wTJndFUJg+if nZyfsZorXogaNGBW586vIjQ1R862NWIR+f0ewcoQ5pSnJUjCrMNxougY2g3CsVwb 9qER4uHlmQ/xqVTtbbyYTXo4V6XoP2VGIi1WdywTOW8rx5xmmczBQQKy5BXqMGTR /ydjEYdysZ1NGnSwAxi7s0HMdjY4xY6HsoPCtli2iPAdK9YZhaM89Irg+tU5kkhs 1gP4VFArCiSY8PSGkLoSGYOAtVYZ79Cr3ZqIU2oEVHo1Yn07w360jSqEBVRBbW6g 01SMUD5ZVcy0SlExOTRsHbsrAZvoYYTvq3T3AoyTyuoRvWOdbokUMu88GbDTCJ6N tQLjfAwgtAJHlagmAuzS2Q8YO2clXy4iguj8QpGFhtoQx5UEkStEKSmijWkPFstU oFX2WTW0stDYU7dpyXKOMGbAgbpZxCG/AAiN/Zd6DTmp9Ip5R6DqGJEaYAF/Qi86 mwiV9pvtUEAFrWyyoxzyAFPaAYuHYRGbYIhdVPQGLaDNlDyISU15SR2sMRqH1Aw9 t7KWFTe+xOcOeWA/x98EIlhyvKJUKJSXb+lcM9/ba0otankbw2drgLVx2e75+uxF xwHWR3I0fLzUBvolflGeFSVsRFN1I9R2xT2NljwNsu17hLOBuG1wtM7xqSZ1WuiQ J7p1nn3zIfw77HPUlijdWq9JStdbhq6y0DX6ahG+r6rpaB04gsANbUyEWwnIXJEC ewH2O4DoB2LFaqmA34hKcIvmSOVzOYwn4JmReFLyLMDxJ2+E3AiDzMEp4judTkRA PytQVQnwDzCSATraSgc4ChwvooNDdhdeKTNSQ89Cvh2RBp0qBvzKb8MmHBOw0PfD KUt8yYgNVG7OElkwWYMDy+B6yEZ42H1+3jA1Qyjqh2JILcr83BxtKoniCuoouA2Q 6Ii6ucfi/fdC+6gquu0puo04aQtLeg5vzhlvGghBBhUgfypPLYvSUVVAWnJYDOqM JR2rdMphFwJzw3jkduRzg8AXkV1XVZPNcLKUvK2227zpyv+xQuYBDg43rNA4BLIh D3Ft1rHHl9sZxBHmUublwZ6cX53QOgmYpBniRKJhBBv2XPAu6S3R50dLGm2c9Ra7 iJ57iaITZsS88UXQO1GYKlrE7GFLrqrgVSSY0KaX0Pue9iaEb97CR6EvxOiOHvYj aRRhf2BuPJvhhdjaHLNrtM1x47xD07aPuZrMvErJFIKhIgWys3MGSug42ojwml5l HO5FDBKqQNe8Kqmjnt4P1uJeGzX5LxmnVEpnCTUTGq99ybqXRF13EsMKEZ4EhhJ5 ZSZCx8QtT13AsXZR8gySG93yMEcvuvPLk4tRcJMWl0OMLJx59pCqT4wh0y+07vGQ P8lgSUG1KxHUivm3iG7ahjJH0qc9s8ABTPLgsSyXG8cMqAbXFBqOFg+zVZti72c3 uCnuWfAaWcJC41Tlwy5cFtaXg9Dy/oASTfNEaDRMaVtdZLJ9Le9SAT1mvogGXwnv nobp9hTh0tZAEGnHFvtnbBLuINZbvrfRrZmXkA7HGeZVeQ+2EHjtWj+5pR1UmXkI oVsYI1bHG4bQ/N66orzcYDRTweD4OsnhVkNEJMa6labuOpC6RkNfOYiGC0Ln213l MXvsWUf6Qw6Wzo7aBowV2WiwSMCDGaKluBhBE34R+2lr3fq61Df+OlwXBA2kJZy9 CbQcnGZzHak1WEqFWLtkuMLzitYsItwF7I0IY2Ez3DyeYP3OC4+HkUmVvyKhkSJD XeE7GPat9yuiUUI30iBUxCk0FZfuw68smNFjxIobVUTEvMLwpqQQtcQ1zVVEJFyY KTF/ZVpbr+2vX2tRo0TRpQQdMU66URStfws8b9PnXYQSV07PcZ30j+PUP/7eFqAo Go8puWvMNJqtPih+UijZ0vC1ulqK4Qpjg61yyAi9+cWD19+4Q78Nh9+Vb7/2Rnfn yz+9uXjyz9/+58VHk39f/vXHt7/78kfNH+788eDNb774+nf+/K9fPvr2w6fXz35+ +63v/vd/ =L0K7 -----END PGP SIGNATURE----- --yrj/dFKFPuw6o+aM-- From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 13:31:09 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 672FB16A41A; Sun, 30 Dec 2007 13:31:09 +0000 (UTC) (envelope-from kris@FreeBSD.org) Received: from weak.local (freefall.freebsd.org [IPv6:2001:4f8:fff6::28]) by mx1.freebsd.org (Postfix) with ESMTP id 842F513C448; Sun, 30 Dec 2007 13:31:08 +0000 (UTC) (envelope-from kris@FreeBSD.org) Message-ID: <47779D9B.7050402@FreeBSD.org> Date: Sun, 30 Dec 2007 14:31:07 +0100 From: Kris Kennaway User-Agent: Thunderbird 2.0.0.9 (Macintosh/20071031) MIME-Version: 1.0 To: Philipp Wuensche References: <4755ED57.6030603@h3q.com> <20071204195131.56cb1307.wmoran@collaborativefusion.com> <4755FAD8.5030805@h3q.com> <4756BDC1.2070802@h3q.com> In-Reply-To: <4756BDC1.2070802@h3q.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cc: Adrian Chadd , "freebsd-performance@freebsd.org" Subject: Re: tuning for high connection rates 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:31:09 -0000 Philipp Wuensche wrote: > Adrian Chadd wrote: >> On 05/12/2007, Philipp Wuensche wrote: >> >>> As I understand it, and correct me if I'm wrong, polling helps against >>> high interrupt rates but for that intel gigabit cards have interrupt >>> moderation. We don't have a problem with interrupts (20% CPU) at the >>> moment but with system (100% CPU) as you can see in the system >>> monitoring graphs. Interrupts sometimes peak at, but usually are under, >>> the 2k interrupts/sec limit. >> Begin by reading up on the hardware profiling support (hwpmc, pmc, >> etc) and see if you can get some system and process-specific profiling >> information. > > Oh interesting stuff, I definitely have to take a look into that. Nice. Did you make progress on this? I'd like to see a profiling trace from hwpmc, and also from LOCK_PROFILING. Kris From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 13:35: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 06C8516A419 for ; Sun, 30 Dec 2007 13:35:59 +0000 (UTC) (envelope-from kris@FreeBSD.org) Received: from weak.local (freefall.freebsd.org [IPv6:2001:4f8:fff6::28]) by mx1.freebsd.org (Postfix) with ESMTP id 1AA7713C43E; Sun, 30 Dec 2007 13:35:57 +0000 (UTC) (envelope-from kris@FreeBSD.org) Message-ID: <47779EBC.5020900@FreeBSD.org> Date: Sun, 30 Dec 2007 14:35:56 +0100 From: Kris Kennaway User-Agent: Thunderbird 2.0.0.9 (Macintosh/20071031) MIME-Version: 1.0 To: Gergely CZUCZY References: <20071129101729.GA57985@harmless.hu> <20071130143023.I884@192.168.1.107> <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> In-Reply-To: <20071230132451.GA61295@harmless.hu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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:35:59 -0000 Gergely CZUCZY wrote: >> I appreciate that you might be constrained by local requirements, but it's really not meaningful to compare different mysql versions if >> your goal is to study OS performance. > It'd be a PITA to install the both versions. Maybe now, that the ports freeze is over, i can > do something. But honestly, every mysql version was faster on linux, than eny on FreeBSD, > even the .22 one was faster. We start work on Jan02, I will see what can I do. Regardless, we need a stable baseline to compare to. >> * What database engine are you using? I have only tested with innodb but maybe you are using myisam? Please provide your exact >> sysbench command lines. > MyISAM, of course. InnoDB is not any good with replication, and we need backup. OK. I tested briefly and InnoDB is almost 20 times faster on my tests with default myisam settings. myisam runs into the usual serious mysql scaling problems at concurrency > 8 threads (contention within the mysql application, not a FreeBSD issue). Still waiting for your sysbench command lines :) >> * Compare to my config file here: >> >> http://people.freebsd.org/~kris/scaling/my.cnf >> >> The default mysql config has very poor performance for innodb (you need at least innodb_thread_concurrency = 0 to disable some mysql >> brain-death). Maybe tuning is required for myisam also. >> >> * Also make sure you are using identical config settings on the two systems. > http://phoemix.harmless.hu/mysql/ verify yourself. Only the paths are > updated, but the main parameters are just the same. OK, I will take your word for it. >> * With the above, please compare read-only mode also (I think Jeff already asked you about this but I didnt see a reply). That will >> allow us to calibrate what is going on. > http://phoemix.harmless.hu/mysql/ro-all.png > The graph is mistitled as Read-write, but it's a readonly test. OK. Kris 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-- From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 14:08:31 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 0FE0016A41A for ; Sun, 30 Dec 2007 14:08:31 +0000 (UTC) (envelope-from kris@FreeBSD.org) Received: from weak.local (freefall.freebsd.org [IPv6:2001:4f8:fff6::28]) by mx1.freebsd.org (Postfix) with ESMTP id 27F8013C45A; Sun, 30 Dec 2007 14:08:29 +0000 (UTC) (envelope-from kris@FreeBSD.org) Message-ID: <4777A65C.8020406@FreeBSD.org> Date: Sun, 30 Dec 2007 15:08:28 +0100 From: Kris Kennaway User-Agent: Thunderbird 2.0.0.9 (Macintosh/20071031) MIME-Version: 1.0 To: Gergely CZUCZY 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> <20071230134354.GA63555@harmless.hu> In-Reply-To: <20071230134354.GA63555@harmless.hu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 14:08:31 -0000 Gergely CZUCZY wrote: > On Sun, Dec 30, 2007 at 02:35:56PM +0100, Kris Kennaway wrote: >> Gergely CZUCZY wrote: >> >>>> I appreciate that you might be constrained by local requirements, but it's really not meaningful to compare different mysql versions >>>> if your goal is to study OS performance. >>> It'd be a PITA to install the both versions. Maybe now, that the ports freeze is over, i can >>> do something. But honestly, every mysql version was faster on linux, than eny on FreeBSD, >>> even the .22 one was faster. We start work on Jan02, I will see what can I do. >> Regardless, we need a stable baseline to compare to. > Cannot do it sooner then january. Any recommended versions? Either 5.0.45 on both, or 5.0.51 on both, please. >> Still waiting for your sysbench command lines :) > There were 2 scripts and a sources file with options: > > basicall that's everything. I know it's a but complex, but this was all behind it. OK, that was very important since you're changing defaults. It looks like myisam is doing huge numbers of concurrent reads of the same file which is running into exclusive locking in the kernel (vnode interlock and lockbuilder mtxpool). Does it not do any caching of the data in userspace but relies on querying into the kernel every time? innodb doesn't have this behaviour. Kris From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 14:11:22 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 C570116A417; Sun, 30 Dec 2007 14:11:22 +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 487A113C457; Sun, 30 Dec 2007 14:11:22 +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 9715F7BFF28; Sun, 30 Dec 2007 15:11:20 +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 Wl3B3D2yIXA4; Sun, 30 Dec 2007 15:11:20 +0100 (CET) Received: from marvin.harmless.hu (localhost [127.0.0.1]) by marvin.harmless.hu (Postfix) with ESMTP id 2CAFC7BFCD5; Sun, 30 Dec 2007 15:11:18 +0100 (CET) Date: Sun, 30 Dec 2007 15:11:18 +0100 From: Gergely CZUCZY To: Kris Kennaway Message-ID: <20071230141118.GA67574@harmless.hu> References: <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> <20071230134354.GA63555@harmless.hu> <4777A65C.8020406@FreeBSD.org> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=x-unknown; protocol="application/pgp-signature"; boundary="azLHFNyN32YCQGCU" Content-Disposition: inline In-Reply-To: <4777A65C.8020406@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 14:11:23 -0000 --azLHFNyN32YCQGCU Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Sun, Dec 30, 2007 at 03:08:28PM +0100, Kris Kennaway wrote: > Gergely CZUCZY wrote: > >On Sun, Dec 30, 2007 at 02:35:56PM +0100, Kris Kennaway wrote: > >>Gergely CZUCZY wrote: > >> > >>>>I appreciate that you might be constrained by local requirements, but= it's really not meaningful to compare different mysql=20 > >>>>versions if your goal is to study OS performance. > >>>It'd be a PITA to install the both versions. Maybe now, that the ports= freeze is over, i can > >>>do something. But honestly, every mysql version was faster on linux, t= han eny on FreeBSD, > >>>even the .22 one was faster. We start work on Jan02, I will see what c= an I do. > >>Regardless, we need a stable baseline to compare to. > >Cannot do it sooner then january. Any recommended versions? >=20 > Either 5.0.45 on both, or 5.0.51 on both, please. Ack. > >>Still waiting for your sysbench command lines :) > >There were 2 scripts and a sources file with options: > >basicall that's everything. I know it's a but complex, but this was all = behind it. >=20 > OK, that was very important since you're changing defaults. For different configurations, yes. But every test is heterogenous. >=20 > It looks like myisam is doing huge numbers of concurrent reads of the sam= e file which is running into exclusive locking in the kernel=20 > (vnode interlock and lockbuilder mtxpool). Does it not do any caching of= the data in userspace but relies on querying into the kernel=20 > every time? innodb doesn't have this behaviour. Sorry, but was this a rethorical kind of question, or was this addressed to= me? :) If the later, then how do I find this out? Sincerely, Gergely Czuczy mailto: gergely.czuczy@harmless.hu --=20 Weenies test. Geniuses solve problems that arise. --azLHFNyN32YCQGCU Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4 (FreeBSD) owGFVU1vG1UUDa0AaRCLrpCgi7tBoao9OE7dGiM7bZO2mKhqRYIqUjbPM9f2wzPv Td57E2e6Y9cFqoAlSGXBAjaID7FH/AGW/Ad+BZw74yQVUtWNlby5H+fcc+57X75+ fu3chb9++f3h5S++/valn179fHI5L0Mws3au3JE27Y1OZ6N99Vrv2pX21fbmZpqy 6vevbL7X3ex1O3eWP7yybU1gE9r7VcEDCnwc3i0ypc37lMyV8xyGZZi2+9FJ3I72 hfU6aGsGpE2mDZ9+23fK+Cm79i2T2FSb2YAOSxs4bRdOm6AmGUfRPUN7pWnRDie0 2WlRt9O5RipQZ3PQ6Q+6/ft36XIHsFu067SnXTZGLVVFS4dKg2hEd9jNOKto++Dj 7YNPzs5Hz63cHWz2Br2rL6w8Gj239qj+GY3GpIrCcaJVYApzVK9sSbmezQNNmBJr fHAYH6c0qSizicrI8WGpHecYkW/RpAzDiHRY9/igMjQzNlDOymBg0zKjYFEmL5Rj SvUU40Qe5ZU/zIbdzgrGETsPBTzpqQBwNLNoBFJI9qFMK7q3RwW7qXW5MgnHTd44 rKcCU9H98f4NCdbACxCgwjSxYU4nlWO6qyqEGrtsNUQlpLAueMCfOuZHLA0tElqk KVGm6ZECgc05zEEnpptloLk17ENWtYgRXDVcThrRUnmaKh/YEf6Dn8pjNBxGc2WI TSWHt9Ht5t5Oq2mAIqYGE3e7+MrPVIjpAYO/coGW1i0k90NlOt0WjWmpQdMzwoVM MoxQf0ypbUbzEc+USzP2UGgJ1gwBlZSCZ2miPIvRn5UmNInbyoh8IK0DeAOOE2yG PlOmVK6K6QY4wDA2h/4pqp4MeCsaNXre0khw1Is78ZWeQBYdWmSbo97G2VGRMZDE 0Y1kEUc17L0grJYK+2hmBLUbN/jKT9gkc0EL/VMZK3saXJKkfXTDFOSnSz5xugie JAh8kZwgcKrBeglcZAvZdF/vAMagk8YsSuxbq7nSeUwLOKWxtRKP14PK+Lg2PDJg FdFJ0ofRhJGVIjo+GcK93ZXLJKh2ic7FbAre9xoOFmLrgIxbycyEbMpTVWbBx9Ft 0D7bFKzgVM9Kp2rgLarYNzZszBdgRfHtnOEXO2NjS3+KYhywsnbhMa8Fw6faq1yC Uysd5+UMzijzCRQkO5VOSenqptjktD4TXyJpGPFqiHMNGVDClUYWHAsHE/FxkpVe H7HcEIvmuE5dsDO82vN3joxNWRLYSVitkfwxKXWWwjF5OC6szS7FRDsWqsGBKy8q eC5RiUgDUNhXqZ2qoKRP6YG/UBipKOPgbOTCY4cl5nMK8f9oVtPTOW8hAMgmaMTe rGO/1RE3CkNXdaRhojjas85Vjfgiaf1ZoVuYWycuooU4ABNDWy9S1Y4/C01Th2XE vgQL/NIV7h03A85w+bpWs2dzuA6Mxxg36tW5tgxbUbQnrgG7qhVFp9f6ozJ5VEW5 0lmwA5o1x3FSH1/He5fLDRDPyyhqt4X3A2Yj4xHXxHh5jMb0PPYkA+XCWdwOuW+M q/CmYDcfb51/eU0e1pNX+cK53SdrT/vfP/7g4ZPe6NO//7l08OebG9/c/PHXte/s 9d+2X/v57aeji/2D+Ks/3li7+Na//wE= =kTBy -----END PGP SIGNATURE----- --azLHFNyN32YCQGCU-- From owner-freebsd-performance@FreeBSD.ORG Sun Dec 30 14:30:55 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 466CF16A421 for ; Sun, 30 Dec 2007 14:30:55 +0000 (UTC) (envelope-from kris@FreeBSD.org) Received: from weak.local (freefall.freebsd.org [IPv6:2001:4f8:fff6::28]) by mx1.freebsd.org (Postfix) with ESMTP id 90D6713C465; Sun, 30 Dec 2007 14:30:53 +0000 (UTC) (envelope-from kris@FreeBSD.org) Message-ID: <4777AB9C.1010003@FreeBSD.org> Date: Sun, 30 Dec 2007 15:30:52 +0100 From: Kris Kennaway User-Agent: Thunderbird 2.0.0.9 (Macintosh/20071031) MIME-Version: 1.0 To: Gergely CZUCZY References: <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> <20071230134354.GA63555@harmless.hu> <4777A65C.8020406@FreeBSD.org> <20071230141118.GA67574@harmless.hu> In-Reply-To: <20071230141118.GA67574@harmless.hu> Content-Type: text/plain; charset=windows-1250; format=flowed Content-Transfer-Encoding: 7bit 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 14:30:55 -0000 Gergely CZUCZY wrote: >>>> Still waiting for your sysbench command lines :) >>> There were 2 scripts and a sources file with options: >>> basicall that's everything. I know it's a but complex, but this was all behind it. >> OK, that was very important since you're changing defaults. > For different configurations, yes. But every test is heterogenous. I meant important from the point of view of others being able to repeat your tests. >> It looks like myisam is doing huge numbers of concurrent reads of the same file which is running into exclusive locking in the kernel >> (vnode interlock and lockbuilder mtxpool). Does it not do any caching of the data in userspace but relies on querying into the kernel >> every time? innodb doesn't have this behaviour. > Sorry, but was this a rethorical kind of question, or was this addressed to me? :) > If the later, then how do I find this out? It's a general question. It looks like myisam either has a design deficiency in this regard or it has poor defaults. If it can be made to improve caching of the data in userland then performance should improve. Kris From owner-freebsd-performance@FreeBSD.ORG Mon Dec 31 16:46:29 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 66EF016A419 for ; Mon, 31 Dec 2007 16:46:29 +0000 (UTC) (envelope-from gofp-freebsd-performance@m.gmane.org) Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by mx1.freebsd.org (Postfix) with ESMTP id DD8B413C455 for ; Mon, 31 Dec 2007 16:46:28 +0000 (UTC) (envelope-from gofp-freebsd-performance@m.gmane.org) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1J9Nm6-0005sW-Ed for freebsd-performance@freebsd.org; Mon, 31 Dec 2007 16:46:02 +0000 Received: from 78-1-126-5.adsl.net.t-com.hr ([78.1.126.5]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 31 Dec 2007 16:46:02 +0000 Received: from ivoras by 78-1-126-5.adsl.net.t-com.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 31 Dec 2007 16:46:02 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: freebsd-performance@freebsd.org From: Ivan Voras Date: Mon, 31 Dec 2007 17:45:39 +0100 Lines: 129 Message-ID: References: <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> <20071230134354.GA63555@harmless.hu> <4777A65C.8020406@FreeBSD.org> <20071230141118.GA67574@harmless.hu> <4777AB9C.1010003@FreeBSD.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig3C60F07D8DE43864D2650B2A" X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: 78-1-126-5.adsl.net.t-com.hr User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) In-Reply-To: <4777AB9C.1010003@FreeBSD.org> X-Enigmail-Version: 0.95.5 Sender: news 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: Mon, 31 Dec 2007 16:46:29 -0000 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig3C60F07D8DE43864D2650B2A Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Kris Kennaway wrote: > Gergely CZUCZY wrote: >>> It looks like myisam is doing huge numbers of concurrent reads of the= >>> same file which is running into exclusive locking in the kernel >>> (vnode interlock and lockbuilder mtxpool). Does it not do any >>> caching of the data in userspace but relies on querying into the >>> kernel every time? innodb doesn't have this behaviour. >> Sorry, but was this a rethorical kind of question, or was this >> addressed to me? :) >> If the later, then how do I find this out? >=20 > It's a general question. It looks like myisam either has a design > deficiency in this regard or it has poor defaults. If it can be made t= o > improve caching of the data in userland then performance should improve= =2E Isn't this common for software developed for Linux? I mean assuming syscalls are cheap; for example: gettimeofday(2), settitle(2), etc. I don't think the applications should be blamed for relying on performance optimizations not present in FreeBSD. Saying applications must do their own caching instead of relying on the kernel and need to avoid concurrent accesses to the same file seems like a doctrine from the dark ages. Does anyone have a theory why syscalls are so expensive in FreeBSD? Here are the results of unixbench 4.1 on two machines. First is the machine running FreeBSD HEAD (debugging disabled) on a dual-core Athlon 64 (i386 mode), 2 GHz: INDEX VALUES TEST BASELINE RESULT INDE= X Dhrystone 2 using register variables 116700.0 6467105.1 554.= 2 Double-Precision Whetstone 55.0 1633.7 297.= 0 Execl Throughput 43.0 2030.9 472.= 3 File Copy 1024 bufsize 2000 maxblocks 3960.0 63783.0 161.= 1 File Copy 256 bufsize 500 maxblocks 1655.0 57489.0 347.= 4 File Copy 4096 bufsize 8000 maxblocks 5800.0 53476.0 92.= 2 Pipe Throughput 12440.0 930715.9 748.= 2 Pipe-based Context Switching 4000.0 204248.8 510.= 6 Process Creation 126.0 5373.3 426.= 5 Shell Scripts (8 concurrent) 6.0 563.7 939.= 5 System Call Overhead 15000.0 720641.0 480.= 4 =3D=3D=3D= =3D=3D=3D=3D=3D=3D FINAL SCORE 387.= 4 The second result is from a machine running Linux 2.6.22, dual-core Core 2 Duo, 1.7 GHz, T2250, i386 mode (i.e. slower than the first machine): INDEX VALUES TEST BASELINE RESULT INDE= X Dhrystone 2 using register variables 116700.0 5576224.9 477.= 8 Double-Precision Whetstone 55.0 1521.6 276.= 7 Execl Throughput 43.0 2734.1 635.= 8 File Copy 1024 bufsize 2000 maxblocks 3960.0 316794.0 800.= 0 File Copy 256 bufsize 500 maxblocks 1655.0 97964.0 591.= 9 File Copy 4096 bufsize 8000 maxblocks 5800.0 665840.0 1148.= 0 Pipe Throughput 12440.0 679339.1 546.= 1 Process Creation 126.0 11562.9 917.= 7 Shell Scripts (8 concurrent) 6.0 316.0 526.= 7 System Call Overhead 15000.0 1134690.6 756.= 5 =3D=3D=3D= =3D=3D=3D=3D=3D=3D FINAL SCORE 625.= 2 Both runs are from the same sources, and the compiler is the same on both systems (4.2.1). I think the only possibly significant difference is that the Linux machine runs a NOHZ kernel (it's a laptop). This is almost comparing apples to oranges, but the difference in syscall-intensive operations (execl, syscall, process creation) seems anomalous, since everything should go against the second machine. I don't think this is a matter of Intel vs AMD, since benchmarks on comparable Xeons 51xx give similar results on FreeBSD. --------------enig3C60F07D8DE43864D2650B2A Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHeRy5ldnAQVacBcgRAkEiAKCMvtbn4fUjHqYF38PtjVhN5g60fQCgv1M7 SDgyskKPu7+Ti2MXjXGJ1Pg= =x5bZ -----END PGP SIGNATURE----- --------------enig3C60F07D8DE43864D2650B2A--