Drupal site down - 500 Internal server error , blank page , heavy usage of resources

One of my drupal websites suddenly went into lot of issues 1) It became extremely slow and I had not done any changes to the website, and all my modules and scripts were upto date 2) It became inaccessible and was throwing 500 Internal server error and max database connection error. I had no clue of where to start debugging, since I was not able to access my admin panel also. This is how I went about solving the issue. The issue was resolved in two parts. First part was done and site was up then it again went down after 2 days. 

Day 1 

  1. Asked my hosting company to check and give me some pointers, they had no clue of what was going on. Just kept telling me that site is using too much resources and I should optimize my scripts. My reply was if the website is down how can it use so many resources , standard drupal installation does not come with such overheads and also I do not send any emails from my website.
  2. I cleared the cache tables by logging into phpmyadmin , still no luck. 
  3. Removed all contributed modules , site was up for few mins and down again. 
  4. I was told that the space on my hard disk is used up. I cleared up all the space and 1 GB was made free, but still no luck.
  5. I asked my hosting company to kill all the processess running, I believe they restarted the server and website loaded in a flash , we thought issue was resolved but in 2-3 mins again it was the same 500 internal server error. 
  6. However I luckily go into admin panel during this time and saw that there were hundreds of users who had registered even when the website was down. They were spam users and bots who were registering and posting on my website. This was using up all my resources, as their scripts were intense. By this time I was feeling very lucky that I have discovered the problem. 
  • I had disabled bad behaviour and user restrictions modules and hence these bots were able to get through. I enabled these modules again. Also tried to install honeypot module but I was not sure if that itself slows down the website, because the BOTS will keep filling the form , with honeypot, it will not really drive them away. So i removed honeypot for sometime.
  • Even After enabling above modules the website was still down. Somehow I managed to login and disable all user registration in Account settings page so only Adminstrator can create the account. Once I did this after few mins the website was up but it was still slow, and one can barely work. I was reading on drupal forums for solution and did few more things

Blocking the BOTS via .htaccess 

Set a cookie with mod-rewrite for anybody requesting resources.
 
# If they are requesting resources, then they're probably not bots.
RewriteCond %{REQUEST_FILENAME} (mytheme\.css|\.png)$ [NC]  # Replace mytheme with your theme name.
RewriteRule .* - [L,co=dude:abides:%{HTTP:Host}:86400]
 
Next check incoming POST's to see if they have that cookie set, if not, assume that they are most likely bots. This skips the index.php because the POST url's get redirected there.
 
# Check if this is a post method,
# If so, the human cookie must be set.
# If the dudes dont abide, they get a 403 for their POST.
RewriteCond %{REQUEST_METHOD} =POST
RewriteCond %{REQUEST_URI} !=/index.php [NC]
RewriteCond %{HTTP_COOKIE} !^.*dude.*$ [NC]
RewriteRule .* - [F]
 

Put in a fix to abort the Drupal bootstrap when this bot is detected. Add this in settings.php. Don't forget to replace example.com with the domain/subdomain you see in your own access log. Blocking individual IP address is not going to help as there are hundreds of them 

if ($_SERVER['HTTP_REFERER'] == 'http://example.com/user/login?destination=node/add') {
  if ($_SERVER['REQUEST_URI'] == '/user/register') {
    header("HTTP/1.0 418 I'm a teapot");
    exit();
  }
}

// This is for the POST variant, with either port 80 in 
// the referer, or an empty referer
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
  if ($_SERVER['REQUEST_URI'] == '/user/register') {
    switch($_SERVER['HTTP_REFERER']) {
      case 'http://example.com:80/':
      case '':
        header("HTTP/1.0 418 I'm a teapot");
        exit();
    }
  }
}

Read this post for details http://2bits.com/articles/another-botnet-spamming-drupal-web-sites-causing-performance-issues.html 

Important point to note here is , that none of the above gave me instant results. After doing all of the above , it took couple of hours for website to be back up. Since it was night time, I just decided to sleep over it and in the morning things were back to normal. However I was stuggling to find the root cause of this issue for a long time. Only when my website was down, we could try all this. 

BOTS are continously trying to register on the website and get past the CAPTCHA and other modules. This slows down the website especially if you are on shared hosting with limited resources. My website had only 1000 unique visitors per day, and because of this issue I was asked to move to semi-dedicated hosting, now even after moving to semi-dedicated the issue would not have solved because the real problem was BOTS slowing down the website and bringing it down rather than DRUPAL or any of its contributed modules taking up more resources. 

CAPTCHA is not effective in preventing the BOTS, even Image CAPTCHA does not prevent registrations. HoneyPot is a better solution but still it consumes some resources. We need to put the bots in blackhole and prevent them from consuming any more resources 

So will the users never be able to register again on my website ?

  • Users will be able to register again , but only genuine users, With above changes to settings.php, .htaccess, Mollom (Free version) and User Restrictions module, you have enough armour to prevent the BOTS from consuming your valuable resources.  If above does not work try BOTCHA module, it uses many spam protection recipes required to keep BOTS away. 

Day 4

The same problem again came on day4 . I had no clue of what to do now, Seems like I had tried every thing, I disabled user signup and cleard all cache , still no luck. My hosting company gave me an important clue.  There were many such queries running

| 38821838 | user| localhost | druupal database | Query | 52 | Copying to tmp table | SELECT f.tid AS tid, COUNT(n.nid) AS topic_count, SUM(ncs.comment_count) AS comment_count

| 38822062 | user | localhost | druupal database | Query | 43 | Copying to tmp table | SELECT f.tid AS tid, COUNT(n.nid) AS topic_count, SUM(ncs.comment_count) AS comment_count

He said above queries are taking up huge time and hence its timing out and giving Error 500 , even 60 seconds execution time is not enough to complete these queries. So now it seems copying to tmp table is consuming the resources and hence timing out and site shows blank page or error 500. Admin pages were accessible but very slow.

  • I read that we should increase the tmp table size  and heap table size. However with small site of mine I did not want to consume any more resources, I was convinced that there is some other problem
  • Removing views module from the modules folder, website was back up with limited features, so i thought that views module is the culprit. 
  • I thought "DISABLING CACHE" in Drupal >> Configuration >> Development>> Performance was the solution for me. As soon as I turned of caching and cleared cache tables the site was back up. , but then it was down again in few minutes.

Real Problem was with "Similar" module. This module used to query database and bring up similar entries of a given node. Till my traffic was less the queries were less, when the traffic increased, the module originates too many queries to compare and pull out similar entries.

This is one of the best modules I have ever come across. For my question answer related forum website this module used to pull out similar entries from past and most times after posting the question , user would see a Similar entry and their question would be answered. Unfortunately on Shared hosting with limited connection it does not work, and i had to turn of this module

Hope the above article if of help. Do Share with others.