MySQL "Too many connections" (Error 1040): How to Get Back In and Fix It

MySQL "Too many connections" is one of those errors that looks simple and usually isn't. The application logs fill with ERROR 1040 (HY000): Too many connections, the site starts returning errors, and when you try to log in to see what is going on, MySQL refuses you too. The quick fix everybody reaches for is a bigger max_connections. Sometimes that is right. More often the limit is only where the real problem becomes visible: something upstream is holding connections much longer than it should.

What the error actually means

MySQL has a fixed ceiling on simultaneous client connections, set by max_connections. When every slot is taken, new connections are rejected and the server increments the Connection_errors_max_connections status counter. The default in MySQL 8.4 is 151, with a range from 1 to 100,000. The effective value is also capped by the operating system: it is the lesser of what you set and open_files_limit minus 810. So if you raise max_connections and the file descriptor limit stays low, MySQL may quietly use a smaller number than the one in your config.

On managed services the default is different. Amazon RDS for MySQL derives it from instance memory, roughly one connection per 12 MB, which AWS says comes out at about 60 on a db.t3.micro and around 630 on an 8 GiB class.

Step 1: get yourself a session

You cannot fix a database you cannot reach. MySQL plans for this, as long as it was set up properly:

• The reserved slot. mysqld actually permits max_connections + 1 connections. The extra one is kept for accounts with the CONNECTION_ADMIN privilege (or the deprecated SUPER). That only works if your application user does not have that privilege. If the app connects as an admin-level account, it will grab the spare slot too, and you are locked out with everybody else.
• The administrative interface. Since MySQL 8.0.14 you can set admin_address and admin_port (default port 33062). The interface exists only if admin_address is set at startup, so it cannot be turned on in the middle of an incident. When it is enabled, it has no connection limit and accepts only users with SERVICE_CONNECTION_ADMIN. If you run self-managed MySQL, configure it now, while things are quiet.

If neither is available, your options get blunter: stop the application tier that is flooding the server, or restart MySQL. A restart throws away all in-flight work and the evidence of what went wrong, so treat it as the last resort, not the first move.

Step 2: look before you kill

Once you are in, don't start killing threads one by one from SHOW PROCESSLIST. Group them first, so the pattern is visible in a few rows:

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client,
       command, state, COUNT(*) AS conns, MAX(time) AS max_sec
FROM information_schema.processlist
GROUP BY user, client, command, state
ORDER BY conns DESC;

The result usually points to one of a handful of patterns:

• Hundreds of Sleep connections with large max_sec. The application opens connections and doesn't give them back. This is a connection leak, or connection pools that are far too large. The default wait_timeout is 28,800 seconds, so MySQL will keep an idle connection open for 8 hours before closing it on its own.
• Many sessions in Waiting for table metadata lock. One session holds a lock (often an ALTER TABLE waiting behind a long transaction, or an open transaction that someone forgot to commit) and everything else queues behind it. The queue fills the connection limit. The sys.schema_table_lock_waits view shows who is blocking whom.
• Many sessions actively running the same query. A slow query after a deploy, a missing index or a cache that just expired. Each request takes longer, so more of them are in flight at once, and the slots run out. Fix the query and the connection count drops on its own.
• One client host owns most of the connections. A batch job, a runaway worker or a retry loop. Stop it at the source.

Step 3: free slots without making it worse

Kill selectively, starting with what is idle and belongs to the application. Generating the statements lets you check the list before running it:

SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE user = 'app_user'
  AND command = 'Sleep'
  AND time > 300;

If sessions are waiting on a lock, kill the blocker, not the queue. Killing a hundred waiting sessions just makes room for a hundred new ones that will wait the same way. Be careful with sessions running long writes: killing them rolls the transaction back, and a big rollback can take as long as the work it undoes.

Then deal with the source. If the application retries instantly and in parallel, it will refill every slot you free within seconds. Scaling the app down or turning a noisy worker off for a few minutes is often the fastest fix there is.

How big should max_connections be?

There is no magic number. The MySQL manual says the practical maximum depends on the thread library, available RAM, memory used per connection, the workload and your response-time target. It notes that Linux should handle 500 to 1,000 simultaneous connections routinely, and up to 10,000 with many gigabytes of RAM and a light workload per connection.

A more useful way to size it is from the application side. Count every process that holds a pool: application servers, pods, workers, cron jobs, admin tools. Multiply each by its pool size, add a margin for deploys, when old and new instances run at the same time, and compare the result with the limit. Teams on Kubernetes are often surprised here: 40 pods with a pool of 20 is 800 possible connections, and autoscaling can double that in a few minutes.

If the honest total is far above what the server can handle, the answer is fewer connections, not a higher limit: smaller pools, or a proxy that multiplexes many client connections onto fewer server connections (ProxySQL for self-managed MySQL, RDS Proxy on AWS). It is the same idea we describe for PostgreSQL in our connection pooling guide.

Settings worth checking afterwards

• wait_timeout: 8 hours is a long time for an idle connection. Lowering it reduces leaked sessions, but set it above your pool's own idle timeout, or the app will run into connections the server has already closed.
• max_user_connections or a per-account limit with ALTER USER ... WITH MAX_USER_CONNECTIONS n: stops one service from using every slot and starving the rest. The global default is 0, which means no limit.
• open_files_limit and the systemd LimitNOFILE: make sure the operating system allows the number you configured.
• thread_cache_size: autosized by default to 8 + max_connections/100, capped at 100. If Threads_created grows quickly compared with Connections, many connections are getting new threads instead of reused ones.
• Privileges: remove CONNECTION_ADMIN and SUPER from application accounts so the reserved slot stays reserved.

Watch it before it fails

"Too many connections" almost never comes out of nowhere. The numbers to watch are all in one place:

SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_connected', 'Max_used_connections',
   'Connection_errors_max_connections', 'Threads_created', 'Connections');

1. Alert on Threads_connected as a percentage of max_connections, for example at 80%, so you have time to act.
2. Track Max_used_connections. If it is close to the limit, you have already been close to an outage.
3. Alert on any increase of Connection_errors_max_connections. Every increase is a client that was refused.
4. Watch query latency and lock waits along with the connection count. Rising latency is usually what drives connections up.