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 - 1 through 15 (of 49 total)
1 2 3 4
  • Joseph Scott

    (@josephscott)

    13 years, 3 months 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.

    Thread Starter n2fisher

    (@n2fisher)

    13 years, 3 months 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.

    Joseph Scott

    (@josephscott)

    13 years, 3 months 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.

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 3 months 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.

    Joseph Scott

    (@josephscott)

    13 years, 3 months ago

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

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 3 months ago

    21,370 at the time of writing

    Joseph Scott

    (@josephscott)

    13 years, 3 months 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.

    Thread Starter n2fisher

    (@n2fisher)

    13 years, 3 months 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

    Joseph Scott

    (@josephscott)

    13 years, 3 months ago

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

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 3 months 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.

    Joseph Scott

    (@josephscott)

    13 years, 3 months 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.

    Thread Starter n2fisher

    (@n2fisher)

    13 years, 3 months 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.

    Anonymous User 1437452

    (@anonymized-1437452)

    13 years, 3 months 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?

    Joseph Scott

    (@josephscott)

    13 years, 3 months 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.

    Jartron

    (@jartron)

    13 years, 3 months 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.

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