{"id":3004,"date":"2013-12-03T15:42:49","date_gmt":"2013-12-03T14:42:49","guid":{"rendered":"http:\/\/t-machine.org\/?p=3004"},"modified":"2013-12-03T15:42:49","modified_gmt":"2013-12-03T14:42:49","slug":"wordpresss-akismet-pushes-database-to-30x-real-size","status":"publish","type":"post","link":"http:\/\/new.t-machine.org\/index.php\/2013\/12\/03\/wordpresss-akismet-pushes-database-to-30x-real-size\/","title":{"rendered":"WordPress’s Akismet pushes Database to 30x real size"},"content":{"rendered":"

Like half the planet, I use WordPress as a blogging platform. There are many good things about it. One of those used to be<\/strong> Akismet (if you ignore the slightly unpleasant sales strategy: it’s free, if you give WordPress some personal details) – but today I hit a very serious bug in Akismet. I’ve had to delete Akismet – but WordPress’s coders don’t clean up after themselves, so you have to do some manual clean-up too. Read on.
\n<\/p>\n

Spamageddon<\/h2>\n

I was getting an average of 200 spams per day that Akismet couldn’t detect (O, RLY?). On the advice of Paul Silver<\/a>, I tried Anti-Spam for WordPress<\/a> … and the spam rate dropped by 99%. Joy!<\/p>\n

A couple of days later, I noticed my database backups (courtesy of DB Backup for WordPress<\/a> were failing to auto-download. (bear with me, this is related…).<\/p>\n

DB-Backup has an option to manually run a backup in your web browser and immediately save it to disk. This normally takes 30 seconds; this time, it took 4 hours. Looking at the output, it was saving 750 megabyte backup files (normally: they’re 20 megabytes or less).<\/p>\n

What?<\/p>\n

Irony: Akismet spams your database<\/h2>\n

A couple of years ago, WordPress added a feature to Akismet where it stores a vast amount of extra info to every comment<\/strong>. I can guess what that might include, but as far as I can tell, Akismet is proprietary, so no-one really knows how it works. It’s supposed to delete this data after 15 days (or even: immediately, depending on your settings). In practice, this appears never to have worked (WordPress’s forums + blogs + twitter are full of complaints about it. NB: I have the latest version of Akismet plugin).<\/p>\n

Login to your SQL admin account (or use PHPMyAdmin … or whatever your preferred way of editing your SQL database is).<\/p>\n

Count the damage<\/h3>\n

With your wordpress database selected (NB: you should have one per website\/blog on your server; make sure you pick the right one), these commands merely “count” stuff, they have no side-effects. The worse that can happen is they slow down your server for a few seconds while they’re counting.<\/p>\n

[sql]
\nselect count(*) from wp_commentmeta where meta_key like "%akismet_%";
\n[\/sql]<\/p>\n

\n+———-+
\n| count(*) |
\n+———-+
\n| 545201 |
\n+———-+\n<\/p><\/blockquote>\n

Half a million comments that Akismet has added its “proprietary” info too. OK, if that’s what it takes to defeat spammers, so be it.<\/p>\n

But how many are actually “spam”?
\n[sql]
\nSELECT comment_approved, count(*) FROM wp_comments group by comment_approved;
\n[\/sql]<\/p>\n

\n+——————+———-+
\n| comment_approved | count(*) |
\n+——————+———-+
\n| 0 | 236 |
\n| 1 | 2887 |
\n| spam | 185455 |
\n| trash | 22 |
\n+——————+———-+\n<\/p><\/blockquote>\n

Wait, what? Of 500,000 comments … 300,000 (60%) don’t actually exist. The original comments (presumably spam) were deleted \/ auto-deleted long ago – but Akismet is keeping hundreds of megabytes of data on them.<\/p>\n

Of what’s left, the majority (almost 200,000) are marked as “spam”. OK, great – it’s done a good job over the years (until recently, when spammers found their way around it). That’s not unexpected.<\/p>\n

But since Akismet’s proprietary info is a little verbose (I’m not sure they planned this too well) and their bugs prevent it from ever being deleted … we’re wasting hundreds of MB here. This makes database backups difficult – we’re not backing up “the website and content”, we’re backing up “Akismet’s junk … plus a tiny amount of website content”.<\/p>\n

\nNB: within WordPress, it seems there was a more-sensible way for Akismet to store this data, but they didn’t use it. Like all other plugins, they should (surely?) have been saving their data to a separate table, preventing these problems, and making it much easier for them to fix their deletion bugs. One poor decision by Akismet team = problems for everyone?\n<\/p><\/blockquote>\n

Delete the Akismet Scourge<\/h3>\n

First, deactivate the plugin – these days, it has very little benefit over-and-above my default WordPress anti-spam setting (“First comment from any new user requires moderator approval” — blocks 95% of all spam instantly)<\/p>\n

…but we have to manually clear out the crud. These next commands ARE destructive, so please don’t typo them – check carefully:<\/p>\n

1: get rid of akismet proprietary info<\/h4>\n

[sql]
\ndelete from wp_commentmeta where meta_key like "%akismet_%";
\n[\/sql]<\/p>\n

Note: my server is fast, but this took a long time:<\/p>\n

\nQuery OK, 545201 rows affected (2 min 26.85 sec)\n<\/p><\/blockquote>\n

2: delete all the old spams<\/h4>\n

These spams now have no further purpose (and in some cases, due to badly-written plugins, or WordPress design flaws: they can slowdown WordPress itself, especially the admin interface).<\/p>\n

[sql]
\nDELETE FROM wp_comments where comment_approved=’spam’ OR comment_approved=’trash’;
\n[\/sql]<\/p>\n

…and check they’ve gone:<\/p>\n

\nSELECT comment_approved, count(*) FROM wp_comments group by comment_approved;
\n+——————+———-+
\n| comment_approved | count(*) |
\n+——————+———-+
\n| 0 | 236 |
\n| 1 | 2887 |
\n+——————+———-+\n<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Like half the planet, I use WordPress as a blogging platform. There are many good things about it. One of those used to be Akismet (if you ignore the slightly unpleasant sales strategy: it’s free, if you give WordPress some personal details) – but today I hit a very serious bug in Akismet. I’ve had […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45],"tags":[],"_links":{"self":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/posts\/3004"}],"collection":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/comments?post=3004"}],"version-history":[{"count":4,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/posts\/3004\/revisions"}],"predecessor-version":[{"id":3008,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/posts\/3004\/revisions\/3008"}],"wp:attachment":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/media?parent=3004"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/categories?post=3004"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/tags?post=3004"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}