MySQL Too Many Connections: Fix ERROR 1040 Fast (All Causes Covered)

Understanding the MySQL Too Many Connections Error

When MySQL returns ERROR 1040 (HY000): Too many connections, it means every slot in the connection pool (controlled by max_connections) is occupied and the server cannot accept another TCP connection. The client — whether your application, a cron job, or a CLI tool — receives the error immediately without ever executing a query.

This error surfaces in many forms depending on your stack:

• Raw MySQL client: ERROR 1040 (HY000): Too many connections
• PHP/Laravel: SQLSTATE[HY000] [1040] Too many connections
• Python/Django: django.db.utils.OperationalError: (1040, 'Too many connections')
• Node.js/mysql2: Error: Too many connections
• HTTP layer (nginx → app → MySQL): 502 Bad Gateway when the application itself crashes or hangs waiting for a DB handle

Step 1: Confirm the Diagnosis

SSH into the MySQL host and check current connection state:

mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -u root -p -e "SHOW STATUS LIKE 'Max_used_connections';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

If Threads_connected equals max_connections, you have confirmed the error. Max_used_connections tells you the historical peak — if it consistently matches max_connections, the limit is too low or there is a leak.

Check for sleeping (leaked) connections:

SELECT user, host, db, command, time, state
FROM information_schema.processlist
WHERE command = 'Sleep'
ORDER BY time DESC
LIMIT 30;

A large number of rows with Command = Sleep and high Time values (hundreds or thousands of seconds) indicates connection leaks.

Step 2: Immediate Relief — Raise max_connections Live

This does not require a MySQL restart and takes effect instantly:

SET GLOBAL max_connections = 500;

Choose a value based on available RAM. A rough formula is:

max_connections ≈ (Available RAM in MB - global buffer RAM) / per_connection_RAM

Each connection uses approximately 1–8 MB depending on sort_buffer_size, join_buffer_size, and tmp_table_size. On a server with 8 GB RAM allocating 4 GB to InnoDB buffer pool, a safe ceiling is 300–500 connections. Setting it too high (e.g., 10 000) can cause the MySQL process itself to OOM-kill.

Step 3: Kill Stale Sleeping Connections

If the pool is already full before you can raise the limit, kill sleeping threads:

-- Generate KILL statements for all sleeping connections older than 300 seconds
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 300;

Copy the output and run each KILL <id>; statement, or use a shell loop:

mysql -u root -p -e "
  SELECT CONCAT('KILL ', id, ';')
  FROM information_schema.processlist
  WHERE command = 'Sleep' AND time > 300;
" --skip-column-names | mysql -u root -p

Step 4: Make the Fix Permanent in my.cnf

Edit /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian) or /etc/my.cnf (RHEL/CentOS):

[mysqld]
max_connections        = 500
wait_timeout           = 300
interactive_timeout    = 300
connect_timeout        = 10

wait_timeout and interactive_timeout control how long MySQL keeps an idle connection open. Dropping them from the default 28800 seconds (8 hours) to 300 seconds (5 minutes) reclaims leaked connections automatically.

Reload without full restart (MySQL 8.0+):

mysqladmin -u root -p reload
# or for a clean restart:
systemctl restart mysql

Step 5: Investigate MySQL Crash, OOM, and Disk Full

MySQL not starting / crashed:

journalctl -u mysql -n 100 --no-pager
cat /var/log/mysql/error.log | tail -100

Common crash signatures:

• [ERROR] InnoDB: ib_logfile0 size ... is not equal to innodb_log_file_size — log file size mismatch after config change.
• [ERROR] Can't start server: Bind on TCP/IP port. Got error: 98: Address already in use — port conflict; check ss -tlnp | grep 3306.
• Out of memory (oom_kill_score ...) in dmesg — MySQL OOM-killed by the kernel.

Disk full causing MySQL failure:

df -h /var/lib/mysql
du -sh /var/lib/mysql/*
# Free space by purging old binary logs safely:
mysql -u root -p -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"

Never manually delete .ibd or ib_logfile* files. Only purge binary logs via MySQL's own PURGE BINARY LOGS command.

High CPU / slow queries:

# Enable slow query log temporarily
mysql -u root -p -e "
  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 1;
  SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
"
# Then analyze:
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

High CPU combined with many connections usually means N+1 query patterns, missing indexes, or a runaway reporting query. Use SHOW PROCESSLIST to identify the offending query and EXPLAIN to optimize it.

Step 6: Long-Term Fix — Connection Pooling

For applications with bursty traffic, implement a connection pooler in front of MySQL:

• ProxySQL — transparent proxy with advanced connection multiplexing, query routing, and query caching. Recommended for production MySQL setups.
• MySQL Router — lightweight official router suitable for InnoDB Cluster topologies.
• Application-level pooling — SQLAlchemy pool_size + max_overflow, HikariCP for Java, mysql2 pool in Node.js.

With ProxySQL, 1000 application connections can map to 50 real MySQL connections, effectively multiplying your capacity without increasing server load.

Step 7: Monitor to Prevent Recurrence

Add these metrics to your monitoring stack (Prometheus + mysqld_exporter, Datadog, or New Relic):

• mysql_global_status_threads_connected — alert at 80% of max_connections
• mysql_global_status_connection_errors_max_connections — should be 0
• mysql_global_status_aborted_connects — rising values indicate connection failures
• Disk usage on /var/lib/mysql — alert at 75% full

A Prometheus alert rule:

- alert: MySQLTooManyConnections
  expr: |
    mysql_global_status_threads_connected /
    mysql_global_variables_max_connections > 0.8
  for: 2m
  labels:
    severity: warning
  annotations:
    summary: "MySQL connections > 80% of max_connections"