MySQL connection limits and pooling: too many connections

ERROR 1040 (HY000): Too many connections almost never means the database needs a bigger max_connections. It means something opened more connections than the work in flight needed, and usually that something is a pool configured with defaults nobody looked at, multiplied by every process and worker that holds one. MySQL 8.4 allows 151 client connections by default. A small application serving real traffic rarely needs more than 20 at a time. The fix is nearly always on the client side: fewer, shared, recycled connections - and a server limit set to what the memory can actually carry.

This post covers how MySQL counts connections, what each one costs, how to see who is holding them, and how to size the pool in each common runtime so the total stays below the limit even on the day you scale out.

How MySQL counts connections#

Every client connection to MySQL is a session with its own thread on the server. MySQL 8.4 uses one thread per connection in the community edition, so the number of connections is also the number of server threads waiting for or executing work.

The settings that decide how many can exist:

Two details in that table explain most surprises. First, MySQL keeps one extra connection beyond max_connections for an account with the CONNECTION_ADMIN privilege (or the deprecated SUPER). That is why you can usually still log in as root to diagnose a server that is refusing your application - as long as root is not one of the accounts flooding it. Use it: an application user should never have that privilege.

Second, wait_timeout is eight hours. An application that opens connections and forgets about them does not get cleaned up for a working day. On a server limited to 151, that is how a leaking script running every minute from cron fills the table by mid-morning.

Since MySQL 8.0.14 there is also a dedicated administrative interface (admin_address and admin_port, default port 33062), which accepts connections from privileged accounts regardless of max_connections. It is off unless admin_address is set, and on a hosted plan the port is not normally reachable, so treat the reserved extra connection as your emergency door instead.

What a connection costs#

Raising max_connections to 2,000 is a configuration change, not capacity. Each connection consumes memory on the server, and some of that memory is only allocated when a query needs it - which means the server looks fine until the moment many connections run heavy queries at once.

The per-session buffers that matter:

An idle connection costs a megabyte or two. A connection running a badly indexed report with a sort, two unindexed joins and a temporary table can briefly cost tens of megabytes. Multiply the worst case by max_connections and you get the number you should compare with the RAM left over after innodb_buffer_pool_size.

That is the real sizing rule. On a 1 GB server, the buffer pool takes roughly half, the server's own overhead a few hundred megabytes, and what remains supports perhaps 50 to 100 active connections doing normal work. On 4 GB you have room for a few hundred, and on 8 GB the limit is more likely to be CPU than memory - 300 connections all executing queries on four cores just queue for the cores. MySQL InnoDB tuning for small servers goes through the buffer pool side of that sum.

Finding out who holds the connections#

Before changing any number, look. Log in as root (the reserved connection gets you in even when the application cannot) and ask the server.

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

What each one tells you:

• Threads_connected is how many connections exist now. Threads_running is how many are actually executing a statement. A healthy application shows a large gap: 40 connected, 2 running.
• Max_used_connections is the high-water mark since the last restart, and Max_used_connections_time says when it happened. If the peak is 151 and the time matches the outage, you have your incident.
• Connection_errors_max_connections counts refusals because of the limit.
• Aborted_clients grows when clients disappear without closing cleanly - typically processes killed mid-request or connections timed out by wait_timeout.

Then break the connections down by who and where:

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, db, command,       COUNT(*) AS conns, MAX(time) AS longest_sFROM information_schema.processlistGROUP BY user, client, db, commandORDER BY conns DESC;

A hundred rows with command = 'Sleep' from one client address is a pool that is too large or a process that leaks. A dozen rows with command = 'Query' and a large time is slow queries holding connections, which is a different problem: the pool fills because every connection is stuck behind a lock or a full table scan. The MySQL slow query log is the tool for that one.

sys.session gives a richer view if you want the current statement, memory and lock information for every session, and performance_schema.threads has the same data at a lower level. For an emergency, KILL <id> ends a connection, and KILL QUERY <id> stops only the statement running on it.

Changing the server limits#

With the root password you can change the limits live. SET GLOBAL lasts until the next restart; SET PERSIST also writes the value to mysqld-auto.cnf in the data directory, so it survives restarts.

-- Look firstSELECT @@max_connections, @@wait_timeout, @@max_user_connections;-- A realistic limit for a 2 GB server, kept across restartsSET PERSIST max_connections = 200;-- Close idle connections after ten minutes instead of eight hoursSET PERSIST wait_timeout = 600;-- Cap one application account so it cannot starve the othersALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 120;

A shorter wait_timeout is the most useful of the three on a server shared by several applications, because it reaps connections abandoned by crashed or careless clients. It comes with one condition: your pools must recycle connections before the server kills them, otherwise the application occasionally picks up a dead connection and the first query on it fails with MySQL server has gone away (error 2006) or Lost connection to MySQL server during query (error 2013). Every runtime below has a setting for this, usually called maximum lifetime or recycle time. Set it a minute or more below wait_timeout.

The per-account cap is the other underused tool. If a reporting job, a cron script and the web application share one database server, give each its own user with MAX_USER_CONNECTIONS. When the cron job misbehaves, it hits its own ceiling (error 1203, User already has more than 'max_user_connections' active connections) and the website keeps working. MySQL users and privileges covers creating those separate accounts.

The pooling arithmetic#

A pool is a set of connections opened once and lent to requests. It exists because opening a MySQL connection costs a TCP handshake, a TLS handshake if you use one, and an authentication exchange - several round trips, which add up to milliseconds per request across a network. Reusing a connection costs nothing.

The trap is that every process has its own pool. The total number of connections your application can open is:

total = pool size  x  processes per instance  x  instances (+ cron, workers, shells)

A Node.js application with the mysql2 default of 10 connections, run in cluster mode with 4 workers, deployed on 3 servers, can open 120 connections before anyone runs a migration. Add a queue worker with its own pool of 10 and a staging environment pointing at the same database, and you are at 151.

The useful number is not the maximum but the concurrency you actually need. A pool only needs as many connections as there are queries running at the same instant. If each request spends 5 ms in the database and an instance serves 200 requests per second, that is one second of database time per second - about one busy connection. Ten is generous. The widely quoted starting point from the HikariCP project is connections = (cores x 2) + effective spindle count for the database server, and on a 2 vCPU database that suggests a total in the single digits to low tens across all clients - smaller than most people expect, and faster, because a database with fewer concurrent queries spends less time switching between them.

Write the sum down somewhere next to max_connections and keep 20% headroom for migrations, a shell and the occasional backup.

Pool settings per runtime#

Each runtime's defaults, and what to change.

PHP

PHP-FPM has no shared pool. Each FPM worker is a separate process that opens a connection per request and closes it at the end, so the number of connections is bounded by pm.max_children. A pool with pm.max_children = 50 can open 50 connections. That is usually fine; connections are cheap to open over a short network path. PDO::ATTR_PERSISTENT => true keeps one connection per worker open between requests - it saves the handshake but leaves session state (temporary tables, SET variables, an open transaction after a fatal error) for the next request, and turns idle workers into idle connections. Use it only if you measured the handshake cost. PHP PDO and MySQL covers the connection options in detail.

Node.js

import mysql from "mysql2/promise";export const pool = mysql.createPool({  host: process.env.DB_HOST,  port: Number(process.env.DB_PORT),  user: process.env.DB_USER,  password: process.env.DB_PASSWORD,  database: process.env.DB_NAME,  connectionLimit: 10,     // default 10  waitForConnections: true, // queue instead of failing  queueLimit: 0,           // 0 = unbounded queue  maxIdle: 5,              // idle connections kept open  idleTimeout: 60000,      // ms before an idle connection is closed  enableKeepAlive: true,});

Create the pool once per process, at module level. The classic leak is calling createPool inside a request handler, which creates a new pool of ten for every request. In serverless deployments each function instance gets its own pool, so set connectionLimit low (1-2) there.

Python

SQLAlchemy's engine holds the pool: pool_size=5 and max_overflow=10 by default, so up to 15 per process. Add pool_recycle below wait_timeout and pool_pre_ping=True, which tests a connection before handing it out.

from sqlalchemy import create_engineengine = create_engine(    "mysql+pymysql://app:secret@db.example.com:3306/app?charset=utf8mb4",    pool_size=5, max_overflow=5, pool_recycle=540, pool_pre_ping=True,)

Gunicorn with 4 workers and that engine can open 40. Django does not pool at all: CONN_MAX_AGE defaults to 0, meaning a new connection per request; set it to a value below wait_timeout (and CONN_HEALTH_CHECKS = True) to reuse connections per worker thread.

Java and .NET

HikariCP defaults to maximumPoolSize = 10 and maxLifetime = 1800000 ms (30 minutes) - which is fine with the default eight-hour wait_timeout, and must be lowered if you shorten it. MySqlConnector for .NET pools by default with Maximum Pool Size=100, which is large; put Maximum Pool Size=20;Connection Lifetime=540 in the connection string for a small server. Go's database/sql has no limit on open connections unless you call SetMaxOpenConns, and keeps two idle by default; always set SetMaxOpenConns, SetMaxIdleConns and SetConnMaxLifetime.

Connection pools and limits covers the same ideas from the PostgreSQL side, where connections are processes and the arithmetic is stricter still.

Troubleshooting the errors you will actually see#

`ERROR 1040: Too many connections`. The global limit is reached. Log in as root, run the processlist query above, and find the client holding most of them. Kill the sleepers if you need the application back immediately, then fix the pool size or the leak. Raise max_connections only if the sum of legitimate pools genuinely exceeds it and memory allows.

`ERROR 1203: User already has more than 'max_user_connections' active connections`. A per-account cap. Either the cap is too low for the pools using that account, or one process is leaking.

`MySQL server has gone away` (2006) or `Lost connection to MySQL server during query` (2013) after idle periods. The server closed an idle connection at wait_timeout and the pool handed it out anyway. Set the pool's maximum lifetime below wait_timeout, or enable a pre-ping. If it happens during a large insert, the other classic cause is a packet larger than max_allowed_packet (64 MB by default in 8.4).

`Host 'x' is blocked because of many connection errors`. After max_connect_errors (default 100) consecutive failed handshakes from one host, MySQL blocks it. Usually a health check that opens a TCP connection and closes it without logging in. FLUSH HOSTS was removed in favour of TRUNCATE TABLE performance_schema.host_cache, which clears the block; then fix the health check.

The pool times out waiting for a connection, but MySQL shows few connections. The pool is too small for the concurrency, or connections are held across slow work - an HTTP call made while a transaction is open, for instance. Release connections before doing anything that is not a query.

Connections climb slowly all day. A leak: code that acquires a connection and returns early on an error path without releasing it. In Node, use pool.query() instead of getConnection() where you can, because it releases automatically.

FAQ#

What should max_connections be?

High enough for the sum of your pools plus 20% headroom, and low enough that every connection running a moderately heavy query at once would still fit in memory. For most applications on a 1-4 GB MySQL server that is somewhere between 100 and 300. The default of 151 is reasonable for a single application.

Is a connection pool always faster?

For anything that runs more than a handful of queries per second, yes - it removes several network round trips from each request. For a cron script that runs once an hour, a pool adds nothing; open one connection, do the work, close it.

Do I need ProxySQL or another connection proxy?

Only when you have more clients than a sensible max_connections can serve - hundreds of serverless functions, or dozens of instances each with a pool. A proxy multiplexes many client connections onto a few server connections. For one to five application servers, correctly sized pools are simpler and enough.

Why does my database show hundreds of sleeping connections?

Each one is a connection a client opened and is not using right now. That is normal in moderation - it is what a pool looks like when traffic is low. Hundreds from one source means the pool's maximum is too large or connections are never returned. Lowering wait_timeout cleans up after careless clients but does not fix them.

Does wait_timeout affect long-running queries?

No. It applies to idle connections only. A query that runs for twenty minutes is not idle. Long queries are limited by max_execution_time for SELECT statements (in milliseconds, 0 by default, meaning no limit) and by the client's own read timeout.