One simple trick to clean up your WordPress database

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":"[email protected] |
|            |                      | 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 Replies to “One simple trick to clean up your WordPress database”

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

    1. Are you able to check whether scheduled events are getting run?

      Not easily / immediately. If I run into this context again, I’ll spend some time debugging.

Leave a Reply

Your email address will not be published. Required fields are marked *