WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Akismet Update caused huge database size growth? (50 posts)

  1. n2fisher
    Member
    Posted 3 years 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.

  2. Joseph Scott
    Member
    Posted 3 years ago #

    I've seen one other report similar to this, but was not able to get any further data about it.

    Starting with 2.5 the Akismet plugin does store history data in commentmeta. Storage requirements will vary depending on how many comments you get.

    It will also automatically delete spam comments that are older than 15 days. When it deletes those, the commentmeta entries should be deleted as well.

  3. n2fisher
    Member
    Posted 3 years ago #

    I'm using 2.5.2; don't know what version I had before it, but I regularly update it, so I'd assume 2.5.1. I see no option to delete comments that are older than 15 days, I don't know how old the oldest comment is .. I suppose I could look at the backup SQL file and try to figure it out.

    The only option I see is to delete spam comments on posts that are older than 30 days .. that isn't valid for me, as people on my site regularly comment on old posts. I'd like to retain comments for 1-2 weeks, and have them auto-deleted after that. 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.

  4. Joseph Scott
    Member
    Posted 3 years ago #

    The 15 day purge happens automatically, there is no specific option for it.

    As for the numbers, check out your Akismet stats, that will give you an idea of how many spam comments you are getting.

  5. WP User
    Member
    Posted 3 years ago #

    I'm also facing the same problem. I get many spam comments daily. Approx. 1000+. And my database size is increasing daily. I found that "wp_commentmeta" table is storing values for Akismet in "meta_key" and "meta_value" fields. There are approx. 23000 records in this table.

  6. Joseph Scott
    Member
    Posted 3 years ago #

    How many non-spam, approved comments do you have on the sites?

  7. WP User
    Member
    Posted 3 years ago #

    21,370 at the time of writing

  8. Joseph Scott
    Member
    Posted 3 years ago #

    Interesting. I wonder if the problem of growth for the commentmeta table isn't that meta about deleted comments isn't being purged, but is instead about meta data on approved comments.

    Thanks for this info, this gives me some ideas on where to look and how to improve the situation.

  9. n2fisher
    Member
    Posted 3 years ago #

    I deleted all the spam comments (3600+) and the table emptied from 50M down to about 60K. That seems to be an indication that it is current comments and not deleted comments.

    If you want I can send you my commentmeta table if you can provide an email address.

    better yet -- here it is. I don't think there is anything confidential in this:

    http://steadfastlutherans.org/nfisher/commentmeta.txt

  10. Joseph Scott
    Member
    Posted 3 years ago #

    Perhaps we need to run optimize on the commentmeta table more often then.

  11. WP User
    Member
    Posted 3 years ago #

    I know one thing! In "WP_commentmeta" table each record contains Akismet related value in "meta_key" and "meta_value" fields which is increasing the db size I think. Also since comments are 21,370. Why WP_commentmeta contains more than 23000 records. Its when I don't have any spam comment . I emptied all spam comments.

  12. Joseph Scott
    Member
    Posted 3 years ago #

    Please read previous replies on this thread, I already mentioned that Akismet, starting with version 2.5 of the plugin, stores data about comments in the commentmeta table.

    http://wordpress.org/support/topic/akismet-update-caused-huge-database-size-growth?replies=11#post-1894684

    For your case, I think the increased storage is from keeping data about already approved comments.

  13. n2fisher
    Member
    Posted 3 years ago #

    Oh, I think you asked: 33,338 approved comments on 1,648 posts.

    Appears to be 200 spam comments a day,

    In December, 5,028 spam and 2,120 ham.

  14. WP User
    Member
    Posted 3 years ago #

    I think there should be an option provided in Akismet settings whether it should store data in WP_commentmeta table or not. It'll really help people in keeping their db size small.

    as far as I'm understanding the db overhead issue is causing by the newly introduced feature keeping akismet history. Why not provide an option to on/off the history?

    Also is there any way or SQL command to remove akismet data from WP_commentmeta table so that we can reduce db size manually?

  15. Joseph Scott
    Member
    Posted 3 years ago #

    The goal of having the data in commentmeta is to provide accurate details to Akismet when reporting spam/not spam, so disabling that would be counter productive.

    If the plugin is missing opportunities to trim back the data then we should look at how to improve that.

  16. Jartron
    Member
    Posted 3 years ago #

    @Joseph
    I noticed this problem on a site I manage.
    It has only 70 approved comments and askimet has recorded 20,100 spam.
    Wp_commentmeta is huge (>30mb) even when the spam is deleted.

    This would definitely imply it's not all about approved comments.

  17. n2fisher
    Member
    Posted 3 years 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

  18. Joseph Scott
    Member
    Posted 3 years 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.

  19. n2fisher
    Member
    Posted 3 years 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?

  20. Joseph Scott
    Member
    Posted 3 years 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.

  21. WP User
    Member
    Posted 3 years 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.

  22. Joseph Scott
    Member
    Posted 3 years 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.

  23. WP User
    Member
    Posted 3 years ago #

    ^^ No. That option is disabled.

  24. Joseph Scott
    Member
    Posted 3 years 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/

  25. WP User
    Member
    Posted 3 years 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.

  26. Joseph Scott
    Member
    Posted 3 years 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.

  27. WP User
    Member
    Posted 3 years 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.

  28. Joseph Scott
    Member
    Posted 3 years 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.

  29. Joseph Scott
    Member
    Posted 3 years 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

  30. WP User
    Member
    Posted 3 years ago #

    Awesome. Worked like a charm. :)

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

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags