Unfortunately, FreeBSD is not the greatest for running a database server on
an SMP architecture... Not yet, anyway. Hopefully 5.1-R or 5.2-R will fix
that. And by the way, IMO, stay away from 5.0, it's not ready for prime
time yet. My testing showed (for our application, anyway) that 4.7 and 4.8
with LinuxThreads was most excellent.
I've succeeded in getting MySQL built against LinuxThreads on FreeBSD, with
the gracious help of Jeremy Zawodny and his build statements. There's an
entry in his blog (http://jeremy.zawodny.com/blog/archives/000458.html) that
is a good starting place to make this work. I can forward my personal
configure statement to you, if you like.
Another question... You say that you're running a RAID10 array. How many
disks? A 2x2 array is decent, but a 4x2 will run much faster. What's your
stripe set size - 64k? 128k? 256k? a meg? Can you afford the risk of going
to a straight RAID0 of all your disks?
Another person noted that you're doing a lot of "select *"'s, do you really
need to grab all the columns all the time?
----- Original Message -----
From: "Ryan R. Tharp" <mysql@stripped>
To: "Richard Clarke" <ric@stripped>
Sent: Thursday, April 24, 2003 7:31 PM
Subject: Re: performance
> No I'm not sure. Infact I believe the default for FreeBSD 4.8 is pthreads.
> So I'm pretty sure I'm using pthreads (how do I check?). I heard
> linuxthreads on freebsd had problems with mysql (though that tid bit might
> be out-dated?).
> ----- Original Message -----
> From: "Richard Clarke" <ric@stripped>
> To: "Ryan R. Tharp" <mysql@stripped>
> Sent: Thursday, April 24, 2003 4:23 PM
> Subject: Re: performance
> > Are you sure you have mysql compiled against linuxthreads?
> > Ric
> > ----- Original Message -----
> > From: "Ryan R. Tharp" <mysql@stripped>
> > To: <mysql@stripped>
> > Sent: Friday, April 25, 2003 12:11 AM
> > Subject: performance
> > Running MySQL 4.0.12 on a dual Xeon 2.8ghz with 2gb memory running
> 4.8 on a Hardware RAID 0+1 SCSI HDDs
> > Have this table that I'm trying to optimize for. It has 1.1million rows
> with 29 fields averaging 506 Bytes per row. We've considered
> > splitting the table but we don't have enough developer time available to
> convert all the code that uses the table. So we'd like just
> > to throw hardware/memory at it for the time being.
> > Some software tricks I've tried are playing with:
> > Dynamic sized rows and static sized rows
> > InnoDB and MyISAM
> > Indexes and No Indexes
> > Primary Keys and No Primary Key (Just an Index for the auto-increment)
> > I wrote a benchmark that tests most of these scenarios and then I began
> adjust the various mysql buffers but to my amazement the
> > benchmarks ran best when I didn't set any buffers in the my.cnf at all.
> > Also the fastest benchmark numbers aren't that fast. Is this table too
> big? Any other optimization tips to try? Any one have any
> > ideas or suggestions?
> > -Ryan.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1