Are you getting a MySQL error: too many connections? This error occurs when the MySQL server reaches its maximum allowed number of client connections. Let's look at precisely what the "too many connections" MySQL error means and what you can do to resolve it.
Understanding the Error
The “Too many connections” error is a common issue when a MySQL server reaches its maximum allowed number of client connections. This error can be frustrating, especially when it happens unexpectedly. To understand the error, knowing how MySQL handles connections is essential. When a client requests a connection to the MySQL server, the server allocates resources to handle the request. If the number of concurrent connections exceeds the maximum allowed limit, the server returns an error message indicating that too many connections are open.
MySQL Error “too many connections”: What’s the Problem?
When logging into MySQL, you may be occasionally rejected and receive an error message like “too many connections“. This means that the maximum number of clients that can connect to the server has been reached. Either one client will have to wait for another to log off, or the administrator will have to increase the maximum number of connections allowed.
Information about maximum allowed database connections to a server can be found using the SHOW VARIABLES statement:
$ mysql –u root –p
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 50 |+-----------------+-------+1 row in set (0.00 sec)
Currently occupied connections can be found by the following statement:
mysql> SHOW GLOBAL STATUS LIKE 'threads_connected';
+-------------------+-------+| Variable_name | Value |+-------------------+-------+| threads_connected | 50 |+-------------------+-------+1 row in set (0.00 sec)
If the number of threads_connected value is the same as max_connections, then MySQL server returns “too many connections” error messages to the client. That is:
Number of new connections allowed = max_connections - threads_connected = 50 - 50 = 0We can see how many connections are currently active by executing the following statement:
mysql> SHOW GLOBAL STATUS LIKE 'threads_running';
+-----------------+-------+| Variable_name | Value |+-----------------+-------+| threads_running | 10 |+-----------------+-------+1 row in set (0.00 sec)
This means that 40 simultaneous connections (i.e., threads_connected - thread_running = 50 - 10 =40 ) are idle in the database.
First, you should ensure that your applications close 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.
Causes of Connections Errors
Connection errors can occur for various reasons. One of the primary causes is the default connection limit set by MySQL, which is 151 connections, which may not be sufficient for high-traffic applications. Another reason is unoptimized applications that do not close connections properly or open excessive connections. Sudden traffic spikes can also lead to connection errors, as the server may be unable to handle the increased load. Additionally, resource constraints, such as shared hosting or servers with limited resources, can contribute to connection errors.
MySQL Error “too many connections”: Solution
To fix the "too many connections" errors you might face, we can increase the value of the max connections parameter by runtime (temporarily) and permanently set its value in the MySQL configuration file.
Increasing max_connections temporarily
To change this variable temporarily while the server is running, enter the following SQL statement:
$ mysql –u root –p
mysql> SET GLOBAL max_connections = 300;
Note: It’s not necessary to have a currently connected client disconnect to run this SQL statement as root. The server permits one more connection than the max_connections value. One connection is reserved for the root user or any other accounts that have SUPER privileges.
When the MySQL server daemon (mysqld) is restarted, the value above will set back to the previous value that exists in my.cnf or my.ini file. To make permanent changes, use the method described here:
Increasing max_connections 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 a server restart occurs. Assuming you are using /etc/my.cnf file, add the below line to it.
# vi /etc/my.cnfmax_connections = 300
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
Best Practices for MySQL Configuration
To avoid connection errors and ensure optimal MySQL performance, follow these best practices for MySQL configuration:
- Monitor connections
- Optimize applications
- Adjust max_connections
- Configure wait_timeout
- Implement connection pooling
- Regularly review configuration
- Consider scaling
By following these best practices, you can minimize the occurrence of connection errors and ensure your MySQL server runs smoothly and efficiently.
Ready for a MySQL upgrade? Try SingleStoreDB
SingleStoreDB is a real-time, distributed SQL database that unifies transactions and analytics in a single engine to drive low-latency access to large datasets, simplifying the development of fast, modern enterprise applications.
Built for developers and architects, SingleStoreDB delivers 10-100 millisecond performance on complex queries — all while ensuring your business can effortlessly scale.
SingleStoreDB is MySQL wire compatible and offers the familiar syntax of SQL, but is based on modern underlying technology that allows infinitely higher speed and scale versus MySQL. This one of the many reasons that SingleStore is the #1 top-rated relational database on TrustRadius. Sign up for a free trial or contact our sales team today for more details on how to get started.
For more information on how SingleStore is related and can turbocharge your MySQL, visit our MySQL page.
Additional Resources
