✏️ Explanatory Question

Your app throws "Too many connections" under load — connection pooling explained

👁 7 Views
📘 Detailed Answer
🟢 Easy
← Previous Database sharding — when to shard, choosing a shard key, and the problems it creates
💡

Answer with Explanation

117

Your app throws "Too many connections" under load — connection pooling explained

Level: Very Hard — A universal production failure; the root cause is almost always missing or misconfigured pooling.

Scenario: During a traffic spike, your app suddenly throws "ERROR 1040: Too many connections" and requests fail. The database CPU is fine — it's not overloaded with work, it's overloaded with connections. Why does this happen, and how do you fix it properly?

The Root Cause

MySQL has a hard limit, max_connections (default ~151). Each connection also consumes memory (buffers, thread stack). Under load, if the app opens a new connection per request and doesn't reuse them, connections pile up past the limit — and MySQL rejects new ones. It's a connection management problem, not a query-performance problem.

The anti-pattern: "Open connection → run 1 query → close connection" for every web request. Establishing a MySQL connection is expensive (TCP handshake, auth, thread setup). Under 10,000 requests/sec, this creates a storm of connect/disconnect churn and blows past max_connections.

Step 1 — Diagnose

-- How many connections are configured vs currently used?
SHOW VARIABLES LIKE 'max_connections';           -- e.g., 151
SHOW STATUS LIKE 'Threads_connected';             -- currently open
SHOW STATUS LIKE 'Max_used_connections';          -- peak reached
SHOW STATUS LIKE 'Connection_errors_max_connections';  -- rejections

-- See what all those connections are doing
SHOW PROCESSLIST;   -- many 'Sleep' rows = idle connections held open = waste
The real fix — connection pooling: A pool maintains a set of reusable, long-lived connections. The app borrows a connection, runs its query, and returns it to the pool (not closing it). A handful of pooled connections serve thousands of requests, because each query only holds a connection for milliseconds.

No Pool vs Pool

No Pooling

  • New connection per request
  • Expensive handshake every time
  • Connections pile up → 1040 error
  • Connect/disconnect churn

With Pooling

  • Fixed set of reused connections
  • Borrow → query → return
  • Handshake paid once, reused
  • Thousands of req/s on ~20 conns

Sizing the Pool (don't oversize!)

A bigger pool is not better — too many connections cause context-switching and lock contention. A well-known starting formula:

$$ pool\_size = (core\_count \times 2) + effective\_spindle\_count $$

For most apps, a small pool of 10–30 connections per app instance outperforms hundreds. The database does more real work with fewer, busier connections.

Pool Configuration Example (HikariCP-style)

{
  "maximumPoolSize": 20,        // hard cap of connections from this app
  "minimumIdle": 5,             // keep 5 warm connections ready
  "connectionTimeout": 30000,   // wait up to 30s for a free connection
  "idleTimeout": 600000,        // close connections idle > 10 min
  "maxLifetime": 1800000        // recycle connections after 30 min
}

The Full Fix (in priority order)

  • Use a connection pool — the real solution (HikariCP, PgBouncer-style proxies, etc.).
  • Right-size the pool — small and busy beats large and idle.
  • Return connections promptly — never hold one across slow external calls.
  • Cap total = sum of app pools ≤ max_connections (leave headroom for admin).
  • Raise max_connections as a stopgap — only if memory allows; it's not the real fix.

Stopgap — Raise the Limit (carefully)

-- Temporary relief; ensure the server has RAM for the extra connections
SET GLOBAL max_connections = 500;
-- Make it permanent in my.cnf: max_connections = 500
-- WARNING: each connection uses memory; too high can OOM the server
Interviewer follow-up: "You have 100 app servers, each with a pool of 30 — that's 3,000 connections, exceeding max_connections. Now what?" → Put a connection proxy/multiplexer (ProxySQL, or MySQL Router) between the apps and the database. It multiplexes thousands of app-side connections onto a much smaller pool of actual DB connections — the standard pattern for large fleets.
← Previous Database sharding — when to shard, choosing a shard key, and the problems it creates