[ale] Building a Linux/Mysql Database server.

[LinuxGnome]

On 05/24/2011 12:26 AM, Atlanta Geek wrote:
> I wasnt very clear.  I actually need to define the specs for a mysql
> database server.
>
> Our current server is a Quad Core ~2GHz 4 Gig 2 15k drive (no raid)
> configuration. And It seems to be I/O bound on a lot of operations.
> The database  as is goes to around 45 Gig.
>
> This new server needs to be able to handle our same database structure
> with about 600 Gig of data.  There's a lot of writing and reading of
> data.
>
> Any suggestions on hardware.  Can Mysql deal with 32 Gig or is this a
> waste of resources because its not enough to cache.  Would we be
> better off looking at SSDs .
>
> Any guidance would be appreciated.
>

If this is a product you will sell, or a system your company will rely on for their business, then seriously, hire an experienced and seasoned MySQL DBA.

Tune your SQL statements.  Fixing a slow query and/or optimizing (or eliminating) joins is much easier, efficient and cheaper than throwing new hardware at it.

Tune your MySQL config.  There are many buffer and cache settings which, in their default settings, are not optimal for anything bigger than a demo db.  Some tuning parameters will depend upon which MySQL data engine you use. (ISAM vs Innodb, etc)

For disk I/O problems that are not fixed by tuning your SQL, you probably need to add more spindles to increase your iops, and divide your logs from your data.  You should be running RAID on the box, if you're not SAN connected, but beware the type of RAID will effect performance.
I would go with many 15k drives for the data (for example, if you have a 500G DB, then 9x 73G drives in RAID5 will you 6~8 times the performance of one 600G drive that is IO bound).  You might consider a ramdrive (i.e. FlashCache, not SDD) for writing your logs to.

You can always throw more RAM into a box.  You might consider switching to an in-memory database engine if the db is small enough, and/or your hardware is robust enough.  These are usually constrained by your budget.

Lost in America,
--LnxGnome