I can answer that. I just stepped off that ride, will ride again.
$650-$800 will buy an 8 GB RAM moderate speed quad-core AMD running a 1TB SATA2 drive. $170 will buy a second relatively fast 1TB drive. Keep in mind that this is off the shelf hardware from most electronics/office store/best-buy type places. You can get more bang elsewhere, but not bad for the money. You can get a faster quad core for little more.
Now, regarding the app, I will assume you're running linux/BSD/unix for the OS and avoid the ms vs unix debate. Here's what I've found:
We have no problems specifying 200+ active users/sessions on any of our applications without blinking, no matter how weak the app is. In fact, we haven't been able to drop/crash/bog-down an application on any quad-core server we've run for some time.... but we learned some lessons back in the single-core 200mhz days.
For example, our sister company sells quite a few mysql-based communications monitoring systems with 1300+ users per machine and on average several hundred concurrent sessions per hour. Logging and reporting is done in real-time (well, some buffering occurs) and they've been running on 3Ghz dual-core machines on [gasp] slow PATA drives... Indeed, 133Mhz P-ata drives. Longest user interface lag ever was about 2 seconds. They dumped MSSQL for MySQL a decade ago and immediately got results.
Keep in mind, these machines are running webapps+databases.... so you do the math. It just works. Also, I've replaced a number of oracle/MS/xxxx applications with these, and never come close to running out of steam. Also, let me expand on what the other guy said from a dba perspective... Here are 6 tips from the trenches.
- Writes will kill performance, especially if locking penalty is a foreign concept to your coders.
- Doing everything in one massive table will kill you.
- Excessive Normalization is not your friend. If your coders are going full 3rd normal form, your app will run badly. Denormalized data takes more space, but makes it possible to accomplish great things with simple queries.
- One big table will choke on frequent writes. See 1.
- If you write your app to use one (or more) tables for data display, and a different table for writes which can be synced to the read tables when system loads permit, you can get away with all kinds of things. We use a small number of tables to buffer writes, and are able to cope with an amazing number of transactions because no one is getting stepped on.
- Use indexes. If you know what parts of the queries are used as keys, anyway.
- Tune the database installation based on memory. See the MySQL docs online.
Truthfully, if you have less than 1000 active sessions, you can often just bump the number of connections up and just go for it.
You can see stupid SQL in action by looking at most wordpress/etc. plugins. Most are written by folks who don't get sql, and they will bring a server to it's knees in no time with just a handful of users.