MySQL Error: Too many connections

MySQL Error: Too many connectionsEditor’s Note: Follow up to this post here MySQL 5.7 Performance Tuning After Installation.

We have always received quite a few questions here at Percona Support on how to avoid the dreaded “Too many connections” error, as well as what is the recommended value for max_connections. So, in this article, I will try to cover the best possible answers to these questions so others can mitigate similar kinds of issues.

My colleague Aurimas wrote a wonderful post some time back about changing max_connections value via GDB when MySQL server is running to get rid of the “Too many connections” error without restarting MySQL.

By default 151 is the maximum permitted number of simultaneous client connections in MySQL 5.5. If you reach the limit of max_connections you will get the “Too many connections” error when you to try to connect to your MySQL server. This means all available connections are in use by other clients.

MySQL permits one extra connection on top of the max_connections limit which is reserved for the database user having SUPER privilege in order to diagnose connection problems. Normally the administrator user has this SUPER privilege. You should avoid granting SUPER privilege to app users.

MySQL uses one thread per client connection and many active threads are performance killer. Usually, a high number of concurrent connections executing queries in parallel can cause significant slowdown and increase chances for deadlocks. Prior to MySQL 5.5, it doesn’t scale well although MySQL is getting better and better since then – but still, if you have hundreds of active connections doing real work (this doesn’t count sleeping [idle] connections) then the memory usage will grow. Each connection requires per thread buffers. Also implicit in memory tables require more memory plus memory requirement for global buffers. On top of that, tmp_table_size/max_heap_table_size that each connection may use, although they are not allocated immediately per new connection.

Overly High Number of Connections

Most of the time, an overly high number of connections is the result of either bugs in applications not closing connections properly or because of wrong design, like the connection to MySQL is established, but then the application is busy doing something else before closing MySQL handler. In cases where an application doesn’t close connections properly, wait_timeout is an important parameter to tune and discard unused or idle connections to minimize the number of active connections to your MySQL server – and this will ultimately help to avoid the “Too many connections” error. Although some systems are running alright with even a high number of connected threads, most of the connections are idle. In general, sleeping threads do not take too much memory – 512 KB or less. Threads_running is a valuable metric to monitor as it doesn’t count sleeping threads – it shows active and the number of queries currently processing, while threads_connected status variables show all connected threads value including idle connections as well.  Peter wrote a nice post on it. You can find it here for further details on it.

If you are using connection pool on the application side, max_connections should be bigger than max connections. Connection pooling is also a good alternative if you are expecting a high number of connections.

Recommended value for max_connections

It depends on the amount of RAM available and memory usage for each connection. Increasing max_connections value increases the number of file descriptors that mysqld requires. Note: there is no hard limit to setting up maximum max_connections value. So, you have to choose max_connections wisely as per your workload, number of simultaneous connections to MySQL server etc. In general, allowing too high of a max_connections value is not recommended because in case of some locking conditions or slowdowns if all those connections running huge contention issue may raise.

In the case of active connections using temporary/memory tables, memory usage can go even higher. On systems with small RAM or with a hard number of connections control on the application side, we can use small max_connections values like 100-300. Systems with 16G RAM or higher max_connections=1000 is a good idea, and per-connection buffer should have good/default values while on some systems we can see up to 8k max connections, but such systems usually became down in case of load spikes.

To deal with it, Oracle and the MariaDB team implemented thread pool. Percona Server ported this feature from MariaDB. Read it here about its implementation in Percona Server. With a properly configured thread pool you may expect throughput to NOT decrease for up to a few thousand concurrent connections, for some types of workload at least.

NOTE: Beware, that in MySQL 5.6 a lot of memory is allocated when you set max_connections value too high. Check this bug report

There is no fixed rule to choose the appropriate value for max_connections because it depends on your workload. Take into account that each thread connected needs memory and expensive context switching. I would recommend choosing a reasonable number for max_connections as per your workload and try to avoid too many connections opened at the same time so the application functions properly.

Share this post

Comments (13)

  • Sohail Ahmed

    Excellent article, Irfan. I used to get the same too many connections error on my forum but I could never understand it well before.

    November 28, 2013 at 6:47 am
  • Chandan

    Nice Observation Irfan……..This will help us to understand more about MySQL Internals

    November 28, 2013 at 8:53 am
  • Nick

    Thanks for the article. Have you ever seen this error resulting from too many connections:

    (HY000/1135): Can’t create a new thread (errno 11); if you are not out of available memory, you can consult the manual for
    a possible OS-dependent bug

    It’s something we’ve been seeing intermittently and has been a pain to trace. Server doesn’t seem to be running out of memory and it not swapping, have increased file descriptors (ulimit nofile/nproc). It has really got me stumped.

    November 28, 2013 at 2:49 pm
  • Nickolay Ihalainen

    Usually the “errno 11” issue caused by PAM limit nproc:
    When you increasing max_connections you still should change OS limits.

    November 29, 2013 at 1:31 am
  • Ahmad Sabih

    Amazing Article. We faced this problem too many times…

    November 30, 2013 at 5:00 pm
  • Nicky Helmkamp December 5, 2013 at 3:59 pm
  • Masroor Farooqi

    Great job of explaining the issue and it’s resolution.

    You are right that most of the time I have seen symptoms like this, it’s because of upstream issues not the database itself. Sometimes (very rarely) you will see the load causing it it but never take that as the first explanation of why you are seeing too many connections.

    The only way I have seen to avoid this issue in a steady state system is to have the app do some sort of connection pooling. The problem is that connection pooling is relatively easy in java, but surprisingly difficult in things like php which, for whatever reason, wants to do a connect->query->disconnect cycle.

    December 6, 2013 at 2:32 pm
  • Zaid Rashwani

    Thanks Muhammad,
    great article about an issue we face frequently..

    December 18, 2013 at 3:24 am
  • aftab

    Monitoring is a must to know such as:

    SHOW GLOBAL STATUS LIKE ‘Max_used_connections’;

    This would help you see maximum number of connections that have been in use simultaneously since the server started. If you have monitoring tool (such percona monitoring plugins or MySQL enterprise) you could see what times you see large number of concurrent connections or trying using pt-stalk to collect diagnostics data when threads_connected is higher than N.

    Please don’t reduce wait_time or interactive_timeout too low, because if your application use long transactions then it may abort transactions in the middle.

    January 23, 2014 at 6:20 am
  • Roshan Mathwale

    I am getting this exception.
    I am processing a large database ,several database interactions are successful but after that I am getting this Exception.
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

    May 7, 2015 at 7:10 am
  • Anirudh Chauhan

    Hi Irfan,

    in my environment we are working with test databases and when application hits database with 15000, 31000 users we found all are null queries and
    it crossed the max connection limit 400 which is set in my.cnf, now application team is saying increase your max connection limit from 400 to 800, so please suggest what will you suggest and what infra i.e CPU and RAM is required to ser connection limit 800.

    September 13, 2016 at 3:18 am
  • sisca c

    Today my site error on connection database sql ( too many connection ) i dont know how to fix it.. thats i should upgrade bandwith connection? or that was DDOS on webhost?

    September 5, 2017 at 10:24 pm
    • sachinaravind92

      Hi Sisca, you run the comment netstat | grep “mysql” | wc -l as root user and see the existing active connections to mysql DB, then see whether that is utilizing the max_connections configured in /etc/mysql.cnf file. If it is utilizing the maximum connections configured, then try updating the value to a higher value with respect to the available RAM in the system (free -m).

      March 20, 2019 at 7:33 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.