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.