So, me and a friend run a site based on British band the Arctic Monkeys. Today, we were featured on www.nme.com, a popular music magazine and website. Within an hour, our mysql server shut down. I had to keep manually restarting the server in putty to get it back up. Right now, it's down, and i cant even ssh into the site to restart anything. Kind of unfortunate seeing as today has been a good day for adsense So, after everything cools off at the site, i'd like to implement some sort of "system" which will cope under large amounts of traffic. I was considering using lighttpd instead of apache 2.2, which we're running now. note that i cant provide any more specific details regarding versions of software we're running as i cant access ANY page right now.
Sounds like your dynamic backend is what is screwing you up-- not Apache. While it is possible that Apache is too bloated because it is operating in forked mode to run PHP, the more likely culprit, as you say, is mySQL. The solution? Remove mySQL from the loop on your most trafficked pages. Turn your dynamic site into one that writes out flat PHP or HTML that does not require a backend. That is the simplest way to deal with floods of traffic if you don't have a budget to match your hardware with a dynamically driven site. I can't see what sort of software you're running, but it is possible also to only cache a flat version of your homepage.. serve that flat version to guests.. and that way you would cut down on probably half of the queries your database is requested to serve.
our site doesnt run entirely on mysql, it's just used for the forum, polls etc. the news on the main page is managed by cutenews, which is all written to a flat file (news.txt) and then a php include to the actual script which retrieves and displays the news. ive removed everything on index.php which requires mysql, which was only a poll and a random buttons script. the server seems to have moderately recovered. another problem appears to be our access_log file. i've never paid much attention to it until now, when i realised its close to a gig in size. i've read about log file rotation and stuff on the apache site but never really understood it fully, or really thought it would be that useful for our kind of site. would you also think that this large log file could be adding to cpu usage unnecessarily?
>>ive removed everything on index.php which requires mysql, which was only a poll and a random buttons script. the server seems to have moderately recovered. You did this now and it helped? Or you did this before and it didn't help enough? >>would you also think that this large log file could be adding to cpu usage unnecessarily? ABSOLUTELY. I'd dev null that sucker until you get a proper log rotation script in there. I'm not much of a command line scripter, I'd have tech support do one up.. though someone else here could probably help. It is likely if the log file gets to 2 gigs, the service will simply shut down. Another possibility to consider is to move your images off to a cheap image server that is nothing but disk speed and bandwidth. There you can run tux or some other stripped down server and take that load from the main box running forked Apache.
On another note...you might want to let adsense know about the feature in the paper and why your site got a spike in traffic. I have read elsewhere here on DP of stories of peoples accounts being suspended after spikes in traffic like this and then you have to go through the hassle of trying to get your account reactivated etc. Just a thought.
If not done already, install eAccelerator and run at least MySql 4.1 and turn on query caching. The two together can probably support 5-10x the traffic you're running now. if your forum is phpBB there is also a mod to cache common sql queries so they don't even hit your database, as well as a better template caching system. I ran 4 forums and 15 websites, doing this on my little VPS dropped the server load from an average of 1.0 to 0.15
I'm having this same problem. I've had to restart my VPS 3x already. The St. Patty's Day rush is hitting me hard.
ill have a look into eaccellerator. im going to leave lighttpd for the meantime as i dont really understand the installation. docs are a bit brief, maybe thats just me though. thanks for the help
Given that your problem is with MySQL I don't really think switching to lighttpd (good though it is) is going to solve anything. It'll require some code changes but you could look into memcached. If you're expecting to have this sort of load frequently it might be worth investing in another server to run the database backend, leaving Apache on the current server. Of course, this adds another point of failure too.