Podfeet Might be a Wee Bit Faster

I am an amazing proof reader. I can spot a typo on a piece of paper that’s across a table from me and turned around, but I can never see typos in my own work. I can only think it’s an arrogance thing in my brain, that there’s no chance I would ever make a typo. I decided that since Steve, like any good husband, likes to correct me, he would think it great fun to correct my blog posts, so I set him up as an administrator on podfeet.com and indeed he’s having fun fixing my typos. He’s trying to start editing content which is a point of contention so we’ll see how far he gets with that.

This last week Steve and I noticed that Podfeet.com had come to a crawl, especially when we tried to get into the WordPress admin control panel. It was taking over a minute just to log in. I called my web hosting company, Bluehost to ask them to check into it. My little friend Carl started with the obvious, “well it loads quickly for me” line which is always entertaining, but he did believe me that there was a problem so he started to dig in. He said, “wow, this is a REALLY active site!” I countered with, “well, it’s respectable but it’s certainly not huge.” He said that he was seeing a lot of data traffic. I explained that that was unlikely since my audio files for the podcast are hosted on Libsyn, and my videos are on YouTube, so other than a handful of very small images a week, it’s all text for the last 10 years. That gave him a clue something was going wrong.

Let me step back a smidge and explain how blogs work for those who don’t administer their own. I know I’ll be diluting the full story here but I want to give just enough background to make the story interesting. Most websites nowadays use a blogging engine, and in my case I use a tool called WordPress. WordPress sits on top of several components. There are theme files that change what the site looks like. The theme has my banner, defines that there’s a left and right sidebar, defines that the center column resizes when you stretch your browser window, and changes the color and size of header titles and links. In addition to those theme files, there’s a database, in my case a MySQL database, which is an open source database. The database is where the content actually lives. So this blog post you’re either reading or hearing right now is sitting inside my database with the information about when I wrote it, who wrote it, and any categories or tags I put on the entry. Inside the database are tables – one for the blog posts, one for the comments, and one for the pages I create for tutorials. I can change themes with the click of a button and the content won’t change, just the way it looks on the page.

At this point Carl told me that one table in my database was HUGE and shouldn’t be. The table was called wp_commentmeta. It turns out Akismet, the plugin I use to catch spam, was leaving data in this table that it should have cleaned up when it got rid of the bad comments. Carl started a back channel chat with a co-worker who was more versed in databases than he is, and while he worked on that, I did some Googling myself. I came across a site called crunchify.com and the author Arpit Shah had a post entitled, “Akismet and WP_CommentMeta – How to Clean up Comment Meta in WordPress Database“. That sure sounded like what we needed. I told Carl about it, and he took a look as well. Arpit explained that Akismet wasn’t cleaning up like it was supposed to (it’s supposed to purge comment meta data after 15 days). The good news was that the solution was a simple query and delete command within phpMyAdmin, which is a very common tool for talking to databases, and is included in most web hosting company’s arsenal to let you manage your website. I’ve been in there a LOT of times, but always with either Niraj or Bart holding my hand, because the possibility of completely botching up your website is so very high.

Carl was fearless though, he took a backup of my database first (brilliant move) and then issued the the query commands. The first command is to simply look in that comment meta table for anything with Akismet in it, to see how messy it is I guess, and the second command deletes any Akismet-associated records in the table. I held my breath, and in a few seconds, Carl proudly announced that my 261MB comment table was now down to 88kb.

I’m telling you all of this because a) it’s geeky so that it’s fun, and b) so you might notice that podfeet.com is quite a bit snappier these days, and c) if you run your own server this little tip might help you too. I had a lot of fun with Carl and enjoyed learning something new about this whole database thing.