For as long as I can remember, I’ve seemingly had an uncanny ability to kill just about any computer I’ve touched. In college I crashed more than one system (at a time) composing music in Finale and Cakewalk. At home, I’ve killed my share of Windows systems by trying to do too many things at once. I’ve even endangered more than one Linux system, or would have if X didn’t crash before I was done with it. And to my surprise, today I found out that my site was beating the life out of my database.
If you’ve tried to hit the site recently, you may have noticed a “too many CPUs connecting” error pop up. Apparently, my server was complaining about too much traffic coming into the site. Frankly, I find that almost ridiculous, since I don’t normally get more than about 400 visitors per day. (Once in a while it’ll peak at 2,000+, but not often.) Nevertheless, it seems as if many of my WordPress plugins and addons have been hitting the database a bit too hard every time someone comes to visit. Here’s an example:
When someone pulls up a page, the server has to display all the code and images on the site. On top of that, the server has to query the database for the page’s content. Like most web pages today, my site relies heavily on database interaction, not just because of the main article, but also because of the website topics, the “most popular pages” list, the “recent entries” list, the “recent comments” list, the miniblog, and a few other little things I have running in the background. Add to that the fact that there are a few people checking the site via RSS, spiders crawling deep into the site, and that people actually visit the front page, so more than one article gets served up, and you have your equation for trouble.
In response to this, I’ll be making a few changes to the site, including possibly a complete theme change (even though I’ve loved TypoXP, I might return to regular old Kubrick), in order to optimize the website performance. I’ve started by eliminating all of the “most recent comments” lists from the site, since those seem to have been taking an especially heavy toll. I’ve also eliminated a few redundant features which, while small, were apparently affecting the server more heavily than I’d anticipated. If that’s not enough, I’ll then begin to alter the internal pages in order to lighten the load, since about 90% of all visitors enter via individual pages within the site and not the via the front page.
Anyway, if you anyone reading this has any experience with matters like this and can offer advice on the matter, I’m all ears. Frankly, if I can help someone else avoid these same mistakes, then by all means let’s do it.
As your former hosting service provider, I can tell you, that when you were on my server in Edmonton, I wrote a special script – to keep my server from outright collapsing. Remember the “server temporarily unavailable” messages. Basically what the script did, was take down apache and throw up a temporarily unavailable page (complete with the proper error code # so the search engines would understand) until the CPU load went back down to normal. That script kept a log of every time it kicked into effect. Since you left my humble server well, that script hasn’t logged one single event. (Apparently your new provider does something similar to prevent one site from collapsing the server).
.
I think the problem has to do with your flattened URLS. (URL rewrites). Then again I also have a wordpress blog that gets a fair amount of traffic, and it has never blown up the server.
.
The answer to your problem is a complex one. I can tell you that for sure it’s releated to MySQL. What you would need to do, is log MySQL queries or use phpMyAdmin or Mysql’s admin tools to tell you what queries are causing the problem (queries that take a long time to execute). Then locate what php script is making that query. To fix the problem – either stop using the php script (if it’s a module turn it off), or rewrite the query so it’s more efficent, or fix the database structure to make it more efficient.
.
The other way to troubleshoot is by using the process of elimination method. Turn off one module at a time or disable one script at a time and see if the problem stops happening.
.
Good luck.
Nate