Mysql: Too many connections

ERROR 1040 (HY000): Too many connections

Oh no you ran out of connections on your mysql server. Unfortunately if you’ve come across this blog through a search engine the issue is happening right now and your options of surviving without user impact is limited. So before I talk about preventative measures to avoid crossing the mysql connection threshold, let me give as much advice as possible in dealing with your current outage.

If you’ve gone through the help files you know that mysql has one extra connection above the max connections you’ve set. If you haven’t played with the configuration the default is 100. Therefore you’re allowed 101 connections (Its 150/151 in mysql 5.1). The extra connection is for a user with super privileges. Assuming your application isn’t using an account with this privilege then you or one of your administrators has a connection open. Find it and up your max connection now. Find the statement to do this below.

Ok you can’t find it, or you can’t reestablish it. Your choice now is either to let these connections go stale or restart mysql. The default configuration on releasing a mysql stale connection is 28800 seconds or 8 hours. Your best option is to wait it out and let some of these bleed off. This assumes that there are some connections that went stale 7 hours ago or earlier. This is risky however considering any new connections trying to be established during this wait time will fail. Your other option is to restart mysql. Before going down this route understand the risk of corruption. If your system is an OLTP system there is a high risk of corrupting your data, during a shutdown. The reason being is the shutdown will probably not be clean, and Mysql is not the best at handling transactions. So before restarting the server brown out any processes executing DML statements whether it be ETL or user interaction.

Before moving on to preventative measures our network engineer guru Shane Foster has a script for finding network connections connected to mysql. This doesn’t solve the issue but it’s a good start for investigation, and understanding the count of connections

netstat -an | grep -e "3306" | grep -c -e "ESTABL"

Hopefully you’ve avoided the issue, and are now coming back to look at preventative measures. The obvious answer is to have more available connections then needed. The real answer is how to make sure you always maintain that buffer (No Pun intended). The first process is alerting. Whether through a monitoring system such as Zabbix or a hand rolled cron job you should have a threshold check. This can be dynamic or static. I’m not going to go through cool queries or scripts you can write as I only have my train ride commute to write this, rather here is a couple of simple queries to help you build upon, which leverage system tables added in 5.1

Find out how many connections allowed:

select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='MAX_CONNECTIONS';

Find out how many open Connections

select count(*) from information_schema.PROCESSLIST;

Find out the percentage of connections being used.

select count(*)/(select VARIABLE_VALUE from information_schema.GLOBAL_VARIABLES where VARIABLE_NAME='MAX_CONNECTIONS') * 100 from information_schema.PROCESSLIST;

Set connections ceiling dynamically

set global max_connections = 100;


All this is great for working within Mysql but this type of warning system usually work only if your connection number is slowly growing. Usually slow growth isn’t the problem; rather it’s caused by a sudden burst of activity, for example by a search engine spider crawl. With Java I’ve seen this in connection pooling where a lock on a table is issued, and no connections can access the object. Java in turn will start opening more connections, and hit the connection threshold. The queries above won’t help this situation, because it requires you connecting to the server to execute them. Rather you need to query the server and get denied, then alert on the denial. One way to do this is to create a smoke test script that connects to the server. If it fails it should consume the error message. If it matches
ERROR 1040 (HY000): Too many connections

Then you need to take action.

As I said above the best method to resolve this issue is to let stale connections bleed out. The issue you face here is that the default for mysql disconnecting a connection is 8 hrs of inactivity. This means if you hit the threshold you can wait hours to let it bleed out. The easy fix is to change this variable of 28800 seconds t

Save Now

o something smaller such as an hour. Below is the command that would change this to an hour.

set global interactive_timeout = 3600;