Safely delete spam comments across a large WP network

I’m currently working on a university WordPress network that’s been running for four or five years (an MU veteran!) and has almost 5000 blogs, most of which are defunct (because they’re from previous semesters). Akismet is activated across the network, so there’s not much of a public spam problem. However, even spam comments are stored in the database, and some of the blogs have tens of thousands of spam comments sitting in their tables. I’m going to implement a couple of tricks to keep this from happening in the future (a lightweight honeypot for non-logged-in users, tell Akismet to auto-delete spam comments on old posts). But for now, I’ve got to clean up this mess, because the very large comment and commentmeta tables are causing resource issues.

I wrote a simple script that gradually cycles through all the blogs on the network and deletes comments that have been marked as spam by Akismet. Here it is, with some comments afterward:

Notes:

  • The number of blogs is hardcoded (4980)
  • The ‘qw_delete_in_progress’ key is a throttle, ensuring that only one of these routines is running at a time. You might call this the poor man’s poor man’s cron.
  • I’ve limited it to 10 comments per pageload, but you could change that if you wanted
  • Put it in an mu-plugins file. When it’s finished running (check the ‘qw_delete_next_blog’ flag in the wp_sitemeta table – it’s done if it’s greater than the total number of blogs on the system), be sure to remove it, or at least comment out the register_shutdown_function line.

Use at your own risk – I’m posting here primarily for my own records 🙂

12 thoughts on “Safely delete spam comments across a large WP network

  1. Pingback: Delete spam from all blogs — WordPress "Must-Use" Tutorials

  2. Pingback: Borrar todos los comentarios spam de una red WordPress descargar gratis | Zonadictz

  3. lwaltzer

    Awesome Boone. Thanks.

    Unless I’m missing something, it doesn’t look like this will clear out the Akismet data in commentmeta… that’s where most of our bloat is happening (Akismet dumps our spam every 30 days, but all of our calls to it get stored in commentmeta). Do you know if there’s a reason for that data to be retained?

    Reply
  4. Pingback: A Blogger’s Best Friend: Askimet | Inside Out Cafe

  5. Pingback: AKISMET WORDPRESS PLUGIN - ElevatedBlog

  6. Pingback: 500 Posts! | The Ranting Papizilla

  7. Pingback: Akismet….. for the record « Minkyweasel World

  8. Pingback: Akismet WordPress Plugin - ElevatedBlog : ElevatedBlog

Leave a Reply

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