{"id":286,"date":"2011-05-09T19:25:17","date_gmt":"2011-05-09T18:25:17","guid":{"rendered":"http:\/\/sburke.eu\/blog\/?p=286"},"modified":"2022-07-25T23:27:40","modified_gmt":"2022-07-25T22:27:40","slug":"wordpress-caching-internet-bots-and-database-issues","status":"publish","type":"post","link":"http:\/\/sburke.eu\/blog\/2011\/05\/wordpress-caching-internet-bots-and-database-issues\/","title":{"rendered":"WordPress: Caching, Internet Bots and Database issues"},"content":{"rendered":"

\"\"<\/a>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 <\/a>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 <\/a>before on my VPS.<\/p>\n

While there are a number of solutions and approaches, below is the one I have taken and proves to be very reliable.<\/p>\n

Solution to malicious bots hitting wordpress blogs and preventing mysql issues<\/h3>\n

The blog in question had its own mysql database and as a result\u00a0I could limit the number of simultaneous mysql connections made by the blog.<\/p>\n

mysql -u root -h localhost -p\r\nmysql> show grants for myblog@'localhost'<\/a>;\r\nmysql> grant usage on myblog.* to myblog@'localhost'<\/a> with max_user_connections 5;<\/pre>\n
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.<\/p>\n
\"\"<\/a>
To test: Reload all open Tabs in Firefox<\/p><\/div>\n
\"\"<\/a>
Wordpress default Database Error dialog<\/p><\/div>\n
Custom MySQL Database error for WordPress<\/h3>\n
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.<\/p>\n
\"\"<\/a>
Digg's Custom Error Page<\/p><\/div>\n
\"\"<\/a>
Twitter's Custom Error Page<\/p><\/div>\n
\u00a0<\/h3>\n
Fortunately wordpress has this already thought of this and has a special page which it can show if it fails on a database connection.<\/p>\n
Location of Script: \/wp-content\/db-error.php<\/p>\n
In my custom db-error.php\u00a0script I do:<\/p>\n