Here’s the scenario. You’ve got a dedicated server running windows in a server farm (generic dedicated server provider). You have mysql + asp or php app running all on the same server. Raid 1 mirroring and 2 GB of ram.
You have to have windows, because your application supports it. Sound Familiar? It’s a problem which is a big issue with many of you out there… The ASP app is super slow. The server barely keeps up and you’re lucky if you have even 200 MB to spare of physical RAM.
So you have a few solutions:
1. Get a bigger server
2. Move Mysql off to another server.
3. Add more RAM (but still this doesn’t solve your i/o problem.
So here are my suggestions:
1. Go to Amazon and run multiple servers with the mysql instance on it’s own NON-Windows machine.
2. Add more RAM
3. Add more drives and set up one of two configurations: RAID-1+0 (4 drives, not to be confused with RAID 0+1) or RAID-5 on 3-4 spindles (drives).
4. Add a High Speed USB 3.0 USB stick and use that as extra RAM. (Not the most ideal solution, but it’ll help immensely)
5. Get a better server dual *Xeon with VT extensions + 32+GB RAM + 3 or more spindles, run xen on it with 2 virtual hosts. one for windows and the other running linux with Mysqld (My personal favorite is centos, but then again, it’s my choice).
The 5th solution is the best solution considering you’ll have complete access to the extra ram and VERY low latency between Mysql and the windows instance (they’re running on the same machine and are effectively communicating over the backplane)
Windows Web (which many dedicated providers use) Allow Max RAM allocation at 2GB. Pretty much useless. Especially if you’re running a “server”
add mysql to that and some pretty hefty tables and you might as well shut off your business, because it’ll be a dog.
Run Xen and have windows on one instance + Linux (Mysql) on the other.
Give the windows instance 4-6GB and Mysql twice whatever the DB size is in RAM.
Give the Xen Host at least 8GB although whatever you can afford is better.
Tune the Mysql properly, and of course your SQL and watch the system scream.