CertGrid CertGrid
Troubleshooting·MySQL

MySQL Connection Limit Failures

`ERROR 1040` is almost never a sign that you need more connections. The status counters and the process list say which it is - and raising the limit on a connection leak converts it into a memory problem.

Troubleshooting Guide 36 of 45 Intermediate

Written against the versions above. MySQL reserves one extra connection above `max_connections` for an account with `CONNECTION_ADMIN`, so an administrator can still get in to fix it. That reservation is the reason this is recoverable without a restart.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Read the counters that describe connection health

    System variables are settings; status variables are measurements, and they live in performance_schema.global_status rather than behind @@. That catches people out - SELECT @@max_used_connections fails with ERROR 1193, because it is a status variable, not a system one.

    Max_used_connections is the high-water mark since startup - the single most useful number for sizing. Threads_connected is now; Threads_running is how many are actually doing work, which is usually far smaller. Connection_errors_max_connections counts rejections, and is the one to alert on.

    bash Example session 
    sudo mysql --table -e "SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Max_used_connections','Threads_connected','Threads_running','Connection_errors_max_connections','Aborted_connects')"+-----------------------------------+----------------+| VARIABLE_NAME                     | VARIABLE_VALUE |+-----------------------------------+----------------+| Aborted_connects                  | 0              || Connection_errors_max_connections | 5              || Max_used_connections              | 6              || Threads_connected                 | 3              || Threads_running                   | 4              |+-----------------------------------+----------------+

    Expected resultThe high-water mark, current connections, running threads and rejection count.

    Success conditionYou can tell how close to the limit the server has ever been.

  2. Saturate it deliberately

    max_connections is lowered to 5, then eight clients are sent at it at once.

    ERROR 1040: Too many connections - the connection is refused before any authentication or query. This is what an application sees the moment a connection pool misbehaves, and it affects every client, including the healthy ones.

    That is what makes 1040 an outage rather than a slowdown: the server is fine, the queries are fine, and nothing new can get in.

    bash Example session 
    sudo mysql -e "SET GLOBAL max_connections = 5"sudo bash -c 'for i in 1 2 3 4 5 6 7 8; do mysql appdb -e "SELECT SLEEP(4)" >/dev/null 2>>/tmp/conn.err & done; sleep 2; mysql -e "SELECT 1" 2>&1 | head -2; echo "exit=$?"; wait; echo "--- errors seen ---"; sort -u /tmp/conn.err | head -3'ERROR 1040 (HY000): Too many connectionsexit=0--- errors seen ---ERROR 1040 (HY000): Too many connections

    Expected resultERROR 1040 (HY000): Too many connections.

    Success conditionYou have reproduced the failure and seen it lock everyone out.

  3. Find out who is holding them

    The process list is the diagnosis, and there are two things to read.

    COMMAND distinguishes Sleep from Query. A connection in Sleep is idle - held open by a pool and doing nothing. A screen full of sleeping connections with a high TIME is a connection leak, not a capacity problem, and raising max_connections will only delay the next occurrence while using more memory.

    TIME is seconds in the current state. Sorting by it descending puts the worst offender first, which is usually the one to kill.

    bash Example session 
    sudo bash -c 'mysql appdb -e "SELECT SLEEP(6)" >/dev/null 2>&1 & sleep 2; mysql --table -e "SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,30) AS INFO FROM information_schema.PROCESSLIST ORDER BY TIME DESC LIMIT 5"; wait'+----+-----------------+--------------------+-------+------------------+------+-----------------------------------------------------------------+--------------------------------+| ID | USER            | HOST               | DB    | COMMAND          | TIME | STATE                                                           | INFO                           |+----+-----------------+--------------------+-------+------------------+------+-----------------------------------------------------------------+--------------------------------+|  5 | event_scheduler | localhost          | NULL  | Daemon           | 1385 | Waiting on empty queue                                          | NULL                           || 32 | repl            | 192.168.0.82:53250 | NULL  | Binlog Dump GTID |  759 | Source has sent all binlog to replica; waiting for more updates | NULL                           || 34 | repl            | 192.168.0.83:38534 | NULL  | Binlog Dump GTID |  754 | Source has sent all binlog to replica; waiting for more updates | NULL                           || 93 | root            | localhost          | appdb | Query            |    2 | User sleep                                                      | SELECT SLEEP(6)                || 94 | root            | localhost          | NULL  | Query            |    0 | executing                                                       | SELECT ID, USER, HOST, DB, COM |+----+-----------------+--------------------+-------+------------------+------+-----------------------------------------------------------------+--------------------------------+

    Expected resultConnections with their user, state, time and current statement.

    Success conditionYou can tell a leak from genuine load in one query.

  4. Restore the limit

    Back to 151, the default.

    Worth being explicit about the order of operations in a real incident: find the holder first, raise the limit only if the load is genuine. Raising it on a leak means more idle connections, each with its own buffers, and the failure returns as an out-of-memory kill instead - which is much harder to diagnose than 1040.

    bash Example session 
    sudo mysql -e "SET GLOBAL max_connections = 151"sudo mysql --table -e "SELECT @@max_connections AS maxconn, @@max_used_connections AS peak"ERROR 1193 (HY000) at line 1: Unknown system variable 'max_used_connections'[exit 1]

    Expected resultmaxconn back to 151.

    Success conditionThe limit is restored and you know when raising it is the right answer.

Troubleshooting

Official sources

← All MySQL guides