I have a high volume vBulletin forum with over 11,000 registered users, and currently have a dedicated server which handles the traffic on most normal days with no issue. However, my forum is a football forum, and my problem is that whenever there is a game and traffic spikes, it overloads Apache and it goes down. Basically what I know is happening is my users start a game thread, and then they constantly refresh it to read responses. When 100's of people are doing that simultaneously the queries per second skyrocket, and has habitually been crashing it unless I have a support tech there restarting MYSQL each time the load peaks. I've set up a chat room and have been trying to get people to use that instead, but they still insist on using the forum for game discussion and it clearly can't deal with it. So my question is - will purchasing a separate dedicated server just for the DB using SCSI drives which would be housed in the same building and connected directly to the other server make a difference? Or will this same scenario (with people refreshing) just slow the two machines down? I've had this problem off and on for over a year and need to do something, so any thoughts from any of you who that deal would this is greatly appreciated.
Change PHP code to using mysql_pconnect() instead of mysql_connect() Set httpd.conf set line: Timeout 7
Splitting your db and web server will help out, without a doubt. Make sure your DB server has lots of ram, and, as you stated, ensure that there are two network cards in both machines, one for talking between each other, and one to connect to the outside world.
It should improve the performance of your site if your separate your database server from your web server. Also, make sure that you have optimized your Apache and MySQL.