ERROR 1040 (HY000): Too many connections means MySQL has handed out every slot max_connections allows. Raising the limit buys time, but each connection costs memory, and the next autoscale fills it again. The lasting fix bounds the connections your app opens, and ProxySQL is the usual tool for that.
What breaks at peak
MySQL refuses a login once Threads_connected reaches max_connections, 151 by default. The client gets this:
ERROR 1040 (HY000): Too many connections
The server reports error 1040 with SQLSTATE 08004, so some drivers print ERROR 1040 (08004). The mysql client and many others show HY000, because the refusal comes during the handshake. MariaDB uses the same code and text. Java apps see it wrapped as java.sql.SQLNonTransientConnectionException, and PHP’s PDO as SQLSTATE[HY000] [1040] Too many connections.
A per-account cap fails with a different error, so check which one you have:
ERROR 1203 (42000): User app already has more than 'max_user_connections' active connections
MySQL keeps one extra slot past max_connections for an account with CONNECTION_ADMIN, or SUPER on older versions. If that slot is taken too, you can’t log in to see what’s wrong. From MySQL 8.0.14 you can open a separate admin port that max_connections doesn’t count:
# changed lines only, in my.cnf
[mysqld]
admin_address = 127.0.0.1
admin_port = 33062
Connect to it with mysql -h 127.0.0.1 -P 33062 -u root -p. It needs a restart to turn on, so add it before the next incident. The account also needs SERVICE_CONNECTION_ADMIN.
The connection math. Every app process holds its own pool. Total connections are pods × processes per pod × pool size, plus cron jobs, migrations and monitoring. An autoscaler that doubles the pods at peak doubles that total. Idle connections count the same as busy ones. A pool of 20 that runs two queries at a time still holds 20 slots.
Sleeping connections. A client that dies without closing its socket leaves a Sleep thread behind. MySQL closes it after wait_timeout, which defaults to 28800 seconds, eight hours. List the oldest. On MariaDB, query information_schema.processlist instead:
SELECT id, user, host, db, command, time
FROM performance_schema.processlist
WHERE command = 'Sleep'
ORDER BY time DESC
LIMIT 20;
The limit you set may not be the limit you got. With a low open files limit, MySQL lowers max_connections at startup and logs it:
Changed limits: max_connections: 214 (requested 1000)
Check SELECT @@max_connections; after every restart. Under systemd, raise LimitNOFILE in a drop-in for the mysqld service.
The fix
Size max_connections from memory. Each connection holds per-thread buffers, allocated as queries need them:
SELECT @@thread_stack, @@sort_buffer_size, @@join_buffer_size,
@@read_buffer_size, @@read_rnd_buffer_size, @@binlog_cache_size,
@@tmp_table_size;
The worst case per connection is about the sum of those, and a join can take several join buffers. Your budget is the RAM left after innodb_buffer_pool_size and the OS. Divide it by a realistic per-connection figure, not the worst case. Measure that figure on staging with performance_schema.memory_summary_by_thread_by_event_name under load. Then set the limit with headroom:
SET PERSIST max_connections = 500;
SET PERSIST wait_timeout = 600;
SET PERSIST max_user_connections = 400;
SET PERSIST applies the value now and keeps it across restarts. MariaDB has no SET PERSIST, so use SET GLOBAL there and write the same values to my.cnf. wait_timeout = 600 closes connections idle for ten minutes. Keep it longer than your pool’s idle timeout, or the pool hands out sockets MySQL already closed. max_user_connections stops the app account from taking every slot, so your admin and monitoring logins still work.
A higher max_connections alone doesn’t help much past a point. CPU-bound MySQL slows down when hundreds of threads run at once. Watch Threads_running, not Threads_connected. Once it sits well above your core count, queries queue inside InnoDB. Percona Server and MariaDB offer thread_handling = pool-of-threads for that, and MySQL Enterprise has a thread pool plugin.
Put ProxySQL in front. ProxySQL accepts thousands of client connections and runs their queries over a small pool of backend connections. Between transactions, a backend connection goes back to the pool and serves another client. ProxySQL calls this multiplexing.
Configure it on the admin interface, port 6032. Replace the hosts and passwords:
-- mysql -u admin -p -h 127.0.0.1 -P 6032
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (10, 'mysql-primary.internal', 3306, 300);
INSERT INTO mysql_users (username, password, default_hostgroup, max_connections)
VALUES ('app', 'secret', 10, 5000);
UPDATE global_variables SET variable_value = '5000'
WHERE variable_name = 'mysql-max_connections';
UPDATE global_variables SET variable_value = '3000'
WHERE variable_name = 'mysql-connect_timeout_server_max';
UPDATE global_variables SET variable_value = 'proxysql_monitor'
WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'monitor-secret'
WHERE variable_name = 'mysql-monitor_password';
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
Create the monitor user on MySQL too, with CREATE USER 'proxysql_monitor'@'%' IDENTIFIED BY 'monitor-secret'; and GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'%';. Then point the app at ProxySQL’s client port, 6033:
- DATABASE_URL=mysql://app:secret@mysql-primary.internal:3306/app
+ DATABASE_URL=mysql://app:secret@proxysql.internal:6033/app
Each value, and what it controls:
mysql_servers.max_connectionscaps ProxySQL’s backend connections to that server. Keep the sum across every ProxySQL instance below MySQL’smax_connections, minus slots for admin, replication and jobs that bypass the proxy.mysql_users.max_connectionscaps client connections for that account at the proxy.mysql-max_connectionscaps all client connections to one ProxySQL instance, 2048 by default. Past it, clients getToo many connectionsfrom ProxySQL itself.mysql-connect_timeout_server_maxis how long a client waits for a backend connection, 10000 ms by default. Set it below your request timeout, so a full pool fails fast.- The monitor user lets ProxySQL check each backend’s health. With replicas in a reader hostgroup, it also reads replica lag, as the replica lag recipe explains.
If every backend connection stays busy past the timeout, the client gets:
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 10 after 3000ms
That means the pool is too small or the queries are too slow. It’s the same queueing PgBouncer does for Postgres, described in the PgBouncer recipe.
Session state breaks multiplexing. ProxySQL pins a client to its backend connection while the session holds state on it. That covers open transactions, LOCK TABLES, GET_LOCK(), temporary tables, SQL_CALC_FOUND_ROWS and session user variables such as SET @x. A pinned client holds a backend slot the whole time. Check stats_mysql_connection_pool under load. If ConnUsed tracks your client count, something in your driver or ORM pins every session. Common causes are a driver that sets a session variable ProxySQL doesn’t track, or long transactions left open.
Authentication. MySQL 8 defaults to caching_sha2_password. ProxySQL supports it for client connections from 2.6. On older ProxySQL, the app account needs mysql_native_password, which MySQL 9 no longer ships.
Size the app pools too. With ProxySQL in front, app pools can grow without touching MySQL. Keep each pod’s pool near the concurrency it needs, and set its idle timeout below wait_timeout. I set mysql_servers.max_connections from the load test below and keep MySQL’s max_connections as a safety margin above it.
Placement. Run at least two ProxySQL instances behind a load balancer or a Kubernetes Service, with the same config on each. ProxySQL Cluster can sync the config tables between them. Each instance opens its own backend pool, so divide the backend budget between them.
Managed MySQL. On Amazon RDS and Aurora, max_connections lives in the DB parameter group, and its default comes from a formula based on instance memory. Azure Database for MySQL and OCI MySQL HeatWave set the default from the instance size, and Cloud SQL from its memory. All four let you change it, and none gives you SUPER or the admin port. ProxySQL works in front of each, with the monitor user created through the admin account. On AWS, RDS Proxy is the managed alternative, covered in the RDS Proxy recipe.
Test it on your system
Use a staging MySQL with production’s max_connections, buffer sizes and instance size. Load the data with sysbench, then compare a direct run with a run through ProxySQL. Use more client threads than max_connections and a fixed rate:
sysbench oltp_read_write --mysql-host=mysql-staging.internal --mysql-port=3306 \
--mysql-user=app --mysql-password=secret --mysql-db=sbtest \
--tables=10 --table-size=1000000 prepare
# direct, expect ERROR 1040 once threads pass max_connections
sysbench oltp_read_only --mysql-host=mysql-staging.internal --mysql-port=3306 \
--mysql-user=app --mysql-password=secret --mysql-db=sbtest --tables=10 \
--threads=800 --rate=2000 --time=600 --report-interval=10 --percentile=99 run
# through ProxySQL at the same rate
sysbench oltp_read_only --mysql-host=proxysql.internal --mysql-port=6033 \
--mysql-user=app --mysql-password=secret --mysql-db=sbtest --tables=10 \
--threads=800 --rate=2000 --time=600 --report-interval=10 --percentile=99 run
Set --rate to your peak transactions per second. sysbench then holds that rate whatever the database does, and its reports show how many transactions wait in its queue. Replace the sysbench workload with a replay of your own queries once the setup works.
While each run goes, watch MySQL and the proxy:
-- on MySQL
SHOW GLOBAL STATUS WHERE Variable_name IN
('Threads_connected', 'Threads_running', 'Max_used_connections',
'Connection_errors_max_connections', 'Aborted_connects');
-- on the ProxySQL admin interface
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnERR, Queries, Latency_us
FROM stats_mysql_connection_pool;
Write your targets down before the ProxySQL run. It passes when no 1040 or 9001 error appears, and Threads_connected on MySQL stays at or under the backend cap you set. sysbench’s p99 and queue length should meet your latency budget, with ConnERR at zero. If Threads_running stays far above your core count, lower the backend cap. Queries then queue in ProxySQL, where they cost no MySQL memory.
Keep it healthy
Scrape MySQL with mysqld_exporter and its default collectors. These rules warn before the limit, catch refused logins and flag thread pile-ups:
groups:
- name: mysql-connections
rules:
- alert: MySQLConnectionsNearMax
expr: |
max by (instance) (mysql_global_status_threads_connected)
/ on (instance) max by (instance) (mysql_global_variables_max_connections) > 0.85
for: 5m
labels:
severity: warning
annotations:
summary: "{{ $labels.instance }} is using over 85% of max_connections"
description: "Find the hosts and users holding connections in performance_schema.processlist. Shorten wait_timeout for sleepers, or route the client through ProxySQL."
- alert: MySQLConnectionsRefused
expr: increase(mysql_global_status_connection_errors_total{error="max_connections"}[5m]) > 0
for: 1m
labels:
severity: critical
annotations:
summary: "{{ $labels.instance }} is refusing logins with ERROR 1040"
description: "Clients fail now. Log in on the admin port, kill the oldest Sleep threads, then check which app scaled up. Fix the pool totals before you raise max_connections."
- alert: MySQLThreadsRunningHigh
expr: max by (instance) (mysql_global_status_threads_running) > 64
for: 10m
labels:
severity: warning
annotations:
summary: "{{ $labels.instance }} has over 64 threads running queries"
description: "Queries are queueing inside MySQL. Set 64 to about twice your core count. Check slow queries in performance_schema, then lower ProxySQL's backend max_connections."
- alert: MySQLAbortedConnects
expr: rate(mysql_global_status_aborted_connects[5m]) > 1
for: 10m
labels:
severity: warning
annotations:
summary: "{{ $labels.instance }} has over one failed login a second"
description: "Check the MySQL error log for the reason: wrong passwords, handshake timeouts or connections refused at the limit."
Also alert on Max connect timeout reached while reaching hostgroup in the ProxySQL log. It means clients waited for a backend connection until the timeout.
Roll back
Save the current values before the change:
SHOW GLOBAL VARIABLES
WHERE Variable_name IN ('max_connections', 'wait_timeout', 'max_user_connections');
To undo a persisted value, set it back, or remove the persisted entry and keep the current runtime value until the next restart:
SET PERSIST max_connections = 151; -- your saved value
RESET PERSIST wait_timeout;
To take ProxySQL out of the path, point the app’s connection string back at port 3306 and redeploy. First check the app’s total pool across all pods fits under max_connections, or you swap queueing for 1040 errors. For a bad ProxySQL change you haven’t saved, LOAD MYSQL SERVERS FROM DISK; followed by LOAD MYSQL SERVERS TO RUNTIME; restores the saved config.
If the change causes errors, such as clients failing on authentication after the switch, roll back now. Otherwise wait for a low-traffic window, when the fewest pods are running.
More recipes
- Monitor MySQL Replication Lag with Prometheus
Alert on MySQL replica lag, stopped replication threads and missing Seconds_Behind_Master values, so stale reads get caught before users report them.
- Improve Cache Hit Ratio to Protect Your Origin at Peak
Trim the cache key, send cache headers from the origin and serve stale while revalidating, so the CDN absorbs peak traffic instead of passing it to your origin.
- Fix ALB 502 Bad Gateway and 504 Gateway Timeout
Set each target's keep-alive above the ALB idle timeout and keep slow requests under it, so the load balancer stops answering peak traffic with 502 and 504.
- Fix Lambda TooManyRequestsException Rate Exceeded
Size Lambda concurrency from peak traffic and split it with reserved and provisioned concurrency, so critical functions stop throttling when traffic spikes.