From owner-freebsd-performance@FreeBSD.ORG Fri Jan 4 19:42:33 2008 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 905DE16A46B for ; Fri, 4 Jan 2008 19:42: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 BA15E13C459; Fri, 4 Jan 2008 19:42:31 +0000 (UTC) (envelope-from kris@FreeBSD.org) Message-ID: <477E8C26.6050002@FreeBSD.org> Date: Fri, 04 Jan 2008 20:42:30 +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> <477D5927.4030100@FreeBSD.org> <20080104133028.GA89602@harmless.hu> <477E89F4.1080605@FreeBSD.org> <20080104193546.GA34364@harmless.hu> In-Reply-To: <20080104193546.GA34364@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: Fri, 04 Jan 2008 19:42:33 -0000 Gergely CZUCZY wrote: > On Fri, Jan 04, 2008 at 08:33:08PM +0100, Kris Kennaway wrote: >> Gergely CZUCZY wrote: >>> On Thu, Jan 03, 2008 at 10:52:39PM +0100, Kris Kennaway wrote: >>>> Gergely CZUCZY wrote: >>>> >>>>>> * 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. >>>> You are not in fact using the same config parameters as I am. When I use your config file I see a large performance loss when I run >>>> locally even with innodb. >>> So, the updated config is here: >>> http://phoemix.harmless.hu/mysql/my.cnf.kris >>> I've migrated the values from your config. >>> And the plot is here: >>> http://phoemix.harmless.hu/mysql/rw-kris.png >>> I"ve also plotted a current production system, which crashes around 96 threads in >>> the test, or so. That's a Linux-2.6.12 and MySQL 5.0.22, so it should be quite unoptimized >>> compared to any recent version of anything. >>> As it seems, you were right. These adjustments made a very slight performance boost, around >>> 5%. But it's still lagging behind. >>> Again, this is MyISAM, not innodb. The "tc4" and "tc16" strings mean the thread_concurrency >>> is either set to 4 or 16. Your config had 16, but I've googled for it, and some resources >>> suggested setting that to CPUs*2, and that's 4 in my case. >>> I've updated the MySQL version on FreeBSD to 5.0.51, and will try to get this version >>> on linux also, and do a comparision with that one, too. >>> Sincerely, >>> Gergely Czuczy >>> mailto: gergely.czuczy@harmless.hu >> OK. The caching problem with myisam is unrelated to the other config changes then. > > I have to finish my test, sorry for not being able to give more results. > The boss decided to put back this box to the serverfarm, and since we're > not using FreeBSD, just I'm a bsd-guy, I won't be able to do anything farther > on on this topic. > > I hope I did something useful with this tests of my at all. > > And thank you all for helping with all the ideas, especially to > you, Kris. Thanks for bringing it up. If anyone else is interested it would be good to figure out if there is a way to improve the myisam caching behaviour. It is likely that performance will also improve once the lockmgr changes are completed. Kris