MySQL max_connections reached maximum – How we solve it
Is your website showing MySQL max_connections reached maximum limit error? We can help you.
Usually, insufficient MySQL max_connections value will give a “Too many connections” error when we try connecting to the MySQL server.
At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.
Today, let’s see how our Support Engineers fix this error for our customers.
What are MySQL max_connections?
MySQL max_connections are the number of simultaneous client connections that the server can accept.
The default value for this is 151. But, there is an extra default connection on top of the max_connections limit.
Generally, this is reserved for the database user having SUPER privilege for diagnosing connection problems.
Value 151 doesn’t mean that only 151 number of connections are allowed.
When there is a sudden surge in MySQL requests, typically due to heavy traffic, the server will not be able to handle them. As a result, websites start showing the MySQL max_connections reached maximum error.
Arriving at the right max_connections value
Let’s now check how our MySQL Engineers calculate the ideal value for maximum connections on a server.
Usually, the number of max connections depends on available RAM and memory usage for each connection. And, is calculated as:
max_connections = (Available RAM - Global Buffers) / Thread Buffers
Here, Global Buffers include the following key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size.
And, Thread Buffers include: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
Additionally, our Engineers consider the various MySQL queries, type of application, etc. while increasing the max_connection value.
How we fix MySQL max_connections reached maximum error?
Recently, one of our customers approached us with the request that his portal went unresponsive. So, we checked the customer’s server and there was a high load.
So, we did a deep check to find out the cause of such high load. We checked the access log.
Our Engineers found that max_connections were too low for handling such a high number of connections. So, we increased the max_connections as shown:
And, restarted the MySQL service.
Finally, this fixed the problem.
Moreover, choosing the appropriate value for max_connections is not based on a fixed rule because it depends on our server workload.
[Having trouble with MySQL server? Our Engineers can fix it now!]
In short, inadequate configuration of database server often result in MySQL max_connection reached maximum error. Today, we discussed in detail on MySQL max_connection and saw how our Support Engineers tweak MySQL parameters to avoid errors.