From owner-freebsd-database@FreeBSD.ORG Sun Jul 24 19:01:00 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 6B49B16A41F for ; Sun, 24 Jul 2005 19:01:00 +0000 (GMT) (envelope-from mike@daboyz.org) Received: from jorm.daboyz.org (jorm.daboyz.org [209.67.213.18]) by mx1.FreeBSD.org (Postfix) with ESMTP id 27B4C43D48 for ; Sun, 24 Jul 2005 19:01:00 +0000 (GMT) (envelope-from mike@daboyz.org) Received: by jorm.daboyz.org (Postfix, from userid 10001) id 1AD3314A6B; Sun, 24 Jul 2005 14:12:48 -0500 (CDT) Date: Sun, 24 Jul 2005 14:12:48 -0500 From: Michael Barrett To: David Miller Message-ID: <20050724191247.GC60734@daboyz.org> Mail-Followup-To: David Miller , Michael Barrett , freebsd-database@freebsd.org References: <20050719063309.GA63017@daboyz.org> <42DCF948.3020404@sparks.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42DCF948.3020404@sparks.net> User-Agent: Mutt/1.4.2.1i 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: Sun, 24 Jul 2005 19:01:00 -0000 Just a quick update- it turns out the problems we're running into don't seem to be limited to FreeBSD. We've installed the same database on Red Hat Enterprise as well, and we're seeing the same sort of performance. It's starting to look more and more like it's a function of the dataset and MySQL itself. Thanks for your help David. On Tue, Jul 19, 2005 at 08:59:52AM -0400, David Miller wrote: > 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 -- ________________________________________________________________________ Mike Barrett | "We have veggie bacon, why don't we have mike@daboyz.org | meat fruit?" www.daboyz.org | -- My ex-coworker, Ben ------------------------+----------------------------------------------- From owner-freebsd-database@FreeBSD.ORG Tue Jul 26 19:35:45 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 7F99716A41F for ; Tue, 26 Jul 2005 19:35:45 +0000 (GMT) (envelope-from decibel@decibel.org) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by mx1.FreeBSD.org (Postfix) with ESMTP id F0EFF43D46 for ; Tue, 26 Jul 2005 19:35:44 +0000 (GMT) (envelope-from decibel@decibel.org) Received: by flake.decibel.org (Postfix, from userid 1001) id 3A9B415234; Tue, 26 Jul 2005 14:35:42 -0500 (CDT) Date: Tue, 26 Jul 2005 14:35:42 -0500 From: "Jim C. Nasby" To: David Miller , Michael Barrett , freebsd-database@freebsd.org Message-ID: <20050726193542.GV29346@decibel.org> References: <20050719063309.GA63017@daboyz.org> <42DCF948.3020404@sparks.net> <20050724191247.GC60734@daboyz.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050724191247.GC60734@daboyz.org> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i Cc: 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, 26 Jul 2005 19:35:45 -0000 FWIW, I've heard from people who've had major problems getting mysql to scale much past that point. Depending on how complicated the application is you might try testing on something like postgresql (though you'll have to tune the out-of-the-box config). On Sun, Jul 24, 2005 at 02:12:48PM -0500, Michael Barrett wrote: > Just a quick update- it turns out the problems we're running into don't seem to be limited to FreeBSD. We've installed the same database on Red Hat Enterprise as well, and we're seeing the same sort of performance. It's starting to look more and more like it's a function of the dataset and MySQL itself. > > Thanks for your help David. > > On Tue, Jul 19, 2005 at 08:59:52AM -0400, David Miller wrote: > > 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 > > -- > ________________________________________________________________________ > Mike Barrett | "We have veggie bacon, why don't we have > mike@daboyz.org | meat fruit?" > www.daboyz.org | -- My ex-coworker, Ben > ------------------------+----------------------------------------------- > _______________________________________________ > freebsd-database@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-database > To unsubscribe, send any mail to "freebsd-database-unsubscribe@freebsd.org" > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"