MySQL "Too many connections" (error 1040): monitor connection usage and fix it for good
Fix MySQL error 1040, Too many connections: find who is holding connections, raise max_connections safely, and alert before the limit is hit.
By Shanky Gupta, Director of Softuvo Solutions (LinkedIn) · Published · Updated
ERROR 1040 (HY000): Too many connections means MySQL has reached its connection limit and is refusing new clients. The limit is max_connections, and the default is 151. Most of the time the cause is not more traffic. It is connections that are open but not doing anything, or queries that hold a connection for too long.
Work through it in this order: check how many connections are open, check the limit, find what is holding them, then fix that cause.
If you cannot log in at all
MySQL keeps one extra connection for an admin account (SUPER or CONNECTION_ADMIN privilege), so try root first:
sudo mysql -u root -pIf that also fails, you have to wait for connections to time out or restart MySQL. Restarting clears the symptom but also the evidence, so if you can get in, look first.
Step 1: how many connections are open
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';Threads_connected is the number open right now. Max_used_connections is the highest number since MySQL last started. If Max_used_connections equals your limit, you have hit it before, even if things look fine now.
Step 2: what is the limit
SHOW VARIABLES LIKE 'max_connections';If Threads_connected is regularly above 80% of this number, you are close to the edge and a traffic spike will tip you over.
Step 3: what is holding the connections
This groups connections by user, host and what they are doing:
SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, command, COUNT(*) AS conns, MAX(time) AS longest_s
FROM information_schema.processlist
GROUP BY user, client, command
ORDER BY conns DESC;How to read the result:
- Many rows with command Sleep and a high longest_s: the app opens connections and does not close them, or keeps too many idle in its pool. This is the most common cause.
- Many rows with command Query and a high longest_s: slow queries are holding connections. Look at them with SHOW FULL PROCESSLIST;
- Most connections from one host or one user: that app server or worker is the one to look at.
Step 4: fix the cause
Fix what step 3 showed you:
- Idle connections piling up: lower wait_timeout so MySQL closes idle connections sooner, for example SET GLOBAL wait_timeout = 300; (the default is 28800, eight hours). Do not go very low if your app uses a connection pool, or the pool will hand out connections MySQL has already closed. Also check that the app closes connections on error paths.
- Too many app processes, each with its own connection: add up workers × connections per worker across every app server. That total must stay under max_connections. Reduce pool sizes, or put ProxySQL in front of MySQL to share connections.
- A slow query holding connections: end it with KILL <id>; using the Id from SHOW FULL PROCESSLIST, then fix the query or add the missing index so it does not come back.
Raising max_connections, if you really need more
Only raise the limit once the cause above is fixed and you still need more. Each connection uses memory, so check you have room. This shows roughly how much each connection can use, in MB:
SELECT (@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @@thread_stack) / 1024 / 1024
AS mb_per_connection;Multiply that by the new limit and make sure the result, plus innodb_buffer_pool_size, fits in RAM with room to spare. Then raise it without a restart:
SET GLOBAL max_connections = 300;To keep it after a restart, add max_connections = 300 under [mysqld] in /etc/mysql/mysql.conf.d/mysqld.cnf (or your distro's equivalent).
Get a warning before it happens again
Alert when Threads_connected passes 80% of max_connections. With Prometheus and mysqld_exporter, that is mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8 for 5 minutes. Without Prometheus, a cron job that runs both SHOW statements every minute and posts to Slack above 80% does the same job.
Also watch Aborted_connects (SHOW STATUS LIKE 'Aborted_connects';). If it keeps going up, clients are already failing to connect during short bursts.
Opservo tracks Threads_connected against max_connections on every MySQL server it finds and warns at 80%, so you see the trend before you see error 1040.
Read next in the server health and monitoring series: Redis memory usage and eviction: how to monitor it before keys start disappearing and Monitor Docker container memory and CPU usage: docker stats, sorting, limits, alerts. The whole series is at Server health and monitoring.