Akismet Update caused huge database size growth?

  • Resolved n2fisher

    (@n2fisher)


    13 years, 3 months ago

    I installed an Akismet update a few days ago when it came out, and a few days later my site stopped working — when comments were posted they were empty, when I tried to add a new post I got “submit for review” rather than “publish” as the blue button to the right. The new post never appeared anywhere in the database.

    After reviewing other things I did recently, and trying to find the problem, I noticed that the size of the database was 150M. 1and1 hosting plans all have a 100M “limit” on databases.

    I removed another plugin that had never worked successfully for me and was using about 40M of the database space, then looked at the commentmeta table, which was using 50M of the space. Turns out it was almost entirely Akismet data on spam comments. I had 3600+ spam comments — I’ve not been consistent in removing spam comments identified by Akismet, and it’s never caused a problem in the past. (I would love to have the program auto-delete comments that are more than a week or two old, as frequently a day or three later someone will email me asking why their comment didn’t appear, and I’ll find it in the spam folder.)

    I do not know if it’s the recent Akismet update, or if it’s just been slowly growing and finally caused problems, but … I deleted all spam and it reduced the size of the commentmeta table from 50M to about 60K. My site now works perfectly again.

    I’m posting this in case others have the same problem, as well as asking if anyone else noticed this. Is it the recent Akismet update, or do I just need to make sure my spam count is kept low.

Viewing 15 replies - 16 through 30 (of 49 total)
1 2 3 4
  • Thread Starter n2fisher

    (@n2fisher)

    13 years, 3 months ago

    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

    Joseph Scott

    (@josephscott)

    13 years, 3 months ago

    @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.

    Thread Starter n2fisher

    (@n2fisher)

    13 years, 3 months ago

    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?

    Joseph Scott

    (@josephscott)

    13 years, 3 months ago

    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.

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 2 months ago

    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.

    Joseph Scott

    (@josephscott)

    13 years, 2 months ago

    @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.

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 2 months ago

    ^^ No. That option is disabled.

    Joseph Scott

    (@josephscott)

    13 years, 2 months ago

    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/

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 2 months ago

    ^^ 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.

    Joseph Scott

    (@josephscott)

    13 years, 2 months ago

    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.

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 2 months ago

    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.

    Joseph Scott

    (@josephscott)

    13 years, 2 months ago

    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.

    Joseph Scott

    (@josephscott)

    13 years, 2 months ago

    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:

    http://plugins.trac.wordpress.org/changeset/340168/akismet/dev/admin.php

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

    http://plugins.svn.wordpress.org/akismet/dev/admin.php

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 2 months ago

    Awesome. Worked like a charm. 🙂

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

    Joseph Scott

    (@josephscott)

    13 years, 2 months ago

    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.

Viewing 15 replies - 16 through 30 (of 49 total)
1 2 3 4
  • The topic ‘Akismet Update caused huge database size growth?’ is closed to new replies.