Quickops 3: too many connections to my database and what can i do 
Tags: mysql, troubleshoot, database, systems
Published: 2021-12-23 23:24:15

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.