Reduce Database Backup Size: Delete Akismet Blocked Spam
June 13th, 2007Does 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.
Subscribe RSS feed
That’s a good tip. Those thousands of spam comments surely occupy too much space. Gotta delete ‘em. Thanks for sharing…
Nice tip :D
Thanks for sharing. I currently have 8k Spam :O
Good tip.
Carmelo Lisciotto
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.
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?
My blog’s host (freehostia has blocked my site because their is spam comments containing key words that get you blocked automatically like p0rn stuff.)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.
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?
My blogs host (freehostia has blocked my site because their is spam comments containing key words that get you blocked automatically like p0rn stuff.)
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!
Wordpress must have changed this because I have 2.8 and there is no spam in my database.