Akismet Update caused huge database size growth?

5 days later —

Commentmeta is 9.7M in size, with 1602 spam comments over the past 5 days.

Jartron: I would look through commentmeta and see what’s in there

@jartron – how many spam comments are left in the comments table?

@n2fisher – are any of the commetmeta entries for comments that have already been deleted? I’m trying figure out if commentmeta isn’t being cleaned up properly in certain cases.

As far as I know no spam comments have been deleted as of yet; I haven’t looked at it (other than to just monitor the database and make sure it didn’t get too large).

I guess my previous comment:

“Surely I don’t get 3600 spam comments in two weeks! I guess I can look at it in two weeks and see how many spam comments I have.”

Isn’t accurate–with 1902 in 5 days it’s perfectly reasonable to have 3600 in 2 weeks .. if you take it straightline .. 1902/5*14 .. then I’d get 5325 comments in 14 days.

But .. 9.4/5*14 = 26M database size .. still waaaay too large, but nowhere near the 50M that I had originally.

Want me to delete all spam comments and see what happens, or let it ride for a bit and see what happens after a few more days?

Lets see how it continues to do.

It may be that we aren’t missing any commentmeta deletes, just that they aren’t happening often enough, depending on how many comments you get.

I ran following query to find out which comments doesnt exist in the wp_comments table but still have their record in wp_commentmeta table:

Select * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)

And for my surprise it shows approx 23K+ comments which were all flagged as either deleted or spammed by akismet.

I removed all those records using:

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)

And now it reduced approx. 5 MB space from db.

Even now whenever Akismet is catching any spam comment and I’m emptying spam, the “wp_commentmeta” table is still keeping its record which will again increase the db size.

Can’t Akismet remove the deleted spam comment records from db so that it can stay consistent?

In the meantime, I’ll need to run the above mentioned query everyday before taking db backup.

@wp User –

Thanks for the additional info. By any chance, do you have the ‘Auto-delete spam submitted on posts more than a month old’ enabled? I’m wondering if when we discard spam comments under that condition if the commentmeta isn’t being cleared.

^^ No. That option is disabled.

Well, shoot. The only other time that Akismet will delete a spam comment is after it is 15 days old. But we already have code that specifically deletes the commentmeta entries for those.

That leaves us with still no specific details as to how rows from the comments table would be removed but leave behind the associated commentmeta rows.

I’d really like to track this down. If you have any other details that may provide insight into this please let us know. For any sensitive info that you don’t want public you can pass it along to akismet.com/contact/

^^ I would really love to help you in this issue. Please let me know what can I do for you. I’m also very annoyed by manually cleaning the wp_commentmeta table each time I empty 200+ spam comments daily.

Can you look at some of the orphaned commentmeta entries and see if there are any clues there.

There are only two points at which the Akismet plugin would delete a comment, and one you don’t have enabled and the other specifically has a DB call to delete commentmeta entries. So either we are missing something still in Akismet, or there is something else besides Akismet that is causing these orphaned entries.

Ok. I’m not very expert while dealing with db and mySQL queries. So can’t help and understand much about these kind of stuffs.

But to clear my thing and the whole issue:

1. Atm I have 7 spam comments in my blog and there are no orphaned records in wp_commentmeta table.

2. I removed the spam comments by clicking on “Empty Spam” button.

3. I rechecked the database and now wp_commentmeta table contains 7 new records for those 7 spam comments.

The record were similar to following:

meta_key – akismet_history

meta_value:

a:4:{s:4:”time”;s:13:”1296679035.53″;s:7:”message”;s:45:”username changed the comment status to delete”;s:5:”event”;s:13:”status-delete”;s:4:”user”;s:8:”username”;}

PS: Similar entries are created when I flag a spam comment as not spam and then approve it.

Interesting, I wonder if the history system is adding a new meta entry after the delete happens.

Thanks for this info, I’m going to do more testing around this theory and see what I can come up with.

Ok, I think I may have found the problem. WP User if you can test out my fix and confirm that it addresses the issue I would appreciate it.

The change is to the admin.php file in the Akismet plugin. Here is the diff for the change:

https://plugins.trac.www.ads-software.com/changeset/340168/akismet/dev/admin.php

Or you can just grab a copy of the admin.php from -dev:

https://plugins.svn.www.ads-software.com/akismet/dev/admin.php

Awesome. Worked like a charm. ??

That 1 line of code did the trick mate. Thanks.

Thanks for trying that out. I’m going to run more tests with it, but barring any bad things coming up this will be included in the next release of the plugin.