One of the most common errors encountered in the MySQL world in general is the infamous error 1040:
Concretely, this means that a MySQL instance has reached its maximum authorized limit for client connections. Until the connections are closed, no new connections will be accepted by the server.
I would like to discuss some practical tips to prevent this situation or, if you are there, how to recover.
Accurately Tune the max_connections Parameter
This parameter defines the maximum number of connections that a MySQL instance will accept. The “why” considerations that you would even like to have a maximum number of connections are based on the resources available to the server and the patterns of application usage. Allowing uncontrolled connections can crash a server, which can be considered “worse” than preventing new connections. Max_connections is a value designed to protect your server, not to solve problems related to anything that hijacks 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 (eg memory used to create a table in memory. is important for measuring application resource models and finding the point at which exceeding this number of connections will become dangerous.
Percona Monitoring and Management (PMM) can help you find these values. Examine memory usage patterns, running threads, and correlate them with the number of connections. PMM can also show you peaks in connection activity, letting you know how close you are to the threshold. Adjust accordingly, keeping in mind the resource constraints of the server.
Below is a server with a very stable connection model and there is a lot of space between Max Used and Max Connections.
Avoiding Common Scenarios Resulting in Overuse of Connections
Having worked with Percona’s managed services team for years, I have had the first-hand opportunity to see where many companies are having “problems” opening too many connections. Conventional wisdom says that it will usually be a bad push of code where an application will behave badly by not closing its open connections or opening too quickly for frivolous reasons.
There are other scenarios I have seen that will cause this too, even if the application works “as expected”. Take a stack of applications that use a cache. Over time, the application has evolved and developed. Now consider the behavior under load if the cache is completely empty. Application workers can try to repopulate the cache in bulk by generating a spike that will overwhelm a server.
It is important to take into account the systems that use the MySQL server and to avoid this kind of borderline behavior or it could cause problems. If possible, it’s a good idea to trap errors in the application and if you encounter “Too many connections”, roll back the application and swipe a bit before trying again to reduce the pressure on the connection pool.
Safeguard Yourself From Being Locked Out
MySQL actually allows you to “breathe” against locking. In versions 5.xx, the SUPER user has a +1 connection always available 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 perhaps an application user is granted these permissions and consumes this additional emergency connection. It is a good idea to audit users and ensure that only real administrators have access to these privileges so that if a server consumes all of its available connections, an administrator can step in and take action. There are other advantages to being strict on authorizations. 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 a completely different endpoint, bypassing the main endpoint and establishing a dedicated administrator connection. If you are using an earlier version but you are using Percona Server for MySQL, you will be able to use extra_port and extra_max_connections to get another way to connect.
If you can log in as an administrator account, you may be able to delete connections, use pt-kill to delete open connections, adjust expiration times, ban incriminated accounts or increase max_connections to free the server.
If you are unable to connect, you can try adjusting 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 limits the number of connections for a given user. If you have a smaller number of application users who can support a certain usage limit for their connection, you can set this value appropriately to prevent the maximum total connection to the server.
For example, if we know that we have 3 application users and we expect these 3 users to never individually exceed 300 connections, we could set max_user_connections to 300. Between the 3 application users, only 900 connections in total would be allowed. If max_connections was set to 1000, we would always have 100 open locations.
Another approach in the same vein, even more granular, consists in limiting the connections by USER account. To do this, you can create an account like this:
It is a good idea to limit connections to tools / applications / monitoring newly introduced in your environment and to make sure that they do not “accidentally” consume too many connections.
Close Unused Connections
MySQL provides the wait_timeout variable. If you observe a gradual increase in connections over time and not in a peak (and your application can handle it), you may want to reduce this variable from its default of 28,800 seconds to something more reasonable. This will essentially ask the server to close the standby connections.
These are just a few considerations when dealing with “Too many connections”. I hope they will help you. You can also consider reading more on the subject in this previous Percona blog post, MySQL Error: Too Many Connections.
Leave A Comment