+1 (541) 238-9429
QueryKings

Too many connections: pooling, ProxySQL, and the limit that actually binds

The page arrives looking the same every time:

ERROR 1040 (HY000): Too many connections

Someone raises max_connections, the errors stop for a week, and then the same incident returns with a larger blast radius. The setting was never the constraint. max_connections is a guard rail; when you hit it, something else — a slow query, a lock wait, a connection pool sized by arithmetic nobody checked — has already gone wrong upstream, and the guard rail is the first place it became visible.

Here is the order we work through it.

First: is the server busy, or is it stuck?

These look identical from the application and need opposite responses.

SELECT VARIABLE_VALUE AS threads_connected
  FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'Threads_connected';

SELECT VARIABLE_VALUE AS threads_running
  FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'Threads_running';

Threads_connected is how many sessions exist. Threads_running is how many are actually executing at this instant. A healthy OLTP server usually runs single digits to low tens of running threads even with hundreds connected; most connections are idle between statements, which is what a pool is for.

  • Connected high, running low. The application is holding connections it is not using. Pool sizing or connection leaks. Nothing is wrong inside MySQL.
  • Connected high, running also high (say 100+). Real concurrency, and probably a pile-up: one bad plan or one lock holder is making every caller wait, so the next request arrives before the last one finished. Look at what is running, not at max_connections.

For the second case, the list of offenders is one query away:

SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS stmt
  FROM information_schema.processlist
 WHERE command <> 'Sleep'
 ORDER BY time DESC
 LIMIT 30;

If thirty sessions are all in Sending data on the same statement, you have one missing index, not a connection problem. If they are in updating or waiting on a lock, see the lock-wait evidence trail: a single long transaction can convert into hundreds of queued connections in under a minute.

The arithmetic nobody checks

Most connection exhaustion in fleets we review is not caused by traffic. It is caused by multiplication.

Each application instance carries a pool. HikariCP defaults to 10. Say 40 service pods, three services sharing the database, plus workers, plus a cron box, plus two ad-hoc BI connections. That is comfortably over a thousand possible connections against a max_connections of 500, and the day it all scales up together is the day you find out.

Write the number down explicitly:

(instances x pool_max) summed over every service
  + background workers
  + migrations and deploy tooling
  + human and BI clients
  + a reserve for your own emergency session

That total must sit under max_connections, and max_connections must sit under what the server's memory can support. Two habits make the reserve real: keep max_connections above your computed ceiling by a margin, and know that MySQL reserves one extra slot for a SUPER/CONNECTION_ADMIN user precisely so you can log in during an outage. On MySQL 8.0 and later you can also set admin_address and admin_port for a dedicated administrative interface with its own connection allowance — worth configuring before you need it, not during.

Why raising max_connections has a cost

MySQL allocates per-connection buffers lazily but genuinely: sort_buffer_size, join_buffer_size, read_rnd_buffer_size, tmp_table_size, plus the net and thread stacks. A session that runs a filesort with a 4 MB sort buffer and a couple of joins can hold tens of megabytes. Multiply by 2,000 connections and you are in territory where the OOM killer, not the optimizer, decides your availability.

The trade-off is concrete: memory spent on per-connection buffers is memory not in the InnoDB buffer pool, and buffer pool hit ratio is usually worth more than connection headroom. If you must raise max_connections, lower the session-level buffer defaults at the same time and set them per-query in the few places that genuinely need a big sort.

There is a scheduling cost too. Beyond roughly the number of CPU threads, more concurrently running threads does not increase throughput — it increases context switching and contention while every query gets slower. That is the shape of the classic throughput cliff: fine up to a point, then latency goes vertical while completed transactions per second fall. MySQL Enterprise and Percona Server offer a thread pool to cap executing threads and queue the rest; in Community MySQL, the equivalent is a properly sized application pool or a proxy layer that does the queuing for you.

Where a pooler belongs

A middle-tier pooler — ProxySQL, RDS Proxy, or a language-native pool used well — is the right fix for specific shapes of this problem:

  • Serverless or very high instance counts (Lambda, autoscaled pods) where each instance's pool is small but the instance count is unbounded. Multiplexing many client connections onto a smaller set of backend connections is exactly what this is for.
  • Connection storms after a failover, where every application instance reconnects at once. A proxy absorbs the reconnect and keeps the database from being the thundering-herd target.
  • Read/write split and query routing, where ProxySQL's rule engine sends reads to replicas without application changes, and can mirror, throttle, or rewrite a specific bad query while you ship a real fix.

What a pooler does not do: make a slow query fast. Put a proxy in front of a database whose problem is a table scan and you convert Too many connections into growing queue latency inside the proxy — the same outage with a less informative error message. Fix the plan first; add the proxy for connection economics.

Two implementation notes that catch teams out. Transaction-level multiplexing means a session variable, a temporary table, or a prepared statement set on one backend connection may not be there on the next statement — RDS Proxy pins the session when it detects these, and pinning quietly erases the benefit, so check your pinning metrics. And the proxy is now in the availability path: it needs its own redundancy, its own monitoring, and a tested answer to "what happens when the proxy restarts mid-transaction".

A short runbook

When 1040 fires:

  1. Get in on the reserved or admin connection. Check Threads_running.
  2. If running is high, read the processlist and kill or fix the dominant statement. Capture it before killing it.
  3. If running is low and connected is high, the problem is upstream: leaked connections, a pool without a max, or a deploy that doubled instance count.
  4. Only then consider max_connections, and raise it with the memory arithmetic done, not as a reflex.
  5. Afterwards, alert on Threads_connected as a percentage of max_connections (70% is a reasonable first threshold) and on Threads_running, so the next occurrence is a warning rather than an outage.

Connection limits are one of the few database failures that are almost always a symptom. Treated that way, they are also one of the quickest to resolve for good: the arithmetic takes an afternoon, and the query that caused the pile-up was going to need fixing anyway.

If you are staring at a connection graph that climbs every afternoon and nobody can say which service owns the connections, that is a good thing to work through with someone who has mapped this before. Send us the pool configuration and an hour of Threads_connected and Threads_running, and the shape is usually clear from the two lines together.