How To Clean WP Options - Slow MySQL Queries
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.
wp_options table
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.
Thanks,
Tom
Level 1 Support Engineer
Bluehost.com
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.
Related Posts
Comments
12 Responses to “How To Clean WP Options - Slow MySQL Queries”
Leave a Reply
Comment Count
Get your Badge!
-
Subscribe!
Interesting post. Never knew about those files. I have 12 of those rss_… type files. But, how do I know if I can safely delete them?
I guess another thing that helps me, according to that article you linked to, is that I don’t change my theme. I modify the one I’m using, but I don’t think any of my modifications affect the database at all.
You could try out the clean options plugin to identify which items are orphaned. I suggest being careful. If your site isn’t broken, don’t fix it. My situation is really messed up, and I may have to dish out some real money to get it fixed.
Damn, that must have been really annoying! I would recommend Doreo.com, they are excellent.
I hope it doesn’t end up having to cost you money, Bobby. I can’t pretend to understand all that is going on there at your blog and host but I think I so far am spared from having to know this technical stuff because my hoster handles all the database stuff and I never see it.
It does appear to me that many applications tend to leave orphaned things that “pollute” our computing experiences though. I think we should institute a couple of holidays, maybe once every six months or so where we set aside time for cleaning up this kind of mess on our computers and applications. Maybe get more people to do backups too (myself included, of course as soon as I know how)!
Hi Teeni, I can’t even use any of my back up because they are corrupted. Any new backups I make are corrupted too, so I can’t move to another host even if I wanted to right now. I am so frustrated I can’t stand to deal with it right now.
Nice post Buddy. I am going to check my tables right away and see how many extra ones are there.
Be careful Madhur! I probably deleted some orphan files that may cause me even more problems. I want to hire someone who knows a lot about mysql. When deleting those rss files, they are supposed to be deleted in pairs…the original, and a matching one with the time stamp.
If my host gave me this much trouble, I would switch to a free WordPress Blog.
Come to think of it, my New England Lighthouse Treasures blog is backed up to a private free WordPress Blog.
Maybe, backing up your blog at the free WordPress.com might benefit you?
P.S. I posted your Magic Lamp Techno Tag.
Nicely done, Bobby. Very informative. I’m embarrassed to say it, but I, too, have accumulated quite a few tables I no longer use. 38 on just one blog. Yikes! Looks like I’m going to be cleaning house pretty soon, perhaps right after (or before) I upgrade to the latest version of WP.
@Debbie: My site is FIXED!!!!!!!
@Mark: Thanks! Be careful as hell when doing any cleaning, it’s easy to screw things up!
How right you are (about screwing things up)! I think it takes everyone learning from experience though!
@ Leigh: And I definetly screwed some things up! My recommendation is, “If it ain’t broke, don’t fix it!”