Comment Relish Plugin + High CPU Usage – Fixed The Right Way

MySQL, PHP
Page content

How The Problem Started

Christmas Eve have just started at about 12:03 AM, when I logged on and saw that dreaded message:

Your Account Has Exceeded Its CPU Quota

So I did what all normal people do at first: I ignored it. Actually, I had a very good reason to at the time. Since it was 12:03 AM, I assumed that my nightly backup job (backup and compress files and databases) was causing the issue.

So after it happened the next day (a few times) I decided to do some investigation.

The Culprit - Comment Relish

HostMonster does a good job of providing some helpful information that you can use to figure out these issues. I went to the directory: cpu_exceeded_logs and sure enough there was a file with today’s (and yesterday’s) date in there. I opened it up and so a ton of referrences to comments on a WordPress Blog:

/ramdisk/bin/php5 /home1/user/public_html/domain.com/wp-comments-post.php

That narrowed things down a little bit. The other helpful directory was mysql_slow_queries. Here I found the following query over and over again:

SELECT c.*, p.*
  FROM wp__comments c
  INNER JOIN wp__posts p ON p.ID = c.comment_post_ID
  LEFT JOIN wp__cr_emailed e ON e.email = c.comment_author_email
  WHERE e.email IS NULL AND c.comment_approved = '1'

It didn’t take too long to figure out that cr stood for Comment Relish.

A little bit of Googling showed me that in some circles, it’s not a very well liked plugin. Some more searching revealed something a bit more technically helpful.

So there you have it, disable Comment Relish and all is well with the world again.

Best of Both Worlds - Proper Fix Instead of Disabling

Now partly because this was a blog I was hosting for someone else and partly because I’m the type of person who’s never satisfied unless they have the answer to everything, I found a middle ground: Good server performance while still maintaining the functionality of the plugin.

So the main problem isn’t the query. Well maybe it is, but I didn’t want to go redesign the entire plugin. WordPress performs tons of these queries all the time and they don’t seem to cause issues. The problem is with table indexes (or lack thereof).

Pulling up PHPMyAdmin showed me that there’s no indexes on cr_emailed.email or comments.comment_author_email; both of which are used above. So that’s a simple fix right? Actually, no. Adding an index to the comments table was simple enough; although some people rather not mess with the WordPress core tables.

The problem is with the table used for Comment Relish: you can’t apply an index on wp__cr_emailed.email because it’s tinytext. Go ahead, try and there’s an error. I guess the author decided on tinytext because it would be smaller than varchar? Not sure if there’s a way arond this or not, but I really wasn’t in the mood to investigate. I’ve used varchar all my life and it’s served me well. So we simply get rid of it, then add the index:

ALTER TABLE `cr_emailed` CHANGE `email` `email` varchar(255);
ALTER TABLE wp_cr_emailed ADD INDEX ( email )

And voila!

Do I Need This With The New Version?

The short answer is yes!

Since this problem has arisen there has been a new version of the plugin put out the combats this problem; there are still many problems with this however:

  1. Since this isn’t part of the official WordPress Plugins, you don’t get the upgrade notice. So I didn’t even know I was running an old version.
  2. The author has decided to use to very same 1.0 version number with the new version, so you can’t even tell if you running the old version without examining the code.
  3. Although there has been a database fix that uses varchar instead of tinytext and applies an index, this fix is only for creation of a new database. So you won’t get the benefit is your table has already been created with the old code.

Hope this helps you folks who are as curious as I was or those who’ve disabled this plugin because of its issues but secretly miss what it does.