TODDBLOG/ posts/ how to keep an ovrloaded db running

Here is my new trick to keep a website running when mysql is overloaded:

Assuming you can narrow down your problem to a bad query on the site and you have no sensible way to disable what is causing it [or if you don't know the source of the bad queries] you can kill queries by regex using something like this

MYSQL="mysql -u root --password=passwd"
BAD_QUERY_REGEX=".*select * from users limit 100000.*"

while `true`; do 
  for x in echo 'show processlist'|$MYSQL|grep $BAD_QUERY_REGEX|awk '{ print $1 }'; do
    echo kill $x | $MYSQL
    done
  sleep 2
done

This will kill any query matching BAD_QUERY_REGEX every 2 seconds. This technique is valuable when some part of a site is killing the whole site because of bad queries. It's not immediately obvious that you can do this to save a site.

tech