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.
To test: Reload all open Tabs in Firefox
Wordpress default Database Error dialog
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.
Digg's Custom Error Page
Twitter's Custom Error Page
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.
<?php header('HTTP/1.1 503 Service Temporarily Unavailable'); header('Status: 503 Service Temporarily Unavailable'); mail("youremail@email.com","Blog DB Error","There is a problem with the db!","From: yourblog@email.com"); ?> <!DOCTYPE HTML> <html dir="ltr" lang="en-US"> <head><title>503 Service Temporarily Unavailable</title></head> <body style="width:600px;margin:auto;margin-top:50px;"> <img src="http://yourblog.com/logo.jpg" /> <h1>Looks like we're getting a lot of database requests.</h1> <p><a href="javascript:location.reload(true)"> Click to refresh and try load the blog again.</a></p> </body> </html>
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.
That’s very cool, I’ve been wanting to make a custom database error page since my HostGator hosting seems to have hiccups pretty often lately.
If I use WP SuperCache, will my blog work even if MySQL server is down? Provided there’s a cached version of the page of course…
Andrew: Yes, if you use WP SuperCache, and the page is cached, your blog will work. If MySQL is down, any comments posted will not be saved however. You could use disqus to manage your wordpress comments if you wanted.