November 2015
« Sep   Jan »

MySQL : Managing Sleeping / Active connections to avoid “too many connections” error

One the busiest MySQL server recently had an issue with “SQL Error 1040: Too Many Connections”. This server has already set 1000 connections limit which I believe quite enough to handle our platform. But we were getting frequent MySQL outage due to this kind of errors. We know that it’s a application leakage issue and can’t be find a permanent solution at MySQL server end.

This is directly cause to the value setup of these variables wait_timeout and inactive_timeout

variable represents the amount of time that MySQL will wait before killing an idle connection. The default wait_timeout variable is 28800 seconds, which is 8 hours

2. interactive_timeout : The number of seconds the server waits for activity on an interactive connection before closing it.

We will lost mysql connectivity when no. connection exceed the “max_connections” limit. So I have set a script for
1. Terminating all the sleeping process those are either running for a time period or flush out all the sleeping process when connection limits are getting reached.

# This script will terminate all the sleeping process which consuming more than 30 minutes. Also whitelising a host which is used for critical operations
# Sleeping time is set to 30 minutes and Web01 connections are excluded
> sleep.txt
mysql -e "show processlist" | grep -E "Sleep" | awk '{print $6,"  " $1,"  "  $3} '| cut -d ':' -f1 | sort  -nr  >> sleep.txt
while read sleep_array;

#echo " $name"
sleep_time=$(echo $name |awk '{print $1}')
pid=$(echo $name |awk '{print $2}')
host=$(echo $name |awk '{print $3}')

    if [ $sleep_time -ge 3600 ] && [ $host != "" ]; then
    #        echo "The process id to be killed $pid, it's time is $sleep_time, host name $host "
            mysqladmin kill $pid
            echo " "

done <sleep.txt

The following script will terminate all the connections when no. connections are reaching maximum connection limit.

Here is the download link. Set this script to run on every 2 minutes. This script will do the following items,
1. Whether the connection is meet the threshold limit and terminate all the process from MySQL memory.
2. Whitelist the connections from certain hosts
3. Provide the the report of Connections details of Sleeping and active sorted by host ip.
4. Send us the report via an email.

Cheers 🙂

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>