What's Actually Happening

MySQL has a maximum number of simultaneous connections limit. When this limit is reached, new connections are rejected with the "too many connections" error. This typically indicates connection leaks, insufficient max_connections, or high traffic.

The Error You'll See

```bash $ mysql -u root -p ERROR 1040 (HY000): Too many connections

# Or in application: SQLSTATE[HY000] [1040] Too many connections ```

Why This Happens

  1. 1.max_connections too low - Limit insufficient for workload
  2. 2.Connection leaks - Application not closing connections
  3. 3.Connection pool misconfigured - Pool size too large
  4. 4.Idle connections - Connections not being reused
  5. 5.Traffic spike - Sudden increase in concurrent users
  6. 6.Long-running queries - Connections held for long time

Step 1: Connect with Super Privilege

When max connections reached, you need a super user connection:

bash
# MySQL reserves 1 connection for super users
mysql -u root -p --max-connections=1

Step 2: Check Current Connections

```sql -- Count current connections SHOW STATUS LIKE 'Threads_connected';

-- Check max connections limit SHOW VARIABLES LIKE 'max_connections';

-- See all connections SHOW PROCESSLIST; ```

Output:

bash
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
|  1 | app  | localhost:12345 | mydb | Sleep   |  100 |          | NULL             |
|  2 | app  | localhost:12346 | mydb | Query   |    5 | Sending  | SELECT * FROM... |
+----+------+-----------------+------+---------+------+----------+------------------+

Step 3: Kill Idle Connections

Find and kill idle connections:

```sql -- Find sleeping connections SELECT * FROM information_schema.processlist WHERE Command = 'Sleep' AND Time > 300;

-- Kill specific connection KILL 123;

-- Kill all sleeping connections older than 5 minutes SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE Command = 'Sleep' AND Time > 300; ```

Step 4: Increase Max Connections

Temporarily:

sql
SET GLOBAL max_connections = 500;

Permanently, edit /etc/mysql/mysql.conf.d/mysqld.cnf:

ini
[mysqld]
max_connections = 500

Restart MySQL:

bash
systemctl restart mysql

Step 5: Check Connection Usage

```sql -- Current vs max SHOW STATUS LIKE 'Max_used_connections'; SHOW VARIABLES LIKE 'max_connections';

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

Step 6: Configure Wait Timeout

Reduce how long idle connections stay:

```sql -- Check current timeout SHOW VARIABLES LIKE 'wait_timeout'; SHOW VARIABLES LIKE 'interactive_timeout';

-- Set lower timeout (default 28800 = 8 hours) SET GLOBAL wait_timeout = 600; SET GLOBAL interactive_timeout = 600; ```

Persist in config:

ini
[mysqld]
wait_timeout = 600
interactive_timeout = 600

Step 7: Check Application Connection Pool

If using connection pool:

yaml
# Example for HikariCP
spring:
  datasource:
    hikari:
      maximum-pool-size: 20      # Don't exceed MySQL limit / number of app instances
      minimum-idle: 5
      idle-timeout: 300000       # 5 minutes
      connection-timeout: 30000

Pool sizing formula: `` pool_size = (core_count * 2) + disk_spindles

Step 8: Monitor Connection Metrics

```sql -- Connections per second SHOW STATUS LIKE 'Connections';

-- Threads running SHOW STATUS LIKE 'Threads_running';

-- Aborted connections (may indicate issues) SHOW STATUS LIKE 'Aborted_connects'; ```

Step 9: Check for Connection Leaks

Signs of connection leak: - Connections increase over time - Many connections in "Sleep" state - Application errors after running for a while

Fix in application code:

```python # Bad: Connection leak def get_user(user_id): conn = mysql.connect() cursor = conn.cursor() cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) return cursor.fetchone() # Connection never closed!

# Good: Proper cleanup def get_user(user_id): conn = mysql.connect() try: cursor = conn.cursor() cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) return cursor.fetchone() finally: conn.close() ```

Step 10: Set Up Monitoring

```sql -- Create monitoring user CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'password'; GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

-- Monitor query (for Prometheus/Grafana) SELECT (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected') as connections, (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='max_connections') as max_connections; ```

Verify the Fix

```bash # Check connections mysql -e "SHOW STATUS LIKE 'Threads_connected';"

# Should be less than max_connections mysql -e "SHOW VARIABLES LIKE 'max_connections';"

# Test new connection mysql -u app -p -e "SELECT 1;" ```

Prevention Tips

```ini # my.cnf settings [mysqld] max_connections = 500 # Adjust based on server capacity wait_timeout = 600 # Close idle connections after 10 minutes interactive_timeout = 600 max_connect_errors = 1000 # Allow more before blocking host

# Monitor connections over time # Alert when connections > 80% of max ```