Quickops 3: too many connections to my database and what can i do

From time to time you may try to connect to your database cluster and receive an error similar to this:

mysqli_connect(): Too many connections

In this case, new services that try to connect to your database may start failing, and even your admin users will not get to the cluster.

So, How are we going to solve this issue?

In my experience you have one of the 3 following options:

• Restart the cluster or service: This can provide potential risks of data loss and it doesn't even resolve the issue or provide useful information.
• Wait unit a connection is releases and connect to your database. This could be a quick solution or it can take forever.
• Check the services that connect to the cluster and release connections. Under this approach you need to review applications configuration's files and look for a service that is not critical and stop it by a moment so you can connect to the cluster with your admin user.

Now, suppose you manage to get a connection and enter the server or cluster.

The following query will provide you with information about users connected to the system and how many connections each user have:

select user, COUNT(*) as c from information_schema.processlist GROUP BY user ORDER BY c DESC LIMIT 10;

Or this one:

select user,count(*) as connections from INFORMATION_SCHEMA.PROCESSLIST group by user order by connections;

You can even see the status of the connections:

SELECT state, COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY state;

This will give you the usernames that are consuming the majority of connections.

But we can go further and increase the connection count

mysql> show variables like "max_connections";
--------------
show variables like "max_connections"
--------------
show variables like "max_connections"
--------------
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100  |
+-----------------+-------+
1 row in set (0.08 sec)

set global max_connections = 500;

Consider that connections increase resources consumption so special care needs to be taken.

This second part is not a good approach...
Why?

• Suppose there is a service that, after a new version release, opens connections to our database but it never closes the connections.
• Even if you have more connections slots available eventually you will have this problem again.

So, A better approach is to:

1. Use the information obtainer to detect the service or services that are consuming most connections.
2. Review the service config files to detect the amount of connections that the service should use or what is the size of the pool configured.
3. If it is too high. Talk with the team to see if all that connections are necessary, or if it can be lowered down.
4. In case the connection pool size is a small number, contact the team it is probably a bug in the software.

If you are in an emergency and needs to release resources you can either stop a service or use this commands:

show full processlist;

 #Find a process that you can kill

kill <PID>;

Care with killing processes this is not a best practice until you have a better diagnose of the issue.

Comments

[darwinpasco25]

If you have too many connections to your database, you should consider if your application is doing too many roundtrips to the database. You should design your database in such a way that it minimizes if not eliminate the need to do roundtrips.

1. Make sure your tables are properly normalized. In most cases, this is where the actual problem will be.
2. Always use set operation. You can use stored procedures that accept json or xml as parameters. Yo can read json and xml as table so you can do bulk insert, update or even delete(if advisable).
3. Output nested records as json or xml so the application wont have to call multiple queries to fetch related records
4. Check if your queries are optimized. Check the execution plan. Check the indexes. If the tables are not properly normalized, chances are the queries also aren't optimized and needs a lot of logic and data manipulation to come up with a result. The faster your queries execute, the sooner it can release the connection. Most cases of too many connections are due to poor query performance.

[Ernesto Lopez]

Yes, thanks a lot for your answer, totally agree with you, in fact this post is if didn´t work with the best practices and you are in a trouble because your db cannot receive connection. It is a great comment thanks!