When MySQL Hits max_connections, Raising It Is the Wrong Fix

There is a particular kind of outage that teaches you something about distributed systems. The database is at ten percent CPU. Disk is idle. Replication lag is zero. Every dashboard is green. And the application is completely down, with every log line repeating the same message:

ERROR 1040 (HY000): Too many connections

The database is not overloaded. It has run out of permission to talk to anyone.

The arithmetic makes this inevitable

Connection exhaustion is rarely a traffic problem. It is a multiplication problem, and it is usually baked in from the day the platform is designed.

Every service instance keeps its own connection pool. That pool is sized for the instance, not for the cluster. So the real number of connections your database must be prepared to accept is:

services × replicas per service × max pool size

Take a modest platform: 12 services, 4 replicas each, HikariCP with the default maximumPoolSize of 10.

12 × 4 × 10 = 480 connections

Against MySQL’s default max_connections of 151, that platform cannot ever be fully healthy. It survives only because the pools are lazy — they open connections on demand, and under normal load most sit well below their maximum. The arithmetic is a bomb with the timer already running.

Then something makes every pool fill at once. A slow downstream dependency holds transactions open longer. A Kubernetes rollout doubles replica count during the surge. A batch job wakes up. Each pool independently decides it needs more connections, all of them at the same time, and they collectively cross the limit within seconds.

What makes this vicious is the feedback loop. Once connections are refused, health checks start failing. The orchestrator restarts pods. Restarted pods reconnect and refill their pools from zero, which is more connection churn against a database already at its limit. The platform now generates its own load, and the outage sustains itself long after the original trigger has passed.

Why raising the limit is a trap

The obvious response is to raise max_connections to 2000 and move on. It works, briefly, and it buys a worse problem.

MySQL allocates per-connection buffers — sort_buffer_size, join_buffer_size, read_buffer_size, and more — and those allocations are per connection, not shared. A few hundred idle connections cost little. Two thousand connections that all decide to sort at once can push mysqld into swap or straight into the OOM killer. You have converted a fast, obvious failure into a slow, ambiguous one, and the second is much harder to diagnose at three in the morning.

There is a scheduling cost too. Every connection is a thread. Thousands of threads contending for the same mutexes and the same buffer pool degrades throughput even when nothing is failing. You end up with a database that is slower under normal load in exchange for a limit you will eventually hit anyway.

The deeper issue is that this treats the symptom. The application does not need 480 concurrent database conversations. Look at any real workload and most of those connections are idle, holding a slot while the application does something else entirely — calling another service, serialising JSON, waiting on the event loop. You are sizing your database for connection count when the thing that actually matters is concurrent query execution.

Multiplexing breaks the coupling

This is the problem ProxySQL is built for. It sits between the application and MySQL as a protocol-aware proxy, and it maintains two independent sets of connections: the frontend connections your applications open to it, and the backend connections it opens to MySQL. Those two numbers do not have to match.

The insight is that a connection only genuinely needs a backend while a query is actually running. Between statements, a connection is idle and its backend can serve someone else. ProxySQL exploits that: 500 frontend connections can share perhaps 50 backend connections, because at any instant only a fraction are mid-query.

Your application keeps its comfortable pool sizes. MySQL sees a small, stable connection count that no longer scales with your replica count. The link between “how many pods do we run” and “how close are we to killing the database” is severed, and that is the real win — it is an architectural fix, not a bigger number.

A minimal configuration looks like this. ProxySQL is administered over SQL on its own admin interface, which is a genuinely nice property: configuration is queryable and scriptable.

-- Backends. max_connections here is the per-server cap ProxySQL will open,
-- and it is the number that actually protects MySQL. Default is 1000.
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (10, 'mysql-primary', 3306, 200);

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (20, 'mysql-replica-1', 3306, 200);

-- Route SELECTs to the replica hostgroup, everything else to the primary.
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (100, 1, '^SELECT.*FOR UPDATE$', 10, 1),
       (200, 1, '^SELECT',              20, 1);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

Note the rule ordering: SELECT ... FOR UPDATE must be matched before the general SELECT rule, or read-modify-write logic silently starts reading from a replica. That is a data correctness bug produced by a routing config, and it will not show up in testing.

Two separate limits matter, and conflating them is a common mistake. The frontend limit, mysql-max_connections, controls how many application connections ProxySQL will accept. The backend limit is the per-server max_connections above. The frontend number should be generous — that is the whole point. The backend number is the one that has to stay comfortably under MySQL’s own max_connections, with headroom for replication threads, monitoring, and your ability to log in and fix things during an incident. Always leave yourself a way in.

The part people miss: multiplexing turns itself off

Here is what turns a successful ProxySQL rollout into a confusing one. Multiplexing is not unconditional. A backend connection can only be handed to another client if it carries no session state that the current client depends on. When ProxySQL detects such state, it pins the backend to that frontend connection and multiplexing stops for the duration.

The conditions that disable it:

An open transaction. Unavoidable and correct — a transaction must stay on one backend.
Temporary tables. Session-scoped, so the connection is pinned.
User-defined variables. Anything using @var.
GET_LOCK() and LOCK TABLES, which are session-scoped by definition.
SQL_CALC_FOUND_ROWS, because FOUND_ROWS() needs the same backend afterwards.

There are subtler ones. SET TRANSACTION ISOLATION LEVEL ... disables multiplexing, while SET SESSION TRANSACTION ISOLATION LEVEL ... is handled properly — ProxySQL supports the isolation level change at session scope only. The same applies to SET TRANSACTION READ WRITE and READ ONLY. A single missing keyword is the difference between multiplexing working and quietly not working.

This matters more than it sounds, because ORMs and connection pools emit these statements on your behalf. A pool configured to set an isolation level on every connection handout can pin every backend it touches, and you will have deployed ProxySQL, changed nothing measurable, and have no idea why.

So verify it rather than assuming it. The connection pool stats table tells you directly:

SELECT hostgroup hg, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR, MaxConnUsed
FROM stats_mysql_connection_pool
WHERE ConnUsed + ConnFree > 0
ORDER BY hg, srv_host;

Compare ConnUsed against the number of frontend connections you know are open. If they track each other one-to-one, multiplexing is not happening and you should find out which statement is pinning connections. MaxConnUsed is the high-water mark, which is the number to watch when sizing backend limits — averages will comfort you right up until the incident.

If your workload batches several statements in quick succession, mysql-connection_delay_multiplex_ms keeps a backend attached briefly after each query rather than returning it to the pool immediately. It trades a little pool efficiency for fewer reassignments.

Where to run it

Two topologies, and the choice is about failure domains.

As a sidecar, one ProxySQL per application pod. Connection to the proxy is over loopback, there is no extra network hop, and a proxy failure takes down exactly one pod — which your orchestrator already knows how to handle. The cost is many small pools, each with its own backend connections, which weakens the consolidation you deployed it for. It also multiplies your configuration management problem.

As a central tier, a small pool of ProxySQL instances behind a virtual IP or a Kubernetes service. Consolidation is much better, configuration lives in one place, and the connection reduction is dramatic. In exchange you have added a network hop and a component whose failure is now everyone’s failure. Run at least two, and make sure the failover path is tested rather than assumed.

For most platforms I would start with the central tier, because the whole point is consolidating connections and the sidecar model undercuts that. But run it as a genuine HA pair from day one. A single proxy in front of your database is a single point of failure you built deliberately, which is the worst kind.

Fix the root cause too

ProxySQL will absorb the symptom. It does not excuse the design that produced it, and if you stop here you have added infrastructure to hide a problem rather than solve it.

Size pools for what a service actually needs. A service handling 50 requests per second with 10ms queries needs roughly one connection, not ten. The default maximumPoolSize of 10 is a starting point nobody revisits, and it is wrong in both directions — wasteful for most services, inadequate for the one doing bulk work.

spring:
  datasource:
    hikari:
      maximum-pool-size: 5
      minimum-idle: 2
      connection-timeout: 3000   # fail fast; do not queue requests behind a full pool
      max-lifetime: 600000       # recycle below any proxy or DB idle timeout
      leak-detection-threshold: 20000

connection-timeout deserves particular thought. The default of 30 seconds means a request will sit waiting for a connection for half a minute before failing — long enough for the caller to time out, retry, and add yet more demand to a pool that is already exhausted. Three seconds converts a slow cascading failure into a fast, visible one. Fast failure is a feature.

Turn on leak-detection-threshold. Connections held far longer than any query should take are almost always a bug — a missing close(), a transaction spanning a remote call, a @Transactional method that grew an HTTP client. Those leaks are what turn a comfortable margin into an outage.

And keep transactions short. The single most effective change I have seen on this class of problem is not infrastructure at all: it is removing remote calls from inside transaction boundaries. A transaction that waits on a third-party payment gateway holds a database connection for the duration of somebody else’s network problem, and their bad day becomes your outage.

The checklist

Calculate services × replicas × pool size and compare it to max_connections today. If the first number is larger, you have a latent outage, not a healthy system.
Right-size pools before adding infrastructure. Most are too large.
Set an aggressive connection-timeout so exhaustion fails fast and visibly.
Deploy ProxySQL to decouple application pools from database threads, sizing backend max_connections with headroom for replication, monitoring, and human access.
Verify multiplexing is actually happening via stats_mysql_connection_pool. Do not assume.
Audit for statements that pin connections, especially isolation levels set by your pool or ORM.
Alert on MaxConnUsed approaching the backend limit, and on ConnERR at all.
Get remote calls out of transactions.

The failure mode is worth understanding well, because it is entirely predictable. It is arithmetic you can do on a whiteboard before you write a line of code — and unlike most capacity problems, it gives you no warning curve. You are fine, and then every service is down at once.