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

When max_connections is full, the usual response is to raise it.

If you raise it before finding out what is using the connections, 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, raising the limit turns a clean, fast rejection into a slow server for everyone.

Check the current connections first. This 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, so that 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 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;

The arithmetic usually explains the incident. Twelve application instances each holding a pool of 20 is 240 connections against a max_connections of 200, and nothing is misbehaving. Nobody did the arithmetic. 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 rather than the session with the highest activity:

KILL 12345;

KILL on a connection inside a transaction rolls that transaction back, and the rollback can take several times 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;

The setting reverts on restart. Persist it 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 close 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. Change it carefully: a pool expecting long-lived connections will start seeing closures it may not retry cleanly.

FAQ

Section titled “FAQ”
  • Should I just raise max_connections?

    Only after you know what is using the connections you have. Every connection costs memory and every active one competes for CPU, so raising the ceiling on a server that is already saturated turns a clean rejection into a slow server for everyone. Raise it when the count is legitimate and the server has headroom.

  • Why are so many connections in the Sleep state?

    Sleep means an application is holding an open connection while doing nothing with it. That is normal for a connection pool. It becomes a problem when several application instances each hold a large pool, and the total quietly exceeds max_connections.

  • What is the reserved super connection?

    MySQL holds one connection slot back for a user with CONNECTION_ADMIN or SUPER, so an administrator can still get in when the server is full. Log in as an admin user, not the application user, or you will be refused along with everyone else.

  • Does lowering wait_timeout drop active work?

    No. It only closes connections that have been idle for that long, and a connection idle mid-transaction still holds its locks until it is closed. Lower it carefully, because a pool that expects long-lived connections will see errors it does not retry.

Section titled “Related”