DB server config (was Re: load testing and tuning a 4GB RAM server)

[Ralph Forsythe]

At 01:47 PM 4/6/2003 -0400, Chuck Swiger wrote:

>Also, your configuration is suited for where the users will be doing 
>mostly reads and not a lot of writes.  If the users might be doing a lot 
>of writes, you should use RAID-1,0 instead of RAID-5.

.....

>Specificly, disk I/O contention is likely to happen if the DB is expected 
>to see any significant usage while the drives are busy serving up user 
>files.  That's aside from the fact that you really don't want to keep 
>database files on RAID-5 storage in the first place, either-- keep the DB 
>on RAID-1 or RAID-1,0 if you can: use some of that 15K swapspace if you 
>need to.

Well, this brings up some interesting points WRT a project I'm working on 
right now.  As-is, the system will bring in about 2-4 gigs a day in raw 
data, which will get parsed out and stored in a database with various other 
information as to how it's sorted, etc.  From there, a PHP site will access 
the database, figure out the sorting information, and present it to the 
user on request.  Pretty simple so far...

However in about a year, I expect the daily data input to exceed 35 gigs, 
with that extra data representing a lot of different things, but being 
presented to the user with basically the same interface.  So...

Writing to the database is probably best suited for Raid 0/1, but reading 
from it is best done on Raid 5, correct?  The database will be MySQL, DB 
server will be FreeBSD.  The system which takes in the data will be Linux 
(not my choice, it's a software issue) which will then push to the database 
over a dedicated 100mbit interface.

Then there is the web server ... this will be FreeBSD as well, running 
Apache with PHP.  Let's assume the web server and DB server are not having 
issues with RAM, have been tuned, etc.  All web pages will be dynamic with 
PHP (all content is determined from the database), except for the style 
sheets and a few static graphics like logos and such.  Initially the load 
will be low, but in peak times down the road I expect connections in the 
thousands potentially.  There will be SSL on some parts, but it will be a 
tiny fraction of the normal traffic (i.e. commerce and registration stuff 
only).

So what would be a better drive configuration for a system like this?  My 
instinct is telling me a Raid0/1, since the majority of the traffic will be 
writes with the incoming data.  A lot of that data might not be used for a 
given time period, but the idea is that it's all available 24/7, and 
archived for a period of time (yes I know, this also means a ridiculous 
amount of disk space, at least half a terabyte just for 2 weeks).  The data 
pruning activities to remove old information could involve a lot of reads, 
but if I index it properly I think that will be minimal and will be spread out.

I just had a thought about a master R/W DB server with Raid0/1 and a 
read-only slave on Raid5 that replicates for web access, but I'm not sure 
I'm gaining anything.  The slave will still have to push just as much data 
to the drives, though replication might have benefits over normal database 
writes I'm not aware of...  (MySQL tuning perhaps?)

Anyone have any suggestions or expertise to share here?  How would YOU 
build something like this?

Thanks!
-Ralph