I had noticed a considerable slowdown in this blog sometime ago and had not been able to track down the cause for this slowdown. I had tried a variety of different things including breaking down the database into small portions and the code into more sizeable chunks to try and isolate what was causing the slowdown (including all kinds of optimization/repair techniques).
I remembered this morning that I had installed Spam Karma to test it and had since uninstalled it. However, I had not realized that Spam Karma had created a fulltext index of the comments table and I had not removed that index. Adding to the problem was the feature of WordPress 1.5 that stores all spam comments. The comment fulltext index had grown to over 50 megs in size (with over 25000 spam comments plus the few thousand legit ones). All of this resulted in every mysql query taking more than one second to execute.
I removed the fulltext index, cleaned out all the spam comments and optimized all the tables and the speed seems to have returned. This post is not meant to defame Spam Karma since I believe that it is a VERY good plugin but it is meant to educate other users that might be having the same problem. If you want to continue using SK with WordPress 1.5 and want to recover some of the performance loss, I suggest you use some kind of a plugin that deletes spam comments instead of storing them in the database.
Earlier versions of Spam Karma did delete the comments. However, they changed this to fit with how WordPress 1.5 itself handles spam comments (storing them). The aim was to later use that data to improve the filter itself (a “learning” filter).
Which version of Spam Karma did you install ? If you did install the 2.0 alpha maybe you can use the current 1.X version that does not store comment.
Well, Somebaudy, as George notes, 1.20 forward in the 1.x branch still stores the comments.
I think storing the comments is a worthwhile thing to do, although from a design perspective, I don’t know if it might not make sense to move spam to another database table. I’d have to think on that a while.
I’d rather keep the spam around for Bayesian-filtering purposes, although perhaps duplicate triples [URL, IP, email] could be gleaned…
For clearing out/evaluating spam comments, I’ve fallen in love with Coldforged’s Paged Comment Editing plugin.
SK1 used to add that FULLTEXT key to the comments table with 1.2… although it doesn’t do it any more (1.5 SQL schema changed, for some reason). But at that time, there wasn’t any issue of thousands of useless spams kept in the table… Indeed 50 megs is much too big…
SK2 doesn’t create any key and will probably auto-purge every X days (keeping comments marked as spam is only interesting as a way to recover them in case a mistake has been done)… so things should be better…
Then again, I am pretty sure both SK1 and SK2 bring some real PHP overhead… but I am afraid there is little way to make that disappear, at best I think I can optimize it to keep it under control and bring it to the same level as other plugins or built-in tasks (for now, it seems to add about 1-2 seconds to the comment posting process… but I will soon add exact benchmarking functions to keep track of this).
I’ll try spamkarma then. Is this why many of my trackbacks do not work?
I’ve just noticed that SK2 was guilty for slowing my comments posting system, and i’ve reached this blog looking for more information on how to deal with this.
At the moment, with 2700 comments on my database and over 7000 of spam comments caught (and kept) by SK2, it takes more than 60secs to post a comment on my blog.
I’ve emptied Sk2 spam table and it keep slowing down my comments.
I know that this is because of SK2 because when i disable it, it all works perfectly. What else can i do? I didnt empty sk2_blacklist (1500 records), and i think that i souldn’t.
Any suggestion?