# MySQL Too Many Connections: Max Connections Reached

Your application suddenly starts failing with this error:

bash
ERROR 1040 (HY000): Too many connections

This error means MySQL has reached its maximum connection limit and cannot accept new connections. Left untreated, this brings down your entire application. Here's how to diagnose and fix it.

Understanding the Error

MySQL has a configurable limit on simultaneous connections. When this limit is reached, new connection attempts fail. The default is 151 connections, which includes one reserved for the SUPER user.

Check your current limit:

sql
SHOW VARIABLES LIKE 'max_connections';

Check current connection count:

sql
SHOW STATUS LIKE 'Threads_connected';

Immediate Recovery: Get Connected

When you can't connect due to max connections, you need a way in to fix it.

Option 1: Use the reserved SUPER connection

MySQL reserves one extra connection for accounts with SUPER or CONNECTION_ADMIN privilege:

bash
mysql -u root -p

Option 2: Increase max_connections at runtime

If you can connect, increase the limit immediately:

sql
SET GLOBAL max_connections = 500;

This takes effect instantly without restart but resets on MySQL restart.

Option 3: Kill idle connections

Free up connections by terminating idle or problematic ones:

```sql -- See all connections SHOW PROCESSLIST;

-- Or more detailed view SELECT * FROM information_schema.processlist ORDER BY time DESC;

-- Kill specific connection KILL 12345; ```

Kill multiple idle connections at once:

```sql -- Generate kill commands for sleeping connections SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE command = 'Sleep' AND time > 60 INTO OUTFILE '/tmp/kill_idle.sql';

-- Execute the commands SOURCE /tmp/kill_idle.sql; ```

Step 1: Diagnose Connection Usage

Understanding why connections are exhausted helps prevent recurrence.

Check connection patterns:

```sql -- Current connections by user SELECT user, host, COUNT(*) as conn_count FROM information_schema.processlist GROUP BY user, host ORDER BY conn_count DESC;

-- Current connections by database SELECT db, COUNT(*) as conn_count FROM information_schema.processlist GROUP BY db ORDER BY conn_count DESC;

-- Connections by state SELECT state, COUNT(*) as conn_count FROM information_schema.processlist GROUP BY state ORDER BY conn_count DESC;

-- Long-running queries SELECT id, user, host, db, time, state, info FROM information_schema.processlist WHERE time > 30 AND command != 'Sleep' ORDER BY time DESC; ```

Check connection statistics:

sql
SHOW STATUS LIKE '%connect%';

Key metrics: - Connections: Total connection attempts since server start - Max_used_connections: Peak connections since server start - Threads_connected: Current open connections - Aborted_connects: Failed connection attempts

Check for connection leaks:

sql
-- Show connections open for a long time
SELECT id, user, host, db, time, state
FROM information_schema.processlist
WHERE time > 300
ORDER BY time DESC;

Step 2: Permanently Increase max_connections

Edit your MySQL configuration file:

bash
# Find config location
mysql --help | grep "Default options" -A 1

Edit /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf:

ini
[mysqld]
max_connections = 500

Restart MySQL:

bash
sudo systemctl restart mysql

Important considerations:

  • Each connection uses memory (stack, buffers)
  • Check your available RAM: free -h
  • Each connection needs at least 256KB for stack alone
  • 500 connections x 256KB = 128MB just for thread stacks
  • Add buffers: read_buffer_size, sort_buffer_size, etc.

Check per-connection memory usage:

sql
SHOW VARIABLES WHERE Variable_name IN (
    'thread_stack',
    'read_buffer_size',
    'read_rnd_buffer_size',
    'sort_buffer_size',
    'join_buffer_size',
    'binlog_cache_size'
);

Step 3: Configure Connection Timeouts

Prevent idle connections from accumulating:

```ini [mysqld] # Close idle connections after 8 hours (default) wait_timeout = 28800

# Or more aggressive: 30 minutes wait_timeout = 1800 interactive_timeout = 1800 ```

Apply without restart:

sql
SET GLOBAL wait_timeout = 1800;
SET GLOBAL interactive_timeout = 1800;

Step 4: Optimize Application Connection Pooling

The real solution is proper connection pooling in your application.

Connection pooling best practices:

  1. 1.Use a connection pool library
  2. 2.Set appropriate pool size
  3. 3.Configure connection validation
  4. 4.Set idle connection timeout

Example pool configuration (Node.js with mysql2):

javascript
const pool = mysql.createPool({
  host: 'localhost',
  user: 'app_user',
  password: 'password',
  database: 'myapp',
  connectionLimit: 20,  // Don't exceed what MySQL allows
  queueLimit: 0,
  waitForConnections: true,
  idleTimeout: 30000,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

Example (Python with SQLAlchemy):

```python from sqlalchemy import create_engine

engine = create_engine( 'mysql+pymysql://user:pass@localhost/db', pool_size=10, max_overflow=20, pool_timeout=30, pool_recycle=1800, pool_pre_ping=True ) ```

Example (Java with HikariCP):

java
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setIdleTimeout(300000);
config.setConnectionTimeout(30000);
config.setMaxLifetime(1800000);

Step 5: Monitor Connection Metrics

Set up monitoring to catch issues before they become outages.

Key queries for monitoring:

```sql -- Connection usage percentage SELECT ROUND(Threads_connected / max_connections * 100, 2) as usage_pct, Threads_connected, VARIABLE_VALUE as max_connections FROM (SELECT VARIABLE_VALUE as Threads_connected FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected') t, (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'max_connections') m;

-- Connection errors SHOW STATUS LIKE 'Connection_errors%'; ```

Set up alerts for: - Connection usage > 80% - Aborted connects increasing rapidly - Long-running connections detected

Step 6: Handle Connection Bursts Gracefully

Configure MySQL to handle sudden spikes:

```ini [mysqld] # Extra connections beyond max_connections # Only available to SUPER users max_connect_errors = 1000

# Backlog of pending connections back_log = 100 ```

Verification

After making changes, verify everything works:

```bash # Test connection mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

# Monitor connections mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected';"

# Stress test (optional) for i in {1..100}; do mysql -u app_user -ppassword -e "SELECT 1" & done wait ```

Troubleshooting Checklist

  • [ ] Connect using SUPER account
  • [ ] Identify who is using connections
  • [ ] Kill idle connections to free space
  • [ ] Increase max_connections temporarily
  • [ ] Update configuration permanently
  • [ ] Configure wait_timeout appropriately
  • [ ] Implement connection pooling
  • [ ] Set up monitoring
  • [ ] Test under load

Quick Reference Commands

```sql -- Check current limit SHOW VARIABLES LIKE 'max_connections';

-- Check current usage SHOW STATUS LIKE 'Threads_connected';

-- Temporarily increase limit SET GLOBAL max_connections = 500;

-- Find idle connections SELECT id, user, time FROM information_schema.processlist WHERE command = 'Sleep' ORDER BY time DESC;

-- Kill connection KILL connection_id;

-- Permanent fix (in config file) -- max_connections = 500 ```

With proper connection pooling and monitoring, you should rarely see "too many connections" errors. The key is preventing connection leaks and sizing your pool appropriately for your traffic.