# How to fix "Too many connections" in MySQL

> **Quick answer:** `ERROR 1040` means `max_connections` is full. Raising it
> before you know what is holding the connections usually makes things worse,
> because every connection costs memory and every active one competes for CPU.
> Log in as an admin user, which has a reserved slot, then group
> `information_schema.PROCESSLIST` by user and host. The answer is normally
> several application instances each holding a pool. Fix the pool, not MySQL.

```
ERROR 1040 (HY000): Too many connections
```

`max_connections` is full. The reflex is to raise it.

**Raise it before you know what is holding the connections and you usually make
things worse.** Every connection costs memory whether it is doing anything or not,
and every *active* one competes for CPU. On a server that is already saturated,
lifting the ceiling converts a clean, fast rejection into a slow server for
everybody.

Find out what is actually there first. That takes about a minute.

## Get in when the server is full

MySQL reserves one connection slot for a user holding `CONNECTION_ADMIN` or
`SUPER`, precisely so an administrator can still reach a full server.

**Connect as an admin user, not as the application user**. The application user
has no reserved slot and will be refused with everyone else.

## What is the ceiling, and where are you against it?

```sql
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
```

`Max_used_connections` is the high-water mark since the server started. If it sits
just under `max_connections`, you have been close to this for a while and today
was simply the day you crossed.

## Who is holding them?

The important split is *doing work* versus *idle*:

```sql
SELECT COMMAND, COUNT(*) AS conns
FROM information_schema.PROCESSLIST
GROUP BY COMMAND
ORDER BY conns DESC;
```

A large `Sleep` count means an application is holding connections open while doing
nothing. That is normally a connection pool, which is fine until you multiply it:

```sql
SELECT USER, HOST, COUNT(*) AS conns
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY conns DESC
LIMIT 20;
```

**This is where the answer usually is.** Twelve application instances each holding
a pool of 20 is 240 connections against a `max_connections` of 200, and nothing is
misbehaving. The arithmetic was simply never done. Fix it in the pool
configuration, not in MySQL.

## Idle *in transaction* is the dangerous kind

A connection sleeping between queries is harmless. A connection sleeping **with a
transaction open** is holding locks and pinning InnoDB's undo history the entire
time:

```sql
SELECT
  t.trx_id,
  t.trx_started,
  TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS age_sec,
  t.trx_mysql_thread_id AS conn_id,
  p.USER, p.HOST, p.COMMAND, p.TIME,
  LEFT(COALESCE(t.trx_query, '(idle in transaction)'), 60) AS query
FROM information_schema.innodb_trx t
JOIN information_schema.PROCESSLIST p
  ON p.ID = t.trx_mysql_thread_id
ORDER BY t.trx_started;
```

`COMMAND` of `Sleep` with a row in `innodb_trx` and an age in the thousands is an
application that opened a transaction and then went off to do something else:
call an API, wait on a queue, or crash without closing cleanly. **That one
connection can block a schema change and grow undo history until disk becomes the
next problem.**

## Clearing the immediate blockage

Kill the specific offender, not the busiest-looking one:

<!-- sql-check: skip illustrative thread id; there is no connection 12345 to kill -->

```sql
KILL 12345;
```

`KILL` on a connection inside a transaction rolls that transaction back, and the
rollback can take as long as the work it is undoing. It is safe; it is not always
instant.

## Then, and only then, tune

If the connection count is legitimate and the server has memory headroom:

```sql
SET GLOBAL max_connections = 500;
```

**That is not persistent.** It reverts on restart. Make it stick in `my.cnf`, or in
the parameter group on RDS and Aurora. On managed platforms `max_connections`
is often a formula derived from instance memory, so check what the platform
computes before you fight it.

To reap genuinely abandoned connections sooner:

```sql
SHOW VARIABLES LIKE 'wait_timeout';
```

Lowering `wait_timeout` closes connections idle for longer than the limit. It does
not touch active work. Move it carefully: a pool expecting long-lived connections
will start seeing closures it may not retry cleanly.

## Related

- [How to find slow queries in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-find-slow-queries-in-mysql/)
- [How to add an index in MySQL without downtime](https://www.dbgorilla.com/learn/mysql/how-to-add-an-index-in-mysql-without-downtime/)
- [How to read a MySQL EXPLAIN plan](https://www.dbgorilla.com/learn/mysql/how-to-read-a-mysql-explain-plan/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)