Getting Rid of 500K Approved Spam WordPress Comments

Warning: Don't do this if you're unfamiliar with MySQL and PHPMyAdmin.

Solution

With more than five hundred thousand 'approved' spam comments, and growing, my only option was to delete them directly from the database.

Run this SQL query, with the date replaced with the actual date when the spam comments started showing on your blog. You must understand, however, that doing so will remove ALL comments since said date, including the valid ones.

DELETE FROM `wp_comments` WHERE `comment_date` >= '2016-03-12 00:00:00'

There may be alternative solutions. If you of one, please share it on the comment section below.

Story

I have this old WordPress blog that I still keep as a cringy reminders of my younger self. It hasn’t been update since 2013 but continues to receive 100 unique visitors or so a day. Not much to be proud of but I just couldn’t get myself to take the blog offline.

See, that WP blog — along with three other websites &mdash is served on the same server instance as this one. DigitalOcean’s $10 plan seems powerful enough to keep all five sites running smoothly, and with some caching & CDN, relatively fast.

Yesterday, I noticed all five sites loading really slow. A page on any of them used to load in less than five seconds; it was taking more than ten. The first thoughts were to blame it on my crappy internet (PLDT), which has always been the cause.

I logged into SSH and run the command, free -m.

The result showed the server having only 69K memory left. To look deeper, I ran htop. And, guess what, CPU usage is at 98%. It usually hovers somewhere around 20 to 30 percent. After looking closely, htop reveals that mysqld is taking somewhere between 60 to 70% of the CPU.

The next step was to go to mysql and run SHOW PROCESSLIST;. The output shows a lot of "SEND" requests to the WordPress blog, specifically wp_comments.

Having narrowed down the cause, I logged into the WordPress blog to find out more than five hundred thousand approved comments, obviously spam. I automatically turn-off commenting under Settings > Discussion, followed by re-activating Akismet. For some reasons, Akismet has deemed an old API key invalid so I had to get new ones. (I should have monitored this.)

Now the cleanup begins. The easiest way to clean-up is to install WP plugins that clean up Spam comments. Unfortunately, the plugins only work for Spam comments. I couldn’t find one that will try to re-classify “Approved” comments as Spam. Removing them manually is also out of the question. Hence, the solution above.