How does mysql determine max connections?

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

How does MySQL calculate max connections?

How many max connections can MySQL handle?

What causes MySQL too many connections?

What is a good max connections for MySQL?