July 2017 ~ Technology blog by Rathish kumar

Error Message:

ERROR 1040 (HY000): Too many connections

MySQL Too many connections

Reason for this error:

This error occurs when connection reaches the maximum limit as defined in the configuration file. The variables holding this value is max_connections

To check the current value of this variable, login as root user and run the following command:

show global variables like max_connections;

MySQL max_connections variable

Note: In above step, you can login using root, you may wonder, when it is showing Too many connection error, how can you login as root? The answer is, MySQL by default will consider maximum allowed connection as max_connection + 1 for SUPER user to fix this issue.

In our example - max_connection = 1, so MySQL will consider maximum allowed connection as 1 + 1 = 2.

Workaround 1:

SET GLOBAL max_connections = 100;

SET GLOBAL max_connections

Now login to MySQL, the too many connection error fixed. This method does not require server restart. After MySQL server restart, the max_connection variable value again roll back to previous value. In order to make the max_connection value persistent, modify the value in the configuration file.

Workaround 2:

Stop the MySQL server:

Service mysql stop

Edit the configuration file my.cnf.

vi /etc/my.cnf

Find the variable max_connections under mysqld section.

[mysql]

max_connections = 100

Set into higher value and save the file.

Start the server.

Service mysqld start

Note: use systemctl manager to stop and start the service if, service command not working.

I hope this post, will help you to fix the issue. If still you are unable to fix this issue, mention on the below comment section. I will get back to you fix this issue.

Update 1:

You can avoid this too many connection error by using this method. For example, 5 applications are connecting MySQL server. So, the calculations to set the max_connections value is as follows.

Total users = 5 applications = 5 Users

Concurrent connections = 200 (depends on your applications)

max_connections = 5 x 200 + 200 = 1200.

In case of all users are connecting, maximum it reaches 1000 users and still 200 connections are remaining. This example is only for our understanding. You should not implement this method on production system, we should have a detailed understanding of the application access to limit the max_connections value.

Update 2:

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