The Geek Diary

MySQL Error “Too many connections” and how to resolve it

by

Problem

When a client tries to log into MySQL it may sometimes be rejected and receive an error message saying that there are “too many connections“. This means that the maximum number of clients that may be connected to the server has been reached. Either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed.

Information about connections to a server can be found using the SHOW STATUS statement:

$ mysql –u root –p
SHOW STATUS LIKE 'max_used_connections';

First, you should ensure that your applications are closing connections to the server when they are no longer needed. However, you can solve this error by increasing the value of the max_connections variable and possibly decreasing the value of wait_timeout if you expect that many of the connections to your server are not being actively used.

Solution

The maximum number of connections threads allowed for the server is contained in the system variable max_connections. The default value is 151. To see the value to which this variable is set, run the following SQL command:

$ mysql –u root –p
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

Changing the max_connections parameter (Temporarily)

To change the value of the system variable max_connections, the –max_connections option can be used. To change this variable temporarily while the server is running, enter the following SQL statement:

$ mysql –u root –p
mysql> SET GLOBAL max_connections = 512;
Note: It’s not necessary to have a client which is currently connected to the server disconnect to run this SQL statement as root. The server permits one connection more than the value of the max_connections. One connection is reserved for the root user or any other accounts that has SUPER privilege.

When the MySQL server daemon (mysqld) is restarted the above value will set back to the default value of 151. To make changes permanent use the below method.

Changing the max_connections parameter (Permanently)

A better method to change max_connections parameter would be to add this option to the options file (my.cnf or my.ini, depending on your system) so that it takes effect next time the server is restarted. Assuming you are using /etc/my.cnf file, add the below line to it.

# vi /etc/my.cnf
max_connections = 512

Now restart the mysqld daemon for the changes to take effect.

For CentOS/RHEL 6:

# service mysqld restart

For CentOS/RHEL 7:

# systemctl restart mysqld

What is the Maximum Value for max_connections on Linux?

The upper limit for how large you can configure max_connections is largely determined by the operating system in addition to the limit of 100,000 that MySQL sets. Linux has a limit called max open files, this is defined “per login” and says the maximum number of files a process can open. The default is 1024 (which you can see using ulimit -n).

You May Also Like