Ok, my server guys are telling me that mysql is hitting loads of 25, which he says is very high and i don't quite know what it means. He is strongly recommending that i shld get another dedicated server for mysql alone. So can i get some advice on how I can assess the validity of his recommendations? I see that my CPU is load is hitting 125-150% daily. So i suppose there is some truth? He is recommending that i get a raid-5 which is best. How can i know a suitable configuration of a new server to get? Of course, if i got all the money in the world i shld buy the best. But with constraints on budget, i want to get one (if i really need a new server) that is good for current loads with some projected margins for growth. Would really appreciate some urgent help from the experts here as my site is crashing quite often. Tks!
What is your current server configuration? When your server is busy, what is MySQL doing? This can be checked with 'show processlist' within the mysql command line client. Have you tried tuning MySQL? MySQL Performance Tuning Primer Script is a good place to start. When the server is busy, what is the server waiting on? Capture the output of 'vmstat 3 10' (runs for ~30 seconds). Specifically we need to see if you are swapping, or waiting on disk-io.
Are you using VPS or Dedi? If you are using Shared Hosting I think they should have kick you for using too much CPU load. I have 14k ++ unique perday 300-600 user online without a problem on VPS...
Try to find what mysql is doing so hard and replace the statements or do somenthing about that. Search about mysql optimizing and you can do a lot.
I would first make sure that all of the queries, the database configuration, and the database structure is well tuned. If you are using well established commercial or open source software, then you can probably assume that this is already done, at least for structure and query optimization. You should however, definitely check your server configuration and see if there are any problems there. If you do decide that you are going to need a dedicated database server, you should use RAID 10 and not 5 if possible. RAID 10 is far superior to 5 for databases. Also, you may want to look into colocation hosting if your current host is really expensive for an additional server. In any case, make absolutely sure that your database server is directly connected to the web server, ideally without even a switch, over a 10/1000 connection. With load like you are experiencing, you would be asking for problems to try to connect with anything less than a gig connection.
Get a host with free private bandwidth preferably with gigabit connection. Raid 10 is also great. the purpose of raid is to have a redundant setup in case of failure, but it multiplies the number of i/o available or reading and writing on drive. One cheaper solution is to add another hardrive to your current server, possibly a sas or even ssd, and use it only for mysql. This drive will provide the i/o and speed for mysql, while the other drives will be for script. This way you double your i/o and speed. However, this is a cheap solution as the second harddrive will need to be protected with constant backup, offsite if possible.