system requirements on separated systems -- apache/php & mysql

[aunquarra]

I've been developing with mySQL/php for a few years, and from a software standpoint, I'm comfortable. But I'm looking at building some servers for an application, and I'm wondering where I should invest the money. Specifically, I'll have a db-only server and the front-end web server.

 

I know that mySQL will love every byte of RAM I can give it, but in the past, I've only worked where I was connecting with localhost. So I'm not sure how much of that was utilized by the mySQL server and how much was utilized by the mySQL client inside php...

 

I also know that a system requirements question is hard to answer without at least some knowledge of how the db application is built. Well, it's all myisam tables, each with auto_increment primary keys which are used (and indexed) frequently in other tables for lots and lots and lots of left joins (simple two-table joins in most cases, but unfortunately I've had to do three table joins a couple times). As a rule, I try to keep things ridiculously efficient.

 

So, I guess my question is this: if I build a separate mySQL server and web server, what should be the hardware focus on each? If I understand everything correctly, I should get a couple great multi-core CPUs for the web server and a reasonable minimum of RAM, while getting a meager processor for the mySQL box and throwing every penny left in my budget at RAM for it. Is that about right?

[fenway]

Web server do a lot of disk I/O, not a lot of number crunching data set manipulation.

[aunquarra]

So I'd be better off buying high speed SCSI drives (instead of SATA) on the web server rather than high grade CPUs?

[fenway]

That would be my suggestion.