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.