In our role as Server administration specialists for server owners, database errors are something that that we resolve for them. A commonly reported one is the MySQL ‘Too many connections’ error.

Many server owners who have their sites running on MySQL database server, often come across this error in the websites or server:

ERROR 1040 (HY000): Too many connections

In the case of PHP based websites with MySQL database server in the backend, the error message can be something like:

PHP Warning: mysqli_connect(): (HY000/1040): Too many connections

MySQL ‘Too many connections’ – The causes and fix

When a MySQL server is initially installed, it will have 150 as the default value for maximum permitted number of simultaneous client connections. This value can be adjusted further in servers.

An additional admin connection is also possible to monitor these client connections. This extra connection is reserved for use by accounts that have CONNECTION_ADMIN or SUPER user privilege.

The number of connections is defined by ‘max_connections’ value. When the number of client connections exceed this value, MySQL shows ‘Too many connections’ error.

This can happen in multiple scenarios, and the fix for the error would vary with the actual cause. Here, we’ll see the various causes that trigger MySQL ‘Too many connections’ error.

1. High traffic to sites

Many a times, the web traffic can go high due to peak sales or promotions. If there number of connections allowed for your MySQL server is not enough, users will get “Too many connections” error in websites.

In such scenarios, we first examine the MySQL processes in the server and analyze the web server traffic to figure out which site is causing the issue.

If the traffic is valid, we tweak the maximum number of connections to the database that are allowed, and increase it to a value that is high enough to meet the website requirements.

For resource-intensive websites, sometimes increasing the connection limit alone would not be sufficient. We do an in-depth analysis and tweak various service parameters to handle this high traffic.

2. Web server attacks

Often, it has been seen that a sudden increase in website traffic and intermittent ‘Too many connections’ errors denote a flooding attack to the web server.

If the connection limit is increased without noticing or preventing the attack, it will eventually crash your server, as a flooding attack is aimed at exhausting server resources with too many requests.

Bobcares’ engineers detect such attack attempts proactively, and immediately pinpoint the source of the attack and block those IP addresses, before it crashes the server.

We examine the access logs, network connections to the server, malicious scripts and processes, etc. in the server, to figure out the depth and extent of the attack.

3. Poorly coded applications

Many websites have custom code and applications running on them. Many often, this code may contain bugs or can lead to infinite loops of MySQL queries, leading to too many connections.

Too many persistent connections to MySQL server or the application not closing connections properly, can also lead to MySQL ‘Too many connections’ error.

If the timeout for idle connections is too high, it can cause too many sleeping connections, and thereby use up the allowable limit of MySQL connections in the server.

By examining the MySQL processes and queries that are running, we figure out the code that is causing issue, and optimise it further to fix ‘Too many connections’ error.

MySQL ‘Too many connections’ fix – What to keep in mind!

One common question that comes into the mind of server owners, is what exactly should be the value of max_connections parameter for MySQL. The fact is that, there is no such specific value.

The number of allowable connections depends on the amount of RAM available and memory usage for each connection. Increasing the value too much can use up the RAM and cause server to crash.

At Bobcares, we examine the available memory in the server and other parameters that require the RAM, before going ahead and increasing the connection limit.

Other parameters such as the timeout settings, cache and open tables can also influence the memory utilised for MySQL and the connections possible.

By doing an iterative tweaking and examining the MySQL server performance and memory usage after each iteration, we configure the MySQL server to serve connections with maximum efficiency.

Conclusion

MySQL ‘Too many connections’ is a commonly seen, but confusing database server error. The major hurdle involved in fixing it, is finding the actual root cause of the error. From attacks to database server settings, we’ve seen the various causes for the error and how to fix it permanently.