Big DB (100G+) w/ MySQL 4.1 and FreeBSD 4?

[Jim C. Nasby]

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 at daboyz.org |  meat fruit?"
>               www.daboyz.org |    -- My ex-coworker, Ben
>      ------------------------+-----------------------------------------------
> _______________________________________________
> freebsd-database at freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-database
> To unsubscribe, send any mail to "freebsd-database-unsubscribe at freebsd.org"
> 

-- 
Jim C. Nasby, Database Consultant               decibel at 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?"