MySQL Too Many Connections: The Pool Math Behind Error 1040

Two Fridays ago our API started throwing 500s at 9:40am. Every request that touched MySQL failed with ERROR 1040 (HY000): Too many connections, while CPU on the database box sat at 12%. Nobody had deployed anything that morning. The server was mostly idle and still refusing to talk to us.

The reason showed up in the first query I ran.

Connected is not the same as busy

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected', 'Threads_running', 'Max_used_connections',
  'Max_used_connections_time', 'Connection_errors_max_connections', 'Aborted_clients'
);

-- Threads_connected                    152
-- Threads_running                        3
-- Max_used_connections                 152
-- Max_used_connections_time            2026-10-02 09:41:07
-- Connection_errors_max_connections   2140
-- Aborted_clients                    18326

152 connections open, 3 of them running a query. MySQL accepts 151 by default, and it had refused 2,140 attempts since the last restart. We were pinned at the ceiling while the database did almost nothing. That gap between Threads_connected and Threads_running is the entire diagnosis: the connections existed, the work did not. Something was holding connections open while idle.

Find out who is holding them

SHOW PROCESSLIST is the reflex, and with 152 rows it tells you nothing useful. Group it and the answer usually shows up on the first line.

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

Ours came back with 118 rows of command = 'Sleep' from three pod IPs, the longest one idle for 4,700 seconds. No leak and no runaway query. Just pools far larger than the work they had to do.

Sleep is not automatically a bug, by the way. Idle pooled connections are the point of pooling. What is not normal is 118 of them on a server where 3 queries are running, since that is the shape of a pool sized by guesswork rather than arithmetic.

The arithmetic nobody runs

Every process gets its own pool and the pools know nothing about each other. What the database sees is:

pool size x worker processes x instances  (+ queue workers, cron, migrations)

Ours was 10 x 4 workers x 3 pods, which is 120, plus a background worker carrying its own pool of 10 per pod. That makes 150. The migration step in our deploy pipeline took the last slot and landed us on 151, which is the default limit exactly.

That 10 came from a tutorial, and in the config file it looked harmless. Multiply it by the runtime and it stops being a detail. This is also why raising max_connections is the wrong first move. Doubling it buys you a week, then the same Friday comes back with twice as many connections to untangle.

Node makes the shortage harder to see, because mysql2 hides it instead of failing. In mysql2 3.x the pool defaults are connectionLimit: 10, maxIdle: 10, idleTimeout: 60000, waitForConnections: true and queueLimit: 0. That last one is an unbounded queue: when all ten connections are busy, request eleven waits quietly instead of erroring. Your p99 climbs for weeks and nothing in the logs says why.

So I started logging the pool state every 30 seconds. The enqueue event fires exactly when a request has to wait for a connection, which makes it the honest counter:

import mysql from 'mysql2/promise'

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  connectionLimit: 10,
  queueLimit: 50,     // default is 0, which means wait forever
  idleTimeout: 30000,
})

let enqueuedTotal = 0
pool.on('enqueue', () => { enqueuedTotal++ })

setInterval(() => {
  const core = pool.pool  // internal, but it is where these numbers live
  console.log({
    open: core._allConnections.length,
    free: core._freeConnections.length,
    waiting: core._connectionQueue.length,
    enqueuedTotal,
  })
}, 30000)

That log settled the argument in one deploy. Waiting sat at zero until a reporting endpoint ran, then it jumped to 40 and stayed there. Ops had been filing a vague latency complaint about that endpoint for two months and nobody could pin it on anything.

The fix, and the trap in the standard advice

We cut the pool to 4 per worker, dropped maxIdle to 2 so idle connections actually get reaped, and gave the batch worker its own database user with a hard cap:

ALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 120;

That cap is the part I would keep even at a comfortable connection count. When a batch job misbehaves now, it fails against its own ceiling with error 1203 instead of taking the site down with it.

Now the trap. Most of the posts I read about error 1040 tell you to shorten wait_timeout, which defaults to 28800 seconds and does look absurd. Set it to 60 with a pool that idles at 60 and you have built a race. The server closes an idle connection at the same moment your pool considers it free, then hands it to a request. mysql2 does not ping a connection before reuse, so that query dies with MySQL server has gone away (2006) or Lost connection to MySQL server during query (2013), and the user sees a random failure they cannot reproduce. Keep wait_timeout comfortably above the pool's idle timeout instead of pulling it down into the same neighbourhood.

One more thing worth knowing before the next incident. MySQL permits max_connections plus one connection, and that extra slot is reserved for accounts holding the CONNECTION_ADMIN privilege. That is where my 152nd connection came from. When our app user could not get in, the admin account still could, which is the only reason we got a processlist to look at. Keep that privilege on a human account and off the application account.

The status query at the top of this post lives in Snippet Ark now, next to the grouped processlist one, because at 9:40 on a Friday I do not want to be recalling column names. If your problem is one slow query instead of many idle connections, that is a different workflow entirely. Here every query was fast. There were just far too many connections sitting open doing nothing.