Skip to content

How to fix "Too many connections" in MySQL

Last updated

View as Markdown

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

Section titled “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?

Section titled “What is the ceiling, and where are you against it?”
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?

Section titled “Who is holding them?”

The important split is doing work versus idle:

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:

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

Section titled “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:

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

Section titled “Clearing the immediate blockage”

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

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

Section titled “Then, and only then, tune”

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

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:

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.

Section titled “Related”