If you have your WordPress based sites regularly e-mail you a backup of your MySQL tables (oi! You at the back… wake up!), then you may have noticed the size of the GZip attachment has grown a lot in recent months. To be honest, I just ignored it at first, but when I discovered that this site in particular was slower than it used to be, I tried to investigate the cause. I repaired and optimized my tables (after disabling some plug-ins following advice from StarGazer). The size dropped a little, due to the overheads being reduced to zero again, however I was still seeing too much CPU throttling taking place through my CPanel admin interface. Deciding to get my hands a little dirtier, I started to actually look at the contents of one of the worst offenders (in terms of size). The wp_commentmeta entries seemed to be disproportionately large, considering how many comments this blog has. I’d obviously trashed all comments marked as spam, but the size hadn’t gone down anywhere near as much as I’d expected. Looking through the data, it appeared that Akismet had been storing a lot of data for each comment that was rejected as spam. Until I switched to just using the WP-BlackCheck plugin, I was sometimes getting thousands of spammy comments left each week. This meant that due to what I’m assuming was an old bug with a previous version of Akismet, my tables were getting unwieldy!
Now, if you’re still reading this, I’ll assume you’re confident running SQL queries (probably more than me!), in which case, feel free to try the following
Select * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
this should select the entries that can be safely dumped. Then run
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
You should now be done – either check the wp_commentmeta size manually, or just wait for your next backup e-mail to arrive.
I can’t say for sure that has helped speed my site’s load times up (though I can see why it might, I don’t know for sure whether MySQL reads through all data to get to the section it needs, or caches everything to RAM?), or that the drop in CPU throttling rates (currently around 30 seconds total in any given hour) was due to cleaning the tables in this manner, but I don’t think it harmed anything either.
Disclaimer: If your server blows up, your site refuses to load, your car breaks down, your dog runs away and your wife leaves you, I’m not to blame at all. I would recommend considering a career writing Blues lyrics though 😉
May 05 2011
May 7th, 2011 11:53 pm
Odd. My comment meta is pretty small 712KB. I don’t get a ton of spam comments–10 or 20 a day-so maybe that’s why. Not at all comfortable with SQL commands so glad it’s not a worry at the moment.
May 9th, 2011 3:12 pm
I’m hoping now that I’ve dumped Akismet entirely, in favour of WP-BlackCheck, that the table won’t continue to grow. It seems to have been some sort of problem with data retention, where once-useful * possibly needed data wasn’t automatically dumped when the usefulness had expired. If I had nothing to do all day, I’d re-establish the old setup for this blog and run some tests. As it is, I thought I’d throw a quick post up on the matter, and just keep an eye on my DB backup size 🙂
I’m not at all comfortable with SQL commands either, in comparison to most stuff, but as I had a very recent backup that day, I thought I might as well dive in at the deep end again and see what happened 🙂