WP Supercache works very nice and has a lot of config options including a preload to cache all wordpress pages. One of my blogs however recieves average traffic and that coupled with the fact that I use tla, I didn’t want to use the preload option. The problem was that a lot of (malicious spamming) bots hit the blog very infrequently and requested 10’s of pages in a second. This caused lots of mysql-slow queries and other database related issues and caused an OOM before on my VPS.
While there are a number of solutions and approaches, below is the one I have taken and proves to be very reliable.
Solution to malicious bots hitting wordpress blogs and preventing mysql issues
The blog in question had its own mysql database and as a result I could limit the number of simultaneous mysql connections made by the blog.
mysql -u root -h localhost -p mysql> show grants for myblog@'localhost'; mysql> grant usage on myblog.* to myblog@'localhost' with max_user_connections 5;
To test this, I opened 20 tabs in Firefox and went to a different page of the blog. I cleared the wp-supercache, logged out, and then right-clicked on one of the firefox tabs and went “refresh all”. All 20 tabs reloaded the different blog pages and posts. On some of the tabs I seen “Error establishing database connection”. This showed that the max_user_connections setting worked.
Custom MySQL Database error for WordPress
So on the rare occassion of a genuine reader seeing the “Error establishing a database connection”, I wanted a more friendly error with a retry option. Digg and twitter all have their own custom errors to cover such a scenario.
Fortunately wordpress has this already thought of this and has a special page which it can show if it fails on a database connection.
Location of Script: /wp-content/db-error.php
In my custom db-error.php script I do:
- Send a 503 Service Temporairly Unavailable (so search engines will not index the error page, and will recrawl the site again)
- Send an email to me so I can monitor how many of these errors occur
- Show a logo, some text, and a retry link.
Of course all going well, most users will recieve a wp-supercached page. This above is for internet bots who hit the blog 10’s and 100’s of times in the space of a second. I can also watch the email messages with the errors and verify the IP of who triggered it. I can then raise max_user_connections as needed to give more resources to the blog db without it overloading all of mysql on my VPS.
Note: this won’t affect google bot or any other genuine bots or web crawlers. It’s primarily aimed at bots who hammer wordpress blogs with many referrer URLs.