When your self hosted wordpress site is getting slow, or lots of cpu quota exceeded errors, how do you fix it? One place you need to look is in your main directory (using c-panel’s file manager) – find a file called tmp. Inside it, you’ll see several folders, one of them is “slow mysql queries”. I used cheap shared hosting like most people (blue host, host gator, etc.). Because of that, you will experience problems – especially if you start getting a lot of traffic. What I’m meaning is 500+ individual visitors per day.
The past 3 days, my site has been suffering from cpu exceeded errors. I know some of it’s my fault, but some is blue host’s fault. On any given day, there are 150 or more sites on the same server. You can check out who’s on your server with IP Neighborhood Reverse DNS Lookup.
Slow MySQL Queries
I found this error in several different files:
# Mon Jul 14 11:04:39 2008
# Query_time: 3 Lock_time: 0 Rows_sent: 957 Rows_examined: 19610
SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’
When I first saw it, I had no idea what it meant. I thought the number of rows examined being 19610, sounded like a lot.
The one place in a wordpress installation notorious for getting bloated is the wp_options table in your database. Open up PhpMyAdmin and take a look at your database. WordPress has 10 tables listed. If yours has more than 10, they were created with some plugin and you should look into removing those extra tables. Click wp_options, then option_name, go down and click the browse icon.
I read a great post, 2 Things I Hate About WordPress And How To Turn Them Into Your Advantage, where the author said he spent 3 hours cleaning orphaned options by hand. Take a look my personal blog nightmare in the example below:
See all those files starting with rss_248393XXXX? I started deleting them 50 at a time until I realized it was taking a long time. Remember the 19610 rows examined in my slow MySQL queries? Those were the ones. Actually, 19312 of them were these magpie rss files. I deleted them in batches of 500 and it took me three hours! I also deleted many tables created by plug ins I don’t even use any more. I know there are still problems in other tables, but I have more work to do.
I’m not sure which plug in was causing the problem, but I deactivated most of them. I deactivated my Comment Luv plug in until I can figure out what the problem is. When I first began using wordpress, I tried out tons of different plug ins (like a true ignorant novice) and ended up causing myself a lot of grief.
A few months ago, I cleaned out the amount of tables in my entire wordpress installation. In the picture above, you see there are 10 tables listed. There used to be 78! Everyone of them were useless trash, clogging up my system. It was so big, when I downloaded a back up of my wordpress database, it was 137 MB. Today when I did a back up, it was 1.9 MB – big difference.
I’m still having a lot of problems and keep getting CPU exceeded quota errors. I still think that my host has overloaded the server and it’s killing my uptime. If you’re experienced with diagnosing slow MySQL queries, please let me know…I need help!
I will keep learning and repairing as I go, and hope I don’t have to move to another host provider or pay for a dedicated server. Take a look at the email I received from blue host in response to my 13th phone call on Sunday:
Customer insists that his databases have been optimized and his wp database is not that intensive. He has had a few db admins look at his database and they believe it has been optimized as much as it can be. He thinks that, due to recent upgrades and maintenance to the server, it is causing a problem with the cpu quota. The error logs indicate it is his database but he said he has done all he can to optimize it. He would like us to look at it and prove that it is his db causing the problem.
Level 1 Support Engineer
That’s right, I want them to prove it’s my fault and not theirs…LOL! Well, I may have a long wait. I will never install another plug in. I use a few, like Akismet, All in one SEO, No WWW, and Do Follow…but those aren’t causing problems. I need an experienced person to check out my database. Let me know if you can help or know someone that can.