From owner-freebsd-database@FreeBSD.ORG Tue Jul 19 12:59:54 2005 Return-Path: X-Original-To: freebsd-database@freebsd.org Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id A3A7616A41F for ; Tue, 19 Jul 2005 12:59:54 +0000 (GMT) (envelope-from dmiller@sparks.net) Received: from search.sparks.net (search.sparks.net [207.5.180.136]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5679943D45 for ; Tue, 19 Jul 2005 12:59:54 +0000 (GMT) (envelope-from dmiller@sparks.net) Received: from [10.0.0.10] (dsl-66-243-211-31.pivot.net [66.243.211.31]) by search.sparks.net (Postfix) with ESMTP id 13F7CA90C; Tue, 19 Jul 2005 08:59:53 -0400 (EDT) Message-ID: <42DCF948.3020404@sparks.net> Date: Tue, 19 Jul 2005 08:59:52 -0400 From: David Miller User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.7.5) Gecko/20041217 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Michael Barrett References: <20050719063309.GA63017@daboyz.org> In-Reply-To: <20050719063309.GA63017@daboyz.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cc: freebsd-database@freebsd.org Subject: Re: Big DB (100G+) w/ MySQL 4.1 and FreeBSD 4? X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 19 Jul 2005 12:59:54 -0000 Michael Barrett wrote: > Does anyone here have any experience with dealing with large MySQL 4.1 databases (over 100 gigs) on FreeBSD 4.x? I'm running into performance issues with a similar setup, and I wanted to compare notes with anyone else who may have tried this. Our current setup is: > > Dell 2650 w/ 2CPU's (533 FSB) and 3G of RAM > FreeBSD 4.11 running SMP > MySQL 4.1.12 using LinuxThreads > > Our tables are almost completely innodb using seperate files per table. The size of our mysql datadir is around 199 Gigs. > > We're seeing all sorts of weird performance issues. The biggest 'issue' we're seeing is that some queries take over a minute to return, yet the system itself (CPU, disks, etc) see almost no activity while this is going on. We had the MySQL AB folks out to take a look, and even running their pre-built binary (w/ LinuxThreads) from their website we saw the same issues. They agreed that 'something was wrong', but couldn't give us any quick ideas on what exactly we could do about it. > > If you do have experience with a system around the size of ours with similar setup, I'd love to hear about your experiences. Also, seeing any loader.conf, sysctl.conf, my.cnf, and general tips about kernel configuration would be awesome. > > Thanks for any help you can provide. > > In testing of earlier versions of FreeBSD (4.8 or so) and MySQL (4.04) I found that running with SMP actually slowed the system down. Simple database tasks actually took longer to finish with SMP enabled than with a single CPU. That might be worth trying. If your system doesn't do that - and I'm told that 5.4 doesn't - it may be worth throwing ktrace on the running mysql process to see what it's doing. Hope this helps, --- David