Diagnosing MySQL “Too many connections” Errors

This guide is part of ProxySQL & MySQL Connection Multiplexing. It deals with the MySQL server refusing a new session because every one of its max_connections thread slots is taken. Unlike a pool timeout, which leaves a queue you can watch drain, this error is immediate and indiscriminate: a health check, a checkout request and the on-call engineer’s mysql client all get the same answer.

ERROR 1040 (08004): Too many connections
[Warning] [MY-010054] [Server] Aborted connection 88213 to db: 'orders' user: 'orders_app'
host: '10.4.12.19' (Too many connections)

The goal is to identify which accounts and hosts hold the slots, get a working session despite the ceiling, relieve the pressure without a restart, and install limits so one misbehaving client can never again consume the whole server.

Rapid incident diagnosis

Get a session first. MySQL reserves one extra connection beyond max_connections for an account with the CONNECTION_ADMIN privilege (or SUPER on older versions). If an application account also holds that privilege, it will already have taken the spare slot — which is the first thing to fix afterwards. On MySQL 8.0.14 and later, a dedicated administrative interface configured with admin_address and admin_port accepts connections independently of max_connections:

mysql -h 127.0.0.1 -P 33062 -u dba_admin -p     # admin_port, unaffected by max_connections

Count by account and host. performance_schema.accounts keeps a row per user and host with the current connection count, which is faster and cheaper than grouping the processlist while the server is under pressure:

SELECT USER, HOST, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS
FROM performance_schema.accounts
WHERE USER IS NOT NULL
ORDER BY CURRENT_CONNECTIONS DESC
LIMIT 15;

Separate working sessions from sleeping ones. A slot held by a Sleep session is a pool that opened more connections than it uses; a slot held by a long-running Query is load. The distinction decides the remedy:

SELECT USER,
       SUBSTRING_INDEX(HOST, ':', 1) AS client_host,
       COMMAND,
       COUNT(*)            AS sessions,
       MAX(TIME)           AS max_seconds_in_state
FROM information_schema.PROCESSLIST
GROUP BY USER, client_host, COMMAND
ORDER BY sessions DESC
LIMIT 20;

Read the counters. Max_used_connections and Max_used_connections_time say when the ceiling was first hit, and Connection_errors_max_connections counts how many attempts were refused since startup:

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

A low Threads_running with Threads_connected at the ceiling is the signature of idle pools, not overload. Match Max_used_connections_time against deploy and autoscaling events; a ceiling reached two minutes after a scale-out from 8 to 14 pods is arithmetic, not a leak.

Connections by account at the moment of the error A bar chart of the 151 occupied slots: orders_app holds 92 with 71 sleeping, reporting holds 38 all sleeping, migrations holds 12, monitoring 6 and an unrecognised host 3. Occupied slots by account (max_connections = 151) orders_app reporting migrations monitoring 10.9.3.77 (unknown) 92 38 12 6 3 running a query Sleep (idle pool connection) 109 of 151 slots are idle: the pools, not the workload, hit the ceiling
Grouping the processlist by account and command usually answers the incident in one query. Here more than half of the slots are sleeping connections held by application pools.

Mathematical sizing or parameter formula

Two budgets bound max_connections, and the lower one wins.

The memory budget comes from per-thread buffers. Each connection reserves thread_stack immediately and can allocate sort_buffer_size, join_buffer_size, read_buffer_size and read_rnd_buffer_size per operation, plus temporary tables up to tmp_table_size. A conservative ceiling is:

max_connections_memory = (RAM − innodb_buffer_pool_size − os_reserve) ÷ per_connection_worst_case

With 32 GiB of RAM, a 22 GiB buffer pool and 2 GiB for the operating system, 8 GiB remain. If a realistic worst case per active connection is 12 MiB, the memory ceiling is about 680 connections. Sleeping connections use far less, so this bound is generous for idle pools and tight for concurrent reporting queries.

The demand budget is the sum of every pool that can connect, including autoscaling maximums:

required = Σ over services (max_replicas × processes_per_replica × pool_max) + reserved

For the incident above: orders_app has up to 14 pods × 1 process × a pool of 10 = 140, reporting has 4 workers × a pool of 10 = 40, and migrations, monitoring and operators need 25. The requirement is 205 against a configured 151 — the ceiling was reachable the moment autoscaling passed 9 pods. Raising max_connections to 250 fits inside the 680 memory bound; shrinking the idle part of each pool fixes the cause. The multi-service version of this arithmetic is worked through in Calculating a Connection Budget Across Microservices.

Memory ceiling versus configured and required connections A number line from 0 to 700 connections shows the configured max_connections of 151, the requirement of 205 at maximum autoscale, a proposed limit of 250, and the memory ceiling of about 680. Connections (0 to 700) 0 151 configured ERROR 1040 here 205 required at max autoscale 250 proposed ~680 memory ceiling (RAM − buffer pool − OS) ÷ 12 MiB fix the demand first; raise the ceiling only within the memory bound
The memory bound is set by the server; the demand bound is set by pool sizes multiplied by the autoscaling maximum. The incident sat between the default ceiling and the real requirement.

Exact remediation & configuration

Relieve the pressure now. Kill idle sessions from the account holding the most sleeping slots. The pools will reconnect on demand, and because they are idle nothing in flight is lost:

SELECT CONCAT('KILL ', ID, ';')
FROM information_schema.PROCESSLIST
WHERE USER = 'reporting' AND COMMAND = 'Sleep' AND TIME > 60;
-- review the output, then execute it from the admin session

Raise the ceiling at runtime, within the memory bound, and persist it so a restart does not revert:

SET PERSIST max_connections = 250;

If the error log shows Changed limits: max_connections: 214 (requested 250), the operating system’s open file limit is lower than MySQL needs; raise LimitNOFILE in the systemd unit before the next restart.

Stop one account from taking every slot. Per-account caps turn a server-wide outage into an error confined to the misbehaving client:

ALTER USER 'reporting'@'%'  WITH MAX_USER_CONNECTIONS 40;
ALTER USER 'orders_app'@'%' WITH MAX_USER_CONNECTIONS 160;
REVOKE CONNECTION_ADMIN ON *.* FROM 'orders_app'@'%';   -- keep the spare slot for humans

A client over its cap now sees ERROR 1203 (42000): User reporting already has more than 'max_user_connections' active connections, while everyone else continues.

Shrink the idle part of each pool. For HikariCP, the fix is a smaller minimumIdle and an idleTimeout short enough to release connections between traffic peaks. For a Python service using SQLAlchemy:

engine = create_engine(
    "mysql+pymysql://reporting@mysql-primary.internal/analytics",
    pool_size=4,          # steady-state connections per worker
    max_overflow=6,       # burst capacity, closed when returned
    pool_recycle=1800,    # below server wait_timeout
    pool_pre_ping=True,
)

Open the admin interface for next time. Add it to my.cnf so the next incident starts with a working session:

[mysqld]
admin_address = 127.0.0.1
admin_port    = 33062
create_admin_listener_thread = ON

If the fleet is large enough that the demand budget will keep outgrowing the server, put ProxySQL in front of it: the proxy’s per-server limit becomes the only number that has to fit inside max_connections.

Partitioning MySQL connection slots Application accounts connect through the main port with MAX_USER_CONNECTIONS caps that add up to less than max_connections, while the admin port accepts operator sessions independently. orders_app cap 160 reporting cap 40 migrations + monitoring cap 25 Main port 3306 max_connections 250 caps total 225 25 slots unassigned dba_admin admin_port 33062 mysqld thread per connection
Per-account caps partition the server's slots, and the administrative interface sits outside them. A runaway client exhausts its own share and nothing else.

Validation & verification

Confirm the headroom exists and holds under the conditions that caused the incident.

-- Headroom now: should be comfortably above zero at peak
SELECT @@max_connections - VARIABLE_VALUE AS free_slots
FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected';

-- Caps are in place
SELECT user, host, max_user_connections FROM mysql.user
WHERE user IN ('orders_app','reporting');

-- No application account can take the reserved slot
SELECT GRANTEE FROM information_schema.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'CONNECTION_ADMIN';

Then reproduce the scale-out. Scale the deployment to its autoscaling maximum in staging and hold a synthetic load for ten minutes while sampling the counter that matters:

kubectl scale deploy/orders-api --replicas=14
for i in $(seq 1 20); do
  mysql -h mysql-staging -uprobe -p"$PW" -Nse \
    "SHOW GLOBAL STATUS LIKE 'Connection_errors_max_connections'"
  sleep 30
done | awk '{print $2}' | uniq -c

The count must stay constant across all samples. Finally, test the escape hatch deliberately: exhaust a staging server with a loop of idle connections and confirm an operator can still log in through admin_port.

Preventing recurrence with monitoring

The error arrives without warning only when nobody watches the approach to the ceiling. Three measurements give hours of notice.

Connection headroom. The ratio of Threads_connected to max_connections is the direct signal. With the Prometheus MySQL exporter it is a simple expression, and a warning at 80 % leaves time to act:

groups:
  - name: mysql-connections
    rules:
      - alert: MySQLConnectionHeadroomLow
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels: { severity: warning }
      - alert: MySQLConnectionsRefused
        expr: increase(mysql_global_status_connection_errors_total{error="max_connections"}[5m]) > 0
        labels: { severity: critical }

Idle share. When most connected threads are sleeping, the ceiling is being consumed by pool reservations. Graph Threads_connected against Threads_running on one panel; a widening gap during quiet hours is the early form of the incident on this page.

Per-account growth. Snapshot performance_schema.accounts daily and compare. An account whose CURRENT_CONNECTIONS at the same hour grows week over week is heading for its cap — or, if it has none, for the server’s ceiling:

CREATE TABLE IF NOT EXISTS ops.connection_snapshots (
  taken_at DATETIME NOT NULL, user VARCHAR(32), host VARCHAR(255), current_connections INT
);
INSERT INTO ops.connection_snapshots
SELECT NOW(), USER, HOST, CURRENT_CONNECTIONS FROM performance_schema.accounts WHERE USER IS NOT NULL;

Two further habits close the remaining gaps. Record max_connections, per-account caps and each service’s pool maximum in the same repository as the services, so capacity reviews see all three together. And include the database connection count in every autoscaling review: raising a deployment’s replica maximum is a change to the MySQL budget, whether or not anyone touched the database.

Signal Source Warning level What it predicts
Threads_connected ÷ max_connections SHOW GLOBAL STATUS 0.8 ERROR 1040 within the next scale-out
Threads_connected − Threads_running SHOW GLOBAL STATUS above 60 % of connected Idle pool reservations filling the server
Connection_errors_max_connections SHOW GLOBAL STATUS any increase Clients already being refused
Account connections week over week performance_schema.accounts +20 % An account approaching its cap

Frequently Asked Questions

Why does raising max_connections sometimes make performance worse?
Because it admits more concurrent work than the CPU and InnoDB can schedule. Past roughly two to four times the core count of running threads, throughput flattens and latency climbs. Raise the ceiling to accommodate idle pool connections, but cap the active concurrency in the pools themselves.
Does wait_timeout fix leaked idle connections?
It closes them eventually — after 28,800 seconds by default — and pools that do not validate connections will then hand out dead sockets. Lower it to a value just above the longest pool idle timeout, and make the pools retire connections first with maxLifetime or pool_recycle.
Is the extra CONNECTION_ADMIN slot shared by all admin accounts?
Yes. There is exactly one spare slot, used by whichever privileged account connects first. That is why the dedicated administrative interface is the more reliable escape hatch on MySQL 8.0.14 and later.
How is this different on Amazon RDS or Aurora MySQL?
The default max_connections is derived from instance memory through a parameter group formula, and admin_port is not available to customers. Use a custom parameter group for the ceiling, rely on per-user caps, and keep a separate low-traffic account for operators. The Aurora specifics are in AWS Aurora Connection Scaling.
Should thread_cache_size be raised during the incident?
It does not add capacity. The thread cache reuses threads for new connections to avoid creation cost; it matters when connections churn, which shows as a high Threads_created rate. It neither frees slots nor changes the ceiling, so leave it for after the incident.
Why do Aborted_clients rise after killing idle sessions?
Each killed connection counts as aborted from the server’s perspective when the client later discovers the closed socket. It is expected after deliberate kills and settles once pools have replaced the connections.