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:Problem
$ 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:
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].
B.3.2.5 Too many connections
If clients encounter Too many connections
errors when attempting to connect to the mysqld server, all available connections are in use by other clients.
The permitted number of connections is controlled by the
max_connections
system variable. To support more connections, set max_connections
to a larger value.
mysqld
actually permits max_connections
+ 1 client connections. The extra connection is reserved for use by accounts that have the CONNECTION_ADMIN
privilege [or the deprecated
SUPER
privilege]. By granting the privilege to administrators and not to normal users [who should not need it], an administrator can connect to the server and use SHOW
PROCESSLIST
to diagnose problems even if the maximum number of unprivileged clients
are connected. See Section 13.7.7.29, “SHOW PROCESSLIST Statement”.
The server also permits administrative connections on a dedicated interface. For more information about how the server handles client connections, see Section 5.1.12.1, “Connection Interfaces”.
This issue occurs mostly when the maximum allowed concurrent connections to MySQL has exceeded. The max connections allowed is stored in the gloobal variable max_connections. You can check it by show global variables like max_connections;
in MySQL
You can fix it by the following steps:
Step1:
Login to MySQL and run this command: SET GLOBAL max_connections = 100;
Now login to MySQL, the too many connection error fixed. This method does not require server restart.
Step2:
Using the above step1 you can resolve ths issue but max_connections will roll back to its default value when mysql is restarted.
In order to make the max_connection value permanent, update the my.cnf file.
Stop the MySQL server: Service mysql stop
Edit the configuration file my.cnf: vi /etc/mysql/my.cnf
Find the variable max_connections under mysqld section.
[mysql]
max_connections = 300
Set into higher value and save the file.
Start the server: Service mysql start
Note: Before increasing the max_connections variable value, make sure that, the server has adequate memory for new requests and connections.
MySQL pre-allocate memory for each connections and de-allocate only when the connection get closed. When new connections are querying, system should have enough resources such memory, network and computation power to satisfy the user requests.
Also, you should consider increasing the open tables limit in MySQL server to accommodate the additional request. And finally. it is very important to close the connections which are completed transaction on the server.