September 2, 2014

MySQL Error: Too many connections

MySQL Error: Too many connectionsWe have always received quite 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 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. You can check here for details.

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 – which 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.

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 amount 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 on the pool side. Connection pooling is also a good alternative if you are expecting a high number of connections. Now what should be the recommended value for max_connections ?

There is no single right answer for that question. 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 case of active connections using temporary/memory tables memory usage can go even higher. On systems with small RAM or with 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, of course 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 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 http://bugs.mysql.com/bug.php?id=68514

Conclusion:
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.

About Muhammad Irfan

Muhammad Irfan is vastly experienced in LAMP Stack. Prior to joining Percona Support, he worked in the role of MySQL DBA & LAMP Administrator, maintained high traffic websites, and worked as a Consultant. His professional interests focus on MySQL scalability and on performance optimization.

Comments

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

  2. Chandan says:

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

  3. Nick says:

    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.

  4. Nickolay Ihalainen says:

    Nick,
    Usually the “errno 11″ issue caused by PAM limit nproc:
    http://www.mysqlperformanceblog.com/2013/02/04/cant_create_thread_errno_11/
    When you increasing max_connections you still should change OS limits.

  5. Amazing Article. We faced this problem too many times…

  6. Masroor Farooqi says:

    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.

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

  8. aftab says:

    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.

Speak Your Mind

*