Preventing MySQL Error 1040: Too Many Connections

One of the most common errors encountered in the MySQL world at large is the infamous Error 1040:

ERROR 1040 (00000): Too many connections

What this means in practical terms is that a MySQL instance has reached its maximum allowable limit for client connections.  Until connections are closed, no new connection will be accepted by the server.

I’d like to discuss some practical advice for preventing this situation, or if you find yourself in it, how to recover.

Accurately Tune the max_connections Parameter

This setting defines the maximum number of connections that a MySQL instance will accept.  Considerations on “why” you would want to even have a max number of connections are based on resources available to the server and application usage patterns.  Allowing uncontrolled connections can crash a server, which may be considered “worse” than preventing further connections.  Max_connections is a value designed to protect your server, not fix problems related to whatever is hijacking the connections.

Each connection to the server will consume both a fixed amount of overhead for things like the “thread” managing the connection and the memory used to manage it, as well as variable resources (for instance memory used to create an in-memory table.  It is important to measure the application’s resource patterns and find the point at which exceeding that number of connections will become dangerous.

Percona Monitoring and Management (PMM) can help you find these values. Look at the memory usage patterns, threads running, and correlate these with the number of connections.  PMM can also show you spikes in connection activity, letting you know how close to the threshold you’re coming.  Tune accordingly, keeping in mind the resource constraints of the server.

Seen below is a server with a very steady connection pattern and there is a lot of room between Max Used and Max Connections.

Avoiding Common Scenarios Resulting in Overuse of Connections

Having worked in the Percona Managed Services team for years, I’ve had the first-hand opportunity to see where many businesses get into “trouble” from opening too many connections.  Conventional wisdom says that it will usually be a bad code push where an application will behave badly by not closing its open connections or by opening too many quickly for frivolous reasons.

There are other scenarios that I’ve seen that will cause this too even if the application is performing “as expected”.  Consider an application stack that utilizes a cache.  Over time the application has scaled up and grown.  Now consider the behavior under load if the cache is completely cleared.  The workers in the application might try to repopulate the cache in mass generating a spike that will overwhelm a server.

It is important to consider the systems that use the MySQL server and prevent these sorts of edge case behaviors or it might lead to problems.  If possible, it is a good idea to trap errors in the application and if you run into “Too many connections” have the application back off and slip for a bit before a retry to reduce the pressure on the connection pool.

Safeguard Yourself From Being Locked Out

MySQL actually gives you “breathing” room from being locked out.  In versions 5.xx the SUPER user has a +1 always available connection and in versions 8.xx there is a +1 for users with CONNECTION_ADMIN privileges.  However, many times a system has lax privilege assignments and maybe an application user is granted these permissions and consumes this extra emergency connection.  It is a good idea to audit users and be sure that only true administrators have access to these privileges so that if a server does consume all its available connections, an administrator can step in and take action.  There are other benefits to being strict on permissions.  Remember that the minimum privilege policy is often a best practice for good reason!  And not always just “security”.

MySQL 8.0.14+ also allows us to specify admin_address and admin_port to provide for a completely different endpoint, bypassing the primary endpoint and establishing a dedicated admin connection.  If you’re running a lower version but are using Percona Server for MySQL, you’ll have the option of using extra_port and extra_max_connections to achieve another way of connecting.

If you are able to log in as an admin account, you may be able to kill connections, use pt-kill to kill open connections, adjust timeouts, ban offending accounts, or raise the max_connections to free up the server.

If you are unable to log in, you may try to adjust the max_connection value on the fly as a last resort.  Please see Too many connections? No problem!

Use a Proxy

Another way to alleviate connection issues (or move the issue to a different layer in the stack), is to adopt the user of a proxy server, such as ProxySQL to handle multiplexing.  See Multiplexing (Mux) in ProxySQL: Use Case.

Limits Per User

Another variable that MySQL can use to determine if a connection should be allowed is max_user_connections.  By setting this value, it puts a limit on the number of connections for any given user.  If you have a smaller number of application users that can stand some limit on their connection usage, you can set this value appropriately to prevent total server connection maximum.

For instance, if we know we have 3 application users and we expect those 3 users to never individually exceed 300 connections, we could set max_user_connections to 300.  Between the 3 application users, only a total of 900 connections would be allowed.  If max_connections was set to 1000, we’d still have 100 open slots.

Another approach in this same vein that is even more granular is to limit connections PER USER account.  To achieve this you can create an account like this:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 10;

It is a good idea to limit connections to tools/applications/monitoring that are newly being introduced in your environment and make sure they do not “accidentally” consume too many connections.

Close Unused Connections

MySQL provides the wait_timeout variable.  If you observe connections climbing progressively over time and not in a spike (and your application can handle it), you may want to reduce this variable from its default of 28800 seconds to something more reasonable.  This will essentially ask the server to close sleeping connections.

These are just a few considerations when dealing with “Too many connections”.  I hope they help you.  You may also consider further reading on the topic in this previous Percona blog post, MySQL Error: Too many connections.

by Tate McDaniel via Percona Database Performance Blog