Misbehaving Comment Relish Plugin

Today, I left a comment on Nate Whitehill’s blog for his post about what a header image says about you. A few minute after the comment I get this email from Nate.

Hi John Chow,

Thanks for taking the time to visit my blog. I hope you found some good information about business, blog, and web development If you did, please consider subscribing to my RSS feed (http://feeds.feedburner.com/NateWhitehill).

Let me know if you have any questions. I look forward to seeing you around the blogosphere. Take care!

Nate Whitehill
http://natewhitehill.com

My first impression upon receiving the email was, “OK, this is a plugin.” I emailed Nate back and asked him about it. He replied back saying the email was from a WordPress plugin call Comment Relish.

Comment relish is a WordPress plugin developed to send an e-mail message to users who comment on your website who have never commented before. The message dispatched to the user is defined within the plugin’s preferences. Numerous tags have been integrated to allow for information to be included in the message easily (I.E.: timestamp, author name, comment, ETC.).

Comment Relish sounds like a great plugin. However, there is one major flaw in it. The plugin goes through your comment database to find out if the reader have ever made a comment on your blog. If not, then it send the thank you email. This isn’t a problem when you only have a 100 comments or so. However, if your blog has 42,000 comments (like this one does) the plugin will consume all the server’s resources trying to find out if you have commented before. The instant I turned on the plugin, it crashed the MySQL database and maxed out the server load to 100! This was why this blog was unavailable for the last hour. We thought we were under a MySQL DOS attack.

Sending a thank you email to first time commentator is a great idea and I would have loved to use Comment Relish to do that. However, if your blog has a lot of comments, I don’t recommend using it unless you want to experience a big time server crash.


44 thoughts on “Misbehaving Comment Relish Plugin”

  1. O ya, i was like what is going on with JohnChow.com? Look i even took a screenshot. Good to have to back.

    http://blogsandbucks.com/images/johnchow_server_down.gif

    1. blogcrowds says:

      Quick work! I was curious about this as well, thought the blog was hacked with good old Denial of Service. Well, it kind of was…

      There should be better way to implement the widget. ie instead of searching through everything, keep track of who the email has been sent to in the MySQL database and search through that. This MySQL table should be much smaller.

      1. Wow. You guys are quick. It’s nice to know that JC has legions of followers to support him.

        cashforcomments.com
        CASH for COMMENTS | get paid to comment.

    2. Yep, I took one too, just in case:

      http://www.aaroncook.com/2007/06/where-did-john-chow-go.html

      I’m really glad it wasn’t a MySQL DOS attack from some rogue asshole…as I’m sure John is as well.

      1. Alex says:

        If John doesnt update to WP 2.2.1, he can be vulnerable of a DOS attack…

      2. BTW, I had a real nasty shit-for-brains leave an antiJohnChow (and pro-hacking) comment on my blog this evening…
        http://www.aaroncook.com/2007/06/where-did-john-chow-go.html

        But I put it to him/her pretty good! 😈

        It’s a shame when people think that hacking into someone’s blogs is OK. I’m just glad John wasn’t actually hacked this time.

    3. Walter Vos says:

      That’s so cool, the content links even work on error messages! :smile:

  2. Steven says:

    Welcome back online John (I didn’t notice you were offline at all ^_^)

    Isn’t there a way to set the settings of comment relish to only scan say a month’s worth of comments or a week’s worth rather than your entire database?

  3. Alex says:

    Wtf, John is better not use all plugins than ppl recommend, a lot of plugins are dangerous….

  4. I wondered why this site was down. I had never seen it down before. 😎 Nice plugin, I might try it on my site!

  5. I was wondering what happened to this place. I thought you had finally packed up and went to that island in the middle of nowhere.

  6. Ed says:

    I haven’t looked at the plugin in depth, but my first thought is this could have been designed much better. Why is it searching comments and not a list of users? I’m assuming there’s a user table in WordPress (been awhile since I looked) that you could just add a boolean column to in order to keep track of who has been sent an email. Should only require tweaking the SQL query the plugin uses in two spots, checking and setting the flag.

  7. Robert says:

    Comment Relish is primitive at best. It doesn’t support HTML tags so links don’t work. Also, there is no easy way to send yourself a test email.

    I tried it a week ago and deleted it after 5 minutes.

    1. Kumiko says:

      All you need is another email address to send a test.

  8. Ted says:

    A smarter programmer than me would refine this plug in so it has a separate table for commenter’s email addresses. It should reduce the server load massively.

    1. Chris Hope says:

      Also never minding that 42k records isn’t really that many… you need to make sure the email address field is indexed.

  9. Darn, then my MySQL DOS attack wasn’t really what caused it then? Just kidding. 😆

    Ah, so then that would explain it…

    Where Did John Chow Go?
    http://www.aaroncook.com/2007/06/where-did-john-chow-go.html

    The plugin is a great idea, but it obviously needs to be reworked so that is doesn’t use up so many resources. Crashing a server is utterly ridiculous.

  10. Sammy Larbi says:

    That’s strange. 42,000 records is not many by any stretch of the imagination for a DBMS, even if you have no indexes on the column it was searching through.

    Good idea though.

  11. Casey says:

    As I was reading this post, I was about to go ahead and stop to download it, until I read it’s a pain for big blogs and can crash your mySQL.

  12. Browie says:

    Thanks for the information. I wasn’t worried about your website but I just installed that plugin. I think i’ll be ok since I have about 1,000x less comments than you do. :roll:

  13. Wallace says:

    nice plugin to change your visitors to the rss subscribe readers.

  14. Good idea,
    But instead of looking thou the all your comments it should set up anther table of people that it has already sent emails too…
    Searching that table would be alot quicker.

  15. Drew says:

    Sounds like a good concept for a plug-in. Despite the risk I may give it a try. Shame that the site went down for a bit though.

    Thanks for the advice and the tip!

  16. Eugene says:

    Awesome! Never thought this plugin existed. Downloaded and installed it. Thanks John and Nate.

  17. Richard Cai says:

    The speed is still very slow.

  18. webd360 says:

    It is a nice plugin, Steven Smethurst’s idea seems like it would make it more efficient too so that blogs like yours can use it. When your blog was down I thought your server crashed or something…

  19. Mason says:

    i believe bob buskirk has the same plugin. when I replied to his blog I got an email as well. too bad his spam plugin banned me forever. :)

  20. Thanks for the warning John, I was just about to download the plugin!

    FT

  21. On that note I think that I will turn it off even before I turn it on. I just recently added it to the blog but I certainly do not want to have a crash in the near future.

    Thanks for sharing John

  22. dotnetnuke says:

    I experienced the timeout error last night, welcome back again. :roll:

  23. shaun says:

    Nice plugin John I will use this on my site. Im sure other bloggers will use this plugin as well 😀

  24. Wahlau.NET says:

    it is a pretty good idea to have this plugin, not sure if it improve a blog

  25. Joy Anderson says:

    I think replying to the poster personally, is a much better idea. I guess however, if you have over 42,000 comments it can be a lot of work! I would still go for the personal touch, but that’s just me. Hope you have a great Father’s Day!

  26. SOLUTION: Here’s how I think this can be solved. You need to add an INDEX to the email address. Go to your PPHPMyAdmin page and find your MySQL database and enter the following (if your DB is not modified)

    ALTER TABLE wp_comments ADD INDEX ( comment_author_email )

    1. To make it even better, change the email field in cr_emailed table from TINYTEXT to VARCHAR(255) [ ALTER TABLE `wp_cr_emailed` CHANGE `email` `email` varchar(255) ] and add an index on that column too [ ALTER TABLE wp_cr_emailed ADD INDEX ( email ) ] . that way it will use indexes in both tables when it makes a query to find out where to send the messages and the query will be faster and less resource consuming.

      1. Good point, that will help it even more.

  27. Ash Haque says:

    Has anyone else tried the plugin? Say if your site has around 500 or so comments, will it only be a problem the first time it’s activated? Or everytime a comment is made?

  28. Michael says:

    I considered using the plugin but I just don’t want to bother my readers with emails. Maybe it would help them come back but I just don’t like to bother people.

  29. I made some modifications to it and tested on 25000 comments, It takes about one second for it to figure out where to send the messages. You can read more details about comment relish optimisation here

Comments are closed.