ERROR 1040 Too many connections: getting back into a full MySQL server

The application logs fill with error 1040, Too many connections. You open a terminal to look, and mysql gets the same answer. The server is up and answering. It has handed out each connection slot it allows, and your session would be one more.

How MySQL counts connections

max_connections caps the number of simultaneous client connections, 151 by default. MySQL and MariaDB permit one connection beyond it and keep that one for an administrator: an account with CONNECTION_ADMIN or the deprecated SUPER on MySQL 8.0 and 8.4, CONNECTION ADMIN or SUPER on MariaDB 10.5 and later.

That’s one slot for all administrators together. A monitoring agent that signs in as root, or an application account that received SUPER years ago, can grab it the moment the server fills up, and you find it taken. The admin account a managed service gives you may not hold CONNECTION_ADMIN at all. Run SHOW GRANTS for your on-call account on a calm day, before you need the slot.

A separate door for administrators

MySQL 8.0.14 added an administrative interface with its own address and port. Set admin_address in the [mysqld] section of my.cnf, for example to 127.0.0.1 or a private IP, and the server listens there on admin_port, 33062 by default. Accounts with SERVICE_CONNECTION_ADMIN can connect on that interface, and MySQL sets no limit on the number of administrative connections. admin_address takes effect at startup only, so set it up before the incident. During one, you connect like this:

mysql -h 10.0.4.12 -P 33062 -u oncall -p

MariaDB has its own version. extra_port, off (0) by default and read at startup, opens a second port that serves each connection with its own thread, outside the thread pool. extra_max_connections, 1 by default, sets how many connections that port accepts on top of max_connections.

Finding who holds the slots

Once you’re in, start with the counters:

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

Threads_connected against max_connections shows how full the server is now. Max_used_connections is the peak since startup; MySQL also reports Max_used_connections_time, the moment of that peak. Connection_errors_max_connections counts the clients the server refused for this reason.

Compare Threads_running with Threads_connected. A server with 151 connections and 4 running holds 147 idle ones. A server with 140 running has a pile-up: the queries wait on something, and each waiting query keeps its slot.

Then group the processlist by client:

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

Three patterns cover most incidents.

Many Sleep connections from one application. Count the pools before you suspect a leak. Thirty application instances with a pool of 10 hold 300 connections, twice the default limit, and each of them looks legitimate to its pool. HikariCP keeps minimumIdle equal to maximumPoolSize unless you set it, so each instance holds its full pool open at idle. A rolling deploy runs old and new instances side by side and doubles the count for a while.

Sleep connections with a Time in the hours. These belong to clients that disappeared without closing the socket, such as a VM that someone deleted, or to code that opens a connection and never closes it. The server keeps an idle connection until wait_timeout, eight hours by default.

Many connections in Query. Look at the State column. Waiting for table metadata lock points to a migration stuck behind a forgotten transaction. Row lock waits point to a transaction asleep with its locks. The connection limit is the symptom here, and the fix is the root blocker.

Getting room back

Close idle connections from the noisy client. Generate the statements first and read them before you run them. This version skips connections with an open transaction, because killing those rolls back work:

SELECT CONCAT('KILL ', p.ID, ';') AS stmt
FROM information_schema.PROCESSLIST p
LEFT JOIN information_schema.INNODB_TRX t
       ON t.trx_mysql_thread_id = p.ID
WHERE p.USER = 'app'
  AND p.COMMAND = 'Sleep'
  AND p.TIME > 300
  AND t.trx_id IS NULL;

On Amazon RDS and Aurora MySQL, build CALL mysql.rds_kill(<id>); instead. Expect the pool to notice and open new connections as it needs them. A fixed-size pool fills itself back up, so this buys time against a pile of abandoned connections and does little against pools that are too big.

Raise the limit as a stopgap.

SET GLOBAL max_connections = 300;

New connections see the new limit at once. On MySQL 8.0 and 8.4, SET PERSIST keeps it across a restart; on MariaDB, add it to my.cnf. On RDS and Aurora, change it in the DB parameter group, and on Azure in the server parameters.

Each extra slot costs memory once someone uses it. An idle connection needs little, a thread stack and a few buffers. A busy one allocates sort_buffer_size, join_buffer_size and read buffers as its queries need them, and can build in-memory temporary tables. Doubling max_connections doubles the worst case, and the worst case arrives during a pile-up, when the extra connections all run queries. If the slots went to waiting queries, a higher limit gives the pile more room to grow and doesn’t touch the blocker.

Fixing the pools

• Do the arithmetic. Add up instances times maximum pool size, the deploy overlap, replicas, monitoring and your own sessions. The total has to fit under max_connections.
• Let pools shrink. Set minimumIdle below the maximum and an idle timeout, and keep the connection lifetime a few seconds below wait_timeout.
• Cap the application account. ALTER USER 'app'@'%' WITH MAX_USER_CONNECTIONS 120; makes a runaway application fail with error 1203 and leaves the rest of the slots for everyone else.
• Guard the admin path. Keep CONNECTION_ADMIN and SUPER away from application accounts, and set up admin_address on MySQL or extra_port on MariaDB at the next planned restart.
• Alert before the wall. Page on Threads_connected above 80% of max_connections, while you can still sign in.

On the phone

RowDeck needs a free slot like any other client. Each connection has a Fallback port field under Advanced, “Used only when the server is full (ERROR 1040): MySQL admin_port or MariaDB extra_port.”, and when the main port answers 1040, RowDeck signs in on the fallback port by itself. For a full server, Health shows a card with a button to use the fallback port and a list of ways in. Once you’re in, the Connections tile compares the count with max_connections, and Activity’s By client view groups threads by program, user or host. Killing connections needs Pro. Monitoring one server is free.

More about the screen: the MySQL processlist on your iPhone.