How to fix MySQL ‘Too many connections’ error in your server?

0
149

A few questions have always been asked about how to prevent the dreaded “Too many connections” mistake and what is the suggested value for max connections. So let’s attempt to cover the best possible responses to these questions in this article so that others can mitigate comparable problems.When MySQL server runs to get rid of the “Too many connections” mistake without restarting MySQL, an expert wrote a fantastic article some time ago about altering max connections value via GDB.

The highest amount of concurrent client links allowed in MySQL 5.5 is by default 151. If you achieve the max connections limit, if you attempt to link to your MySQL server, you will get the “Too many connections” mistake. This implies that other customers are using all accessible links. MySQL allows one additional link to the max connections limit reserved for the SUPER privilege database user to diagnose link issues. This SUPER privilege is normally given to the administrator user. App users should not be granted SUPER privilege. MySQL utilizes one thread per client link and efficiency kills many active threads. Usually, a large amount of simultaneous links running parallel queries can trigger important slowdown and boost the likelihood of deadlocks. It doesn’t scale well before MySQL 5.5, although MySQL has since improved and improved–but still, if you have hundreds of active contacts doing real work (this doesn’t count sleeping [idle] connections) then memory utilization will increase. Every link needs buffers per thread. More memory plus memory requirement for worldwide buffers are also implicit in memory tables. In addition, tmp table size / max heap table size can be used by each connection, although they are not assigned by fresh connection instantly.

Overly high number of connections

Most of the moment, an excessively large amount of links lead either from bugs in apps that do not close links correctly or from incorrect design, such as establishing a link to MySQL, but then the application is busy doing something else before closing MySQL handler. In cases where an application does not properly close connections, wait timeout is an important parameter for tuning and discarding unused or idle connections to minimize the number of active connections to your MySQL server, which ultimately helps to avoid the error of “Too many connections.”While some systems run smoothly with even a large amount of linked threads, most connections are idle. Sleeping threads generally don’t take too much memory–512 KB or less. Threads running is a useful measurement to track as it does not count sleeping threads–it displays active and the amount of queries presently being processed, while threads connected status variables display all linked threads including idle links.  Peter wrote on it a good post. For more information on it, you can discover it here. Max connections should be larger than max connections if you are using connection pool on the application side. Connection pooling is also a useful option if a large amount of links are expected.

Recommended value for max connections

It relies on how much RAM is accessible for each link and how much memory is used. Increasing the importance of max connections improves the amount of file descriptors required by mysql performance improvement. Note: the setting of maximum max connections value does not have a hard limit. You must therefore choose max connections wisely as per your workload, number of concurrent MySQL server contacts, etc. In particular, it is not suggested to allow too elevated a max connections value because, in the event of some locking circumstances or slowdowns, all those connections that run enormous contention problem may result.

Memory use for MySQL Performance Optimization may be even greater in the event of active links using temporary / memory tables. We can use tiny max connections values like 100-300 on tiny RAM devices or with a difficult amount of connections control on the implementation side. Systems with 16 G RAM or greater max connections=1000 is a nice concept, and per-connection buffer should have good / default values, while we can see up to 8k max links on some devices, but such systems generally dropped when load spikes occur. To cope with it, thread pool was introduced by Oracle and the MariaDB team. This function was ported by Percona Server from MariaDB. Read it here about its Percona Server execution. You can expect throughput with a correctly configured thread pool to NOT reduce for up to a few thousand concurrent links, for at least some kinds of workload. Beware, if you set the value of max connections too high, a lot of memory is allocated in MySQL 5.6.