We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze. The freeze occurs because the query causes the number of database connections to go up to 400+ and basically anytime the site goes over 100 database connections the site slows down terribly and just doesn't respond.
We are running Amazon RDS using MySQL Red Hat Linux
We have a large enough EC2 on the front end app server, and a large enough RDS.
The way we are fixing this issue now is to find the offending query, and kill it. Once the query is killed...our database connections drop to around 20 which is the normal amount you see when monitoring the site statistics.
Is there a way to stop the offending query and kill it before it runs too long and consumes the connections? I am trying to automate the killing of the bad query before it happens, or at least realize after 30 seconds its a bad query and kill it.