Akismet Update caused huge database size growth?

Joseph, I have a hosting customer that is having this same problem, I think. Unfortunately, I’m not a WordPress expert and so I’m not sure. I don’t see a table called commentmeta in their install, but their comment table is almost 800MB in size and their dashboard says they only have around 3,000 comments and less than a dozen in the spam queue. Queries are running very slow as a result of the size of this table. They disabled the Akismet plug-in this morning to stop the growth of the table.

I’d like to delete the extra data from her comment table, but not being familiar with WordPress table structures or Akismet, I don’t know what criteria to use to delete the Akismet data without also deleting her comments.

Can you help? ??

@isparks_curtis –

From what you describe it isn’t clear that Akismet is contributing to the growth of the comment table, in fact it is probably helping keep it in check by auto deleting spam comments that are over 15 days old.

If you don’t have a commentmeta table then you are likely running an old version of WordPress. You also mentioned that there are only 12 spam comments, so deleting those 12 likely won’t help you much.

I think you should gather more info to help pin down exactly what is going with the table size. For instance, when was the last time optimize was run on it to reclaim space from deleted rows?

@joseph,

Their dashboard says they’re running WordPress 3.0.4.

I went ahead and ran an optimize on the table, but the table didn’t shrink any.

But, I guess you’re saying this isn’t an Akismet issue since they don’t even have a commentmeta table. I guess it’s time I actually take a look at the raw data and see if I can figure out where all the extra records are coming from.

If they don’t have a commentmeta table and they are running 3.0.4 then something is wrong. Perhaps an upgrade from an earlier version didn’t run correctly?

I’d take a serious look at what is going on to make sure that there aren’t other things missing from their install as well.

Yeah, I agree… this looks like an issue of their tables not getting upgraded properly. Hopefully I can find a procedure for getting them out of that predicament. Thanks!

i have also a huge wp_commentmeta table (> 5,495kb) and a small comment table ( < 80 kb) but i checked the askimet admin.php code and mine already has the additional 1 liner. so i’m stumped.

the thread that i started is here.

i just took a peek at the contents of wp_commentmeta and its mostly about askimet_history and askimet_as_submitted so i suppose its askimet related.

i’m using wp 3.0.5 already and askimet 2.5.3

OK; I now have two weeks of data in my database.

The oldest spam comment is from 1/31

My commentmeta database is 37M and my comment database is 40M
I have 3118 comments in my spam folder.

Comment: I’d like to be able to set the number of days that it removes spam automatically — obviously 2 weeks is too long for my website. I’d like to set it to 3 days .. if someone hasn’t contacted me in that time they can lose their comment.

I’m about to remove all spam comments.

My commentmeta table has now dropped to 18M and my comment table to 34M

I have 34K comments in my comment table.

Running this line:

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

produces 5.4K of the 8.3K entries in commentmeta have no comment associated with them. I have not deleted any spam comments, so your update is not the reason for these unknown comments.

Running this line:

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

took my commentmeta table down to 2.8K entries and 5M tablesize.

The first three lines now deal with comment id 123867, which is a valid comment:

a:4:{s:4:”time”;s:15:”1295452823.0785″;s:7:”message”;s:28:”Akismet cleared this comment”;s:5:”event”;s:9:”check-ham”;s:4:”user”;s:0:””;}

There’s really no reason for this information to be in the commentmeta table is there? The akismet_as_submitted entry is huge.

wow. that line put wp_comment meta down to 32.45kb!

thanks n2fisher

Despite having the updating Akismet admin file, my wp_commentmeta is still 25mb. It’s filled with nothing but askismet references to non-spam comments. I emptied out my spam and trash comments and repaired / optimized the table, but it didn’t shrink hardly at all.

I ran the two lines that n2fisher suggested above, but they also produced zero results.

Can I just empty the entire table or will that also mess up my comments?

I’ve emptied the table several times since I initially reported it with no problems noticed. I normally empty it after clearing out spam comments.

My current meta table is at 23M; I just ran the line above:

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

and it removed 2M from the table. I have NOT installed the change recommended. I have 2719 spam comments currently.

I REALLY would like to be able to set the “delete spam comments after 15 days” to 5 days. I am using 1and1 for hosting and they have a 100M limit on database size — 21M in spam filtering is just way too much space taken. They evidently have no way to raise the limit from 100M, with 10M in posts and 40M in comments it’s just getting too close to the limit for me.

I guess I’ll install the line, but it doesn’t appear to save enough space.

((later: Evidently the line has been added to normal updates, as I checked and the line is inserted in my admin.php file))

If I empty wp_commentmeta table, will it cause any issue as the whole table only contains reference to Akismet activities?

Its size is 13.4 MB atm and if I delete all records under it, I can reduce my db size.

Can someone tell me what the purpose of the akismet entries in the wp_commentmeta table serve? Is there any danger in deleting them if the referenced comment no longer exists in the wp_comments table?

I’ve also got a huge wp_commentmeta table.
I’m running WordPress 3.2 with Akismet 2.5.3

wp_comments has 10,241 records at 7.2 MiB
wp_commentmeta has 27,045 records at 33.9 MiB

This suggested query:
Select * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)

returns 241 records of the 27,045 in the table

Any idea what I can do to reduce the size of wp_commentmeta?

Thanks!

Hi Joseph,

I have a HUGE temptation to purge the records in the wp_1_commentmeta table but fear consequences. Will it hose my 40,000+ comments?

For what it’s worth, the wp_1_commentmeta table has grown to 100,000+ entries of Akismet stuff, and is taking up around 90 megs of space.

I’m running Akismet 2.5.3 with WP 3.1.x.

@smazsyr

I had the same problem. I went ahead and purged the records and the site is back to normal now.