Reduce Database Backup Size: Delete Akismet Blocked Spam

Does you internet connection time out while backing up your huge wordpress database files? Your actually useful database file (.sql) is not as large as it seems… and you could easily reduce the size of the backup file by a little extra effort.

Last time I backed up my WordPress database using PHPMyAdmin for a wordpress upgrade, it was over 41MB! It was so large that it took a lot of time to download over the internet connection and was impossible to create an error-free copy. Then I realized that the actually useful database tables were not so huge.

I use Akismet wordpress plugin which has effectively blocked over 400,000 spam comments on this blog. On that day, Akismet was storing over 20,000 comments marked as spam (I usually get that much comment spam in a week!). I deleted all the Akismet marked comments (Delete All button rocks!), retried backing up my database and the file created was a small 7MB .sql file, easily compressed by .zip download to 2MB!

Lots of pending comments can create huge table sizes in the database. Thus, it is a good idea to check and delete all Akismet comments (after checking for valid comments – if you have the time) to minimize the size of the database (assuming you have already cleared the comment moderation queue). It also avoids backup and PHPMyAdmin errors encountered during backup of such a huge file.

I use the amazing WordPress Database Backup wordpress plugin to create weekly backup of my WordPress database. If I check my database files over the weeks, they vary from 3-7 MB and I believe this might be due to the variable amount of accumulated Akismet spam I have not cleared when the backup was automatically done.

So reduce the size of your database backup file by clearing your pending comments in moderation and those blocked by Akismet. You file find the .sql file much more manageable and usable. Try it.


  1. Shankar Ganesh says:

    That’s a good tip. Those thousands of spam comments surely occupy too much space. Gotta delete ’em. Thanks for sharing…

  2. Dj Flush says:

    Nice tip :D

    Thanks for sharing. I currently have 8k Spam :O

  3. Nirmal says:

    I delete Spam comments on a daily basis. I feel thats the best way to clear it, otherwise once it gets to more than 1000, you can’t even check if there was any genuine comment caught by Akismet.

  4. Ellie says:

    Any tips for a person that desperately needs to use a backup file that is chock full of spam comments that weren’t finally zapped before the backup was made? PhpMyAdmin doesn’t even want to take the text of all of this and I know that my legit comments are not what is clogging it up. What are the syntax clues that the Akismet crap is starting?

  5. Asuka says:

    My blog’s host (freehostia has blocked my site because their is spam comments containing key words that get you blocked automatically)Is there a way to delete these comments right from the database or I absolutely need to be able to log on to my blog and manally delete the spam comment. I also use the Akismet plugin. I’m so stuck because I can’t find anything on the subject. Any help would be apreceiated.

  6. Asuka says:

    I solved my problem by going in the database and deleting all the comments. The strange thing is I only say my regular and approved comments. Where are the filtered comments stored?

  7. Tony says:

    I just wish there was a way from within the wordpress program to disable the storage of comments marked spam….!

    Oh well in the mean time, here is a PHP file I made to clean up the spam!

  8. Jane says:

    WordPress must have changed this because I have 2.8 and there is no spam in my database.

  9. Keith Davis says:

    Thanks for the heads up.
    My blog is new and I can see that I am slowly building up the amount of spam that Akismet picks up.
    A great plugin that I check every day.
    With Google using page load speed as one of its metrics, it makes sense to keep the database size down.

  10. Sankar says:

    You are awesome. with spam comments my back up file size is 2.4MB. After deletion size became to 1.9MB.


  11. Mightee says:

    400,000 of spam comments? :O
    are you serious mate?
    y didn’t you delete them regularly?
    by the way thanks for great tips :)

Leave a Reply

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