Most WordPress sites likely run Akismet, which comes bundled with every new copy. What they probably don’t know is that, over time, Akismet will cause your wp_commentmeta table to balloon in size.
Use wp db size --tables to see the size of all tables:
$ wp db size --tables
+-----------------------+--------+
| Name | Size |
+-----------------------+--------+
| wp_users | 9 KB |
| wp_usermeta | 66 KB |
| wp_posts | 274 MB |
| wp_comments | 48 MB |
| wp_links | 3 KB |
| wp_options | 41 MB |
| wp_postmeta | 25 MB |
| wp_terms | 796 KB |
| wp_term_taxonomy | 621 KB |
| wp_term_relationships | 905 KB |
| wp_termmeta | 48 KB |
| wp_commentmeta | 687 MB |
+-----------------------+--------+
Whoa! wp_commentmeta is way larger than I’d expect it to be. What’s going on there?
Let’s take a look at what keys are used:
$ wp db query "SELECT DISTINCT meta_key FROM wp_commentmeta"
+-----------------------+
| meta_key |
+-----------------------+
| akismet_result |
| akismet_history |
| ERRating |
| akismet_user_result |
| akismet_user |
| akismet_rechecking |
| akismet_as_submitted |
| akismet_pro_tip |
| _wp_trash_meta_status |
| is_customer_note |
| rating |
+-----------------------+
Hm. Can’t quite tell what might be a large one from that set. Let’s look at a random comment:
$ wp comment meta list 659968
+------------+----------------------+-----------------------------------------------------------------------------------------+
| comment_id | meta_key | meta_value |
+------------+----------------------+-----------------------------------------------------------------------------------------+
| 659968 | akismet_result | true |
| 659968 | akismet_history | {"time":1503414726.1497,"event":"check-spam"} |
| 659968 | akismet_as_submitted | {"comment_author":"coach handbag tassels embroidery","comment_author_email":"uazetk@gma |
| | | il.com","comment_author_url":"http:\/\/www.bestcoachbag.store\/accordion-zip-wallet-in- |
| | | signature-embossed-leather-p-37.html","comment_content":"Hi, i think that i saw you vis |
| | | ited my site thus i came to return the favor?I'm attempting to find things to enhance m |
| | | y website!I suppose its ok to use some of your ideas!!","comment_type":"","user_ip":"58 |
| | | .19.83.5","user_agent":"Mozilla\/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozill |
| | | a\/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; .NET CLR 1.0.3705)","blog":"http:\ |
| | | /\/example.com","blog_lang":"en_US","blog_charset":"UTF-8","permalink":"http:\/\/exa |
| | | ple.com\/one-two-three"} |
+------------+----------------------+-----------------------------------------------------------------------------------------+
What on Lord’s earth? Why is Akismet storing a copy of every comment in comment meta? I wonder how large that is in total:
$ wp db query "SELECT sum(char_length(meta_value)) FROM wp_commentmeta WHERE meta_key='akismet_as_submitted'"
+------------------------------+
| sum(char_length(meta_value)) |
+------------------------------+
| 567045828 |
+------------------------------+
Ah, I see. Akismet’s akismet_as_submitted entry is 567 MB of the 1 GB total database size. That makes sense.
PS I tried to list the total comments but then I ran into a WP-CLI bug. Que será, será.
2 Comments
The reason that Akismet stores a copy of the comment in commentmeta is so that if a comment is later marked as spam (or “not spam”), Akismet has an exact copy of the comment as it was originally submitted to Akismet to use in the submit-spam or submit-ham API call.
These *should* be deleted after 15 days (or whatever number of days the `akismet_delete_commentmeta_interval` filter returns) by the `akismet_scheduled_delete` event. If they’re not, then either your WordPress events aren’t being run, or there’s a bug in Akismet.
(For reference, on my site that’s been running on WordPress for 11 years, wp_commentmeta is 9.1MB and has ~12,000 entries. The oldest `akismet_as_submitted` value is 14 days old.)
Are you able to check whether scheduled events are getting run? Or, you could manually trigger the `akismet_scheduled_delete` action to see whether Akismet fails to delete those old commentmeta entries.
Not easily / immediately. If I run into this context again, I’ll spend some time debugging.