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