Home / Databases / MySQL

DatabasesTroubleshootingBeginner

MySQL 'Too many connections' (error 1040): find the cause and fix it

Get in when every slot is taken, see which users and hosts hold connections, kill sleepers and long queries, and right-size max_connections and pools.

Deepak Bhardwaj2 min read

Applies toMySQL 5.7/8.x, Amazon RDS/Aurora MySQL, Azure Database for MySQL Flexible Server, OCI MySQL HeatWave

The error

ERROR 1040 (HY000): Too many connections

1. Get in

MySQL keeps one extra connection for a user with CONNECTION_ADMIN (or SUPER). MySQL 8.0.14+ can also have a separate admin port:

# my.cnf
admin_address = 127.0.0.1
admin_port = 33062

mysql -h 127.0.0.1 -P 33062 -u root -p

2. Who is connected?

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, command, COUNT(*) AS n,
       MAX(time) AS max_seconds
FROM information_schema.processlist
GROUP BY user, client, command
ORDER BY n DESC;

3. Read the pattern

Pattern Cause Fix
Hundreds of Sleep from one app Pool too large, or connections leaked (not closed) Smaller pool, fix the leak, lower wait_timeout
Many Query piling up Queries are slow or blocked; new requests open more connections Find the blocker (below)
Many Locked / waiting for lock A long transaction or DDL holds a lock Find and end it

4. Kill safely

-- Sleeping for over 10 minutes
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 600;

-- Blocking transactions (InnoDB)
SELECT r.trx_mysql_thread_id AS waiting, b.trx_mysql_thread_id AS blocking, b.trx_started, b.trx_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

Run the generated KILL statements after reviewing them. On RDS use CALL mysql.rds_kill(<id>);.

5. Settings

SET GLOBAL wait_timeout = 600;          -- idle non-interactive connections closed after 10 min
SET GLOBAL max_connections = 500;       -- only if memory allows

Persist them in my.cnf or with SET PERSIST (8.0), or in the parameter group on managed services.

Note

Each connection uses memory (thread stack plus per-session buffers). Raising max_connections from 500 to 5,000 without checking memory can turn this error into an out-of-memory crash.

6. The lasting fix

  • Pool size × number of app instances must stay under max_connections, with room for admins and replication.
  • Use a proxy for bursty or serverless workloads: ProxySQL, RDS Proxy or MySQL Router.
  • Always close connections in finally blocks, or use the framework's pool.

Tip

Graph Threads_connected and Threads_running separately. Many connected and few running = idle pools. Many running = the database is the bottleneck.

Related fixes