Having up-to-date backups is an essential safeguard in case something goes wrong with your website. So some time ago I installed WP-DBManager and configured it to send a daily backup of my WordPress database to my GMail account. All was well, until last week the backup process failed five times in a row. Upon checking the PHP error log, I found several messages stating “memory limit exceeded”. What the…?
Turns out it was the comment spam.
This site gets hundreds of spam comments per day. Most of them are caught by Akismet, but even then they’re still stored in the database for 30 days. This can quickly add up to megabytes of worthless records. So when the backup plugin tries to process a database dump, it chokes on the (relatively generous) 32MB memory limit. To illustrate, here are some stats :
|Count||Average length||Used DB space|
|All legitimate comments||3577||250 bytes||0.85 MB|
(only the first half of April)
|4505||5528 bytes||23.75 MB|
Overall, spam comments accounted for more than half of the WP database size. After I deleted them the daily backup routine started working again.
Avoiding Spam Overload
Of course, cleaning up the database is only a temporary solution. If you have a semi-popular site, the spam comments can build up and you might encounter a problem similar to the one described above in the future. Here are some ideas on how to avoid that :
- Configure Akismet to automatically discard spam comments on old posts (there’s a checkbox in Plugins -> Akismet Configuration). This will significantly decrease the amount of diskspace wasted on storing spam.
- Increase the PHP memory limit so that it can cope with large databases. Effective, but it’s usually not an option for people on cheap hosting. Most shared hosts will lock the memory limit at 8 MB.
- Use an anti-spam plugin that doesn’t save detected spam comments. For example, WP-SpamFree is very effective and can work alongside Akismet. Personally, I’m not using it right now because of performance considerations, but that might change.
On a somewhat related note, McAfee recently released a study that demonstrates the environmental impact of e-mail spam. According to the study, unwanted e-mail leads to approximately 17 million tons of carbon dioxide being released as greenhouse gas each year.
Here’s a little calculation I did for fun. Lets assume that processing a spam comment “costs” the same amount* of CO2 as delivering a spam email (0.3 g). Now, multiply that by the amount of spam caught daily by Akismet (~17030800 comments today). The result : 1864 tons of carbon emissions are caused by comment spam each year. For perspective, this is how much carbon dioxide equivalent would be released by 360 passenger cars in the same time period. Scary.
* There is no reason to believe this is true 😉Related posts :