Getting bombarded with ERROR 1040 (HY000): Too many connections is maddening because your app suddenly dies and a restart only buys you a few seconds. Don't panic — the fix is usually a quick config change, but the real problem is often deeper. Let's solve it.
The Immediate Fix: Raise max_connections
Log into MySQL as root and run:
mysql -u root -pThen check your current limit:
SHOW VARIABLES LIKE 'max_connections';If it's set to the default 151, that's your bottleneck. Increase it dynamically:
SET GLOBAL max_connections = 500;That works immediately for the current server instance. But if you restart MySQL, it reverts. To make it permanent, edit your config file. On Ubuntu/Debian that's usually /etc/mysql/mysql.conf.d/mysqld.cnf; on CentOS/RHEL it's /etc/my.cnf.
Add under the [mysqld] section:
[mysqld]
max_connections = 500Then restart:
sudo systemctl restart mysql # or mariadb / mysqldWhy This Worked
What's actually happening here is that MySQL allocates one thread per connection, plus memory for each. The default limit is conservative to prevent the server from being overwhelmed. But if your app legitimately needs more concurrent connections — say you run a PHP site with a connection pool or multiple app servers — you'll hit the ceiling fast. Raising max_connections gives MySQL permission to open more sockets and spawn more threads.
But here's the catch: just cranking it to 1000 can backfire. Each connection eats memory. With max_connections = 1000 and each thread using 8 MB, that's 8 GB of RAM just in thread stacks. So the real fix is not just raising the limit — it's finding out why you're exhausting it.
The Real Culprit: Connection Leaks
Most of the time, error 1040 isn't because you need more connections. It's because your app is holding connections open and never releasing them. This is classic with PHP's mysqli or PDO when you forget to close connections, or when a long-running script keeps a connection alive unnecessarily.
Check how many connections are actually being used:
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';If Max_used_connections is near your limit, you have a real concurrency issue. If it's high but your app should only use a handful, you've got a leak.
Each connection has a wait_timeout (default 28800 seconds = 8 hours) after which it can idle and get killed. But if your app keeps opening new connections without closing old ones, the count only goes up.
How to Fix Leaks
- Use connection pooling in your app server (e.g., PHP-FPM with
mysqli_connect— but note with PHP-FPM each worker has its own connection, so you need to tune worker counts too). - Close connections explicitly in code. In PHP:
mysqli_close($conn)or set PDO to null. - Lower
wait_timeoutto something like 60 seconds so idle connections get cleaned up faster.
SET GLOBAL wait_timeout = 60;
SET GLOBAL interactive_timeout = 60;Put those in your config too.
Less Common Variations
1. MySQL's own internal connections
MySQL reserves a few connections for the admin (usually 10). That's why you can still log in as root even when you get error 1040. But if you're using a monitoring tool that connects as root and eats those, you'll lock yourself out. Don't set max_connections too high without accounting for that.
2. MariaDB has a different default
MariaDB sometimes has a lower default, like 100. Same fix applies, but the config file may be /etc/mysql/mariadb.conf.d/50-server.cnf.
3. Host-specific errors
You might see Host 'x.x.x.x' is blocked alongside 1040. That's a different beast — it means too many connection errors from one host. Fix with FLUSH HOSTS and raise max_connect_errors.
FLUSH HOSTS;
SET GLOBAL max_connect_errors = 1000;4. Sockets vs TCP
If you're connecting via localhost, MySQL uses a Unix socket which is lightweight. But if you're connecting via TCP (e.g., remote app server), each connection uses more resources. Consider using persistent connections in your app — but only if you properly handle concurrency.
Prevention: Tune Like a Pro
First, monitor your connection usage over a week. Use SHOW GLOBAL STATUS or set up a Graphite/Grafana query. Know your baseline.
Second, set reasonable numbers. If your app needs 200 concurrent connections, don't set 1000. Use this rough formula:
max_connections = (available_ram - (innodb_buffer_pool_size + other_services)) / thread_stack_sizethread_stack is usually 256KB. So if you have 8GB RAM and InnoDB buffer pool takes 4GB, you have roughly 4GB left for threads. 4GB / 256KB = 16000, but that's optimistic. Realistically, leave a good margin.
Third, set up alerts. If your connection count regularly hits 80% of max, you'll get paged before it breaks.
Finally, if you're using an ORM like Hibernate or Django, configure its connection pool. These are the best places to control connections. A restart should be a last resort, not your fix.