How to Diagnose and Fix MySQL Too Many Connections Errors in Production

Background

MySQL "Too many connections" is a common emergency failure in production environments. It means the MySQL server cannot allocate a new connection handle, so new client requests are rejected. Causes include traffic spikes, slow‑query blocking, connection leaks, or mis‑configured parameters. Without a clear troubleshooting plan, operators may repeatedly restart the service, which can cause data corruption or downtime.

This article uses MySQL 8.0.39 as the reference version and a 2026 monitoring stack (Prometheus + AlertManager + Grafana) to provide a complete workflow from emergency stop‑gap to root‑cause analysis and long‑term remediation. Prerequisites: basic Linux commands (top, ps, netstat) and familiarity with MySQL architecture (connection manager, thread pool, storage engine).

1. Problem Symptoms and Immediate Assessment

1.1 Typical Indicators

Application logs contain SQLSTATE[HY000] [2002] Can't connect to MySQL server or ERROR 1040 (HY000): Too many connections.

Some API calls time out while direct MySQL queries still work.

Root can log in locally, but normal users cannot establish new connections (e.g., root@localhost still works).

Step 1: Verify the host is alive.

# Check if MySQL process is running
ps aux | grep mysqld | grep -v grep

# Check if port 3306 is listening
ss -tlnp | grep 3306
# or
netstat -tlnp | grep 3306

# Check system load
uptime
w

Step 2: Verify connection count status.

# Direct socket connection (no TCP pool)
mysql -u root -p -S /var/lib/mysql/mysql.sock -e "SHOW PROCESSLIST;" 2>/dev/null | wc -l

If SHOW PROCESSLIST fails because the connection limit is reached but the MySQL process is still alive, you can inspect the error log and OS‑level connection counts:

# View recent connection‑refused messages in the error log
tail -100 /var/log/mysql/error.log | grep -i "too many connections"

# Count OS‑level MySQL connections
ss -ant | grep :3306 | wc -l

# Show per‑IP connection distribution
ss -ant | grep :3306 | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -rn

Step 3: Compare current connections with max_connections .

# Show current and max connections
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

Key metrics: Threads_connected: total established connections (including sleeping ones). Threads_running: connections actively executing SQL (excluding I/O‑waiting).

The real danger is a surge in Threads_running. When it approaches max_connections, the server is severely overloaded.

2. Quick Stop‑Gap Operations

Warning: The following actions are invasive. Ensure you have backups and have notified relevant business owners before proceeding.

2.1 Kill Idle Sleep Connections

Most overload cases have many connections stuck in Sleep state. If wait_timeout is not set, these connections can occupy handles indefinitely. Killing them provides immediate relief.

# Kill all sleep connections (ensure no long‑running transactions)
KILL CONNECTION IF EXISTS (
  SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST
  WHERE COMMAND = 'Sleep' AND TIME > 30
);

If the above syntax is unsupported, generate kill statements via a cursor:

# MySQL 8.0 syntax
SELECT CONCAT('KILL ', ID, ';')
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 30
INTO OUTFILE '/tmp/kill_sleep.sql';
SOURCE /tmp/kill_sleep.sql;

Shell one‑liner to clean sleep connections:

#!/bin/bash
# kill_mysql_sleep_conn.sh
# Usage: ./kill_mysql_sleep_conn.sh 30 (threshold seconds)
SOCKET="/var/lib/mysql/mysql.sock"
THRESHOLD=${1:-30}
mysql -u root -p -S "$SOCKET" -N -e "SELECT CONCAT('KILL ', ID, ';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Sleep' AND TIME > $THRESHOLD;" | mysql -u root -p -S "$SOCKET"
echo "$(date '+%Y-%m-%d %H:%M:%S') - Cleaned sleep connections"

2.2 Temporarily Increase max_connections

If killing sleep connections is insufficient, raise the limit without restarting MySQL:

# Dynamic adjustment (no restart required)
SET GLOBAL max_connections = 500;

Or edit my.cnf and reload:

# Backup original config
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak.$(date +%Y%m%d%H%M%S)

# Temporarily raise connections
sed -i '/^\\[mysqld\"]/a max_connections=500' /etc/mysql/my.cnf

# Verify
mysql -u root -S /var/lib/mysql/mysql.sock -e "SHOW VARIABLES LIKE 'max_connections';"

2.3 Kill Specific Slow Queries

Identify long‑running queries and terminate them:

# Find top 10 longest‑running queries (excluding Sleep)
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,100) AS QUERY
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND!='Sleep'
ORDER BY TIME DESC
LIMIT 10;

Shell script to kill queries exceeding a threshold (default 60 s):

#!/bin/bash
# kill_long_query.sh
# Usage: ./kill_long_query.sh 60
SOCKET="/var/lib/mysql/mysql.sock"
QUERY_TIME=${1:-60}
mysql -u root -S "$SOCKET" -N -e "SELECT CONCAT('KILL QUERY ', ID, ';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND!='Sleep' AND TIME > $QUERY_TIME;" | mysql -u root -S "$SOCKET"
echo "$(date) - Killed queries running > $QUERY_TIME seconds"

3. Root‑Cause Analysis

3.1 Slow Queries Accumulate Connections

When a query runs for a long time (e.g., >30 s), its connection stays in Sending data or Sorting result state, preventing reuse. Concurrent slow queries quickly fill the connection pool.

How to locate slow queries:

# Using performance_schema
SELECT DIGEST, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 AS SUM_SECONDS,
       AVG_TIMER_WAIT/1000000000000 AS AVG_SECONDS,
       LEFT(SQL_TEXT,200) AS SQL_SAMPLE
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Typical scenarios:

Uncommitted large transactions : A transaction holds locks for a long time.

Missing indexes causing full table scans : Scanning 100 k rows to return 10 rows.

Deep pagination : Repeated LIMIT offset, n leads to many back‑track operations.

3.2 Connection Leak (Application Fails to Close)

If an application does not close connections after use, each request leaks a handle. Over time, the pool is exhausted.

Leak‑detection script:

#!/bin/bash
# detect_conn_leak.sh
mysql -u root -S /var/lib/mysql/mysql.sock -e "
SELECT USER, HOST, COUNT(*) AS CONN_COUNT, MAX(TIME) AS MAX_TIME, MIN(TIME) AS MIN_TIME
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND='Sleep'
GROUP BY USER, HOST
HAVING COUNT(*) > 10
ORDER BY CONN_COUNT DESC;"

If MAX_TIME is large for a given user/IP, that application likely leaks connections.

3.3 Short‑Connection Burst

Languages like PHP or Python often use short‑connection mode, creating a new TCP connection for each request. Sudden traffic spikes can outpace MySQL's ability to accept new connections.

Detect short‑connection pattern:

# Connections per minute
SELECT DATE_FORMAT(CREATED, '%Y-%m-%d %H:%i') AS MINUTE, COUNT(*) AS NEW_CONNECTIONS
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND='Sleep'
GROUP BY MINUTE
ORDER BY NEW_CONNECTIONS DESC
LIMIT 10;

# System‑level counters
mysql -u root -S /var/lib/mysql/mysql.sock -e "SHOW STATUS LIKE 'Connections';"
mysql -u root -S /var/lib/mysql/mysql.sock -e "SHOW STATUS LIKE 'Aborted_connects';"

If Aborted_connects rises continuously, many short connections are being rejected.

Configuration to mitigate:

# my.cnf adjustments
[mysqld]
max_connections = 1000          # allow more concurrent connections
wait_timeout = 600              # 10 min idle timeout
interactive_timeout = 600
connect_timeout = 10
# Enable connection pool (MySQL 8.0 Enterprise) or use ProxySQL

3.4 max_connections Set Too Low

MySQL 8.0 defaults to max_connections = 151, which is insufficient for medium‑traffic production. Blindly increasing it can exhaust server memory because each thread consumes thread_stack (default 256 KB) plus per‑connection buffers.

Check current usage:

# Current and historical max usage
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Max_used_connections';

Reasonable calculation:

# Estimate based on available memory
TOTAL_MEM=$(free -m | awk '/^Mem:/{print $2}')
THREAD_STACK=$(mysql -u root -S /var/lib/mysql/mysql.sock -N -e "SHOW VARIABLES LIKE 'thread_stack';" | awk '{print $2}' | sed 's/k$//')
CONN_MEM_KB=$((THREAD_STACK + 2048))   # approx 2 MB per connection
MYSQL_BUF_KB=4096000                    # approx 4 GB global buffers
AVAILABLE_MEM_KB=$((TOTAL_MEM*1024 - MYSQL_BUF_KB))
MAX_CONN=$((AVAILABLE_MEM_KB / CONN_MEM_KB / 2))   # keep 50% headroom

echo "Suggested max_connections: $MAX_CONN"

Typical recommendations (based on server specs): 4 CPU 8 GB → 300‑500; 8 CPU 16 GB → 500‑800; 16 CPU 32 GB → 800‑1500; 32 CPU 64 GB → 1500‑3000.

3.5 Application Connection‑Management Guidelines

Correct pattern (Python example using mysql‑connector with a pool):

import mysql.connector
from mysql.connector import pooling

# Create a connection pool
connection_pool = pooling.MySQLConnectionPool(
    pool_name="app_pool",
    pool_size=10,
    pool_reset_session=True,
    host="localhost",
    database="mydb",
    user="app_user",
    password="app_password"
)

def query_data(sql, params=None):
    conn = None
    cursor = None
    try:
        conn = connection_pool.get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, params or ())
        return cursor.fetchall()
    except mysql.connector.Error as e:
        print(f"Database error: {e}")
        raise
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()  # returns to pool

Incorrect pattern (leads to leaks):

def query_data_bad(sql):
    conn = mysql.connector.connect(host="localhost", database="mydb", user="app_user", password="app_password")
    cursor = conn.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    # Forgot to close cursor and connection
    return result

4. Monitoring and Alerting Configuration

4.1 Prometheus + MySQL Exporter Stack

Use mysqld_exporter (or its Percona successor) to expose MySQL metrics to Prometheus.

Installation script (2026 latest version):

#!/bin/bash
EXPORTER_VERSION="0.16.0"
DOWNLOAD_URL="https://github.com/prometheus/mysqld_exporter/releases/download/v${EXPORTER_VERSION}/mysqld_exporter-${EXPORTER_VERSION}.linux-amd64.tar.gz"
cd /tmp
curl -LO "$DOWNLOAD_URL"
tar xzf mysqld_exporter-${EXPORTER_VERSION}.linux-amd64.tar.gz
sudo mv mysqld_exporter-${EXPORTER_VERSION}.linux-amd64/mysqld_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/mysqld_exporter

# Create a low‑privilege monitoring user
mysql -u root -e "
CREATE USER IF NOT EXISTS 'exporter'@'localhost' IDENTIFIED BY 'StrongExporterPass2026!' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
"

# Exporter config
sudo tee /etc/mysql_exporter.cnf >/dev/null <<'EOF'
[client]
user=exporter
password=StrongExporterPass2026!
host=localhost
port=3306
socket=/var/lib/mysql/mysql.sock
EOF
sudo chmod 644 /etc/mysql_exporter.cnf

# Systemd service
sudo tee /etc/systemd/system/mysql-exporter.service >/dev/null <<'EOF'
[Unit]
Description=Prometheus MySQL Exporter
After=network.target mysql.service

[Service]
Type=simple
User=prometheus
Group=prometheus
ExecStart=/usr/local/bin/mysqld_exporter --config.my-cnf=/etc/mysql_exporter.cnf \
    --collect.info_schema.processlist \
    --collect.info_schema.innodb_tablespaces \
    --collect.info_schema.innodb_metrics
Restart=always

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable mysql-exporter
sudo systemctl start mysql-exporter

4.2 AlertManager Rules for Connection Overload

# mysql_connection_alerts.yml (place under /etc/prometheus/rules/)

groups:
- name: MySQL连接数告警规则
  rules:
  - alert: MySQLConnectionsHigh
    expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL 连接数过高"
      description: "当前连接数 {{ $value | humanizePercentage }}，接近上限。"

  - alert: MySQLConnectionsCritical
    expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.95
    for: 1m
    labels:
      severity: critical
    annotations:
      summary: "MySQL 连接数即将打满"
      description: "当前连接数 {{ $value | humanizePercentage }}，立即处理！"

  - alert: MySQLConnectionRefused
    expr: rate(mysql_global_status_connection_errors_total[5m]) > 10
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "MySQL 连接拒绝率上升"
      description: "连接错误率 {{ $value }}/s，请检查。"

  - alert: MySQLSlowQueries
    expr: rate(mysql_global_status_slow_queries[5m]) > 5
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL 慢查询增加"
      description: "慢查询速率 {{ $value }}/s，请检查慢查询日志。"

  - alert: MySQLThreadsRunningHigh
    expr: mysql_global_status_threads_running > 20
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL 活跃连接数过高"
      description: "{{ $value }} 个连接正在执行查询，可能存在慢查询。"

4.3 Grafana Dashboard JSON (importable)

{
  "dashboard": {
    "title": "MySQL 连接数监控面板",
    "panels": [
      {
        "title": "当前连接数 / 最大连接数",
        "type": "gauge",
        "targets": [
          {"expr": "mysql_global_status_threads_connected", "legendFormat": "当前连接"},
          {"expr": "mysql_global_variables_max_connections", "legendFormat": "最大连接"}
        ]
      },
      {
        "title": "连接数趋势",
        "type": "graph",
        "targets": [
          {"expr": "mysql_global_status_threads_connected", "legendFormat": "已连接"},
          {"expr": "mysql_global_status_threads_running", "legendFormat": "运行中"},
          {"expr": "mysql_global_status_threads_cached", "legendFormat": "缓存"}
        ]
      },
      {
        "title": "连接建立速率",
        "type": "graph",
        "targets": [
          {"expr": "rate(mysql_global_status_connections[5m])", "legendFormat": "连接速率"}
        ]
      }
    ]
  }
}

5. Long‑Term Remediation

5.1 Connection‑Pool Configuration

HikariCP (Java) example:

spring:
  datasource:
    hikari:
      maximum-pool-size: 20   # adjust based on CPU cores
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: SELECT 1

PgBouncer (MySQL‑compatible) example:

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=3306 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 5
server_idle_timeout = 600

Start and use:

# Start PgBouncer
pgbouncer -d /etc/pgbouncer/pgbouncer.ini

# Application connects via PgBouncer
mysql -h 127.0.0.1 -P 6432 -u app_user -p mydb

5.2 Proper max_connections Sizing

Use the memory‑based formula shown earlier and set the value persistently:

# Persistent setting (MySQL 8.0+)
SET PERSIST max_connections = 800;
# Or edit my.cnf
[mysqld]
max_connections = 800

5.3 Tune wait_timeout and interactive_timeout

# View current timeout settings
SHOW VARIABLES LIKE '%timeout%';

# Recommended values (adjust per workload)
SET PERSIST wait_timeout = 600;          # 10 min idle
SET PERSIST interactive_timeout = 600;

# In my.cnf
[mysqld]
wait_timeout = 600
interactive_timeout = 600

5.4 Application Connection‑Management Standards

Always acquire connections from a pool and release them in a finally block or language‑specific context manager. Avoid raw DriverManager.getConnection() without explicit close.

6. Quick‑Reference Command Cheat Sheet

6.1 Emergency Diagnosis Commands

# One‑liner to show key connection metrics
mysql -u root -S /var/lib/mysql/mysql.sock -e "
SELECT 'Threads_connected' AS METRIC, @@Threads_connected UNION ALL
SELECT 'max_connections', @@max_connections UNION ALL
SELECT 'Threads_running', @@Threads_running UNION ALL
SELECT 'Threads_cached', @@Threads_cached;
"

6.2 Process‑Level Diagnostics

# List all connections with details
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,200) AS QUERY
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND!='Daemon'
ORDER BY TIME DESC;

# Connections per user
SELECT USER, COUNT(*) AS CNT, MAX(TIME) AS MAX_TIME, SUM(TIME) AS TOTAL_TIME
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY USER
ORDER BY CNT DESC;

# Running queries (top 20)
SELECT ID, USER, LEFT(INFO,100) AS QUERY, TIME, STATE
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND='Query'
ORDER BY TIME DESC
LIMIT 20;

6.3 Lock‑Wait Diagnosis

# Current lock waits
SELECT r.trx_id, r.trx_mysql_thread_id, r.trx_query, r.trx_state,
       TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS WAIT_SEC,
       l.lock_index, l.lock_table, l.lock_type
FROM INFORMATION_SCHEMA.INNODB_TRX r
JOIN INFORMATION_SCHEMA.INNODB_LOCKS l ON r.trx_id = l.lock_trx_id;

# All InnoDB locks
SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM INFORMATION_SCHEMA.INNODB_LOCKS;

6.4 Performance Checks

# Buffer pool hit rate
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
SELECT (1 - (reads VARIABLE_VALUE / (reads VARIABLE_VALUE + requests VARIABLE_VALUE))) AS HIT_RATE
FROM (
  SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads'
) reads,
(
  SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'
) requests;

# Table lock contention
SHOW STATUS LIKE 'Table_locks%';

6.5 Full Diagnosis Script (example)

#!/bin/bash
# mysql_diagnosis.sh – comprehensive connection‑overload report
SOCKET="/var/lib/mysql/mysql.sock"
OUTPUT="/tmp/mysql_diagnosis_$(date +%Y%m%d_%H%M%S).txt"
exec > >(tee "$OUTPUT") 2>&1

echo "========== MySQL Connection Overload Diagnosis =========="
echo "Generated at: $(date)"

echo "[1] Connection Overview"
mysql -u root -S "$SOCKET" -N -e "
SELECT 'Threads_connected', @@Threads_connected;
SELECT 'max_connections', @@max_connections;
SELECT 'Threads_running', @@Threads_running;"

echo "[2] Top 10 Users by Connection Count"
mysql -u root -S "$SOCKET" -N -e "SELECT USER, COUNT(*) AS CNT FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY USER ORDER BY CNT DESC LIMIT 10;"

echo "[3] Long‑Running Queries (Top 10)"
mysql -u root -S "$SOCKET" -N -e "SELECT ID, USER, LEFT(INFO,100) AS QUERY, TIME, STATE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Query' ORDER BY TIME DESC LIMIT 10;"

echo "[4] Lock Wait Count"
mysql -u root -S "$SOCKET" -N -e "SELECT COUNT(*) AS LOCK_WAIT_COUNT FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_state='LOCK WAIT';"

echo "[5] Slow Queries in Last Hour"
mysql -u root -S "$SOCKET" -N -e "SELECT COUNT(*) AS SLOW_QUERY_COUNT FROM mysql.slow_log WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);"

echo "[6] Recommendations"
CONN_RATIO=$(mysql -u root -S "$SOCKET" -N -e "SELECT Threads_connected/@@max_connections FROM performance_schema.global_status WHERE variable_name='Threads_connected';")
if (( $(echo "$CONN_RATIO > 0.8" | bc -l) )); then
  echo "- Connection usage exceeds 80%; consider cleaning sleep connections or raising max_connections."
fi

echo "Report saved to $OUTPUT"

7. Real‑World Scenarios and Case Studies

7.1 Scenario 1 – Flash‑Sale Traffic Spike

During a major promotion, traffic surged 50×, pushing connections from 200 to the default limit of 151 within minutes. Symptoms included alert MySQL_Too_Many_Connections and application errors like SQLSTATE[HY000] [2002] Can't connect to MySQL server.

Emergency steps:

Confirm MySQL process is alive ( ps aux | grep mysqld).

Temporarily raise max_connections to 500.

Run the sleep‑connection kill script to free handles.

Analyze whether the overload is traffic‑driven or caused by slow queries.

Root cause: The application did not use a connection pool; each request opened a new TCP connection, which accumulated as Sleep after the traffic burst.

Solution: Enable HikariCP with a larger pool (e.g., maximum-pool-size: 50) and adjust timeout settings.

7.2 Scenario 2 – Slow Query Causing Connection Accumulation

A SaaS finance module exported data using a cross‑table join lacking an index on product_id. The query ran >3 minutes, holding connections in Sending data state and causing Threads_running to hit 45/151.

Diagnosis: Query identified via performance_schema.events_statements_summary_by_digest and EXPLAIN showed a full table scan.

Fix: Add INDEX idx_product_id (product_id) to the orders table, verify with EXPLAIN (type changes from ALL to ref), and monitor post‑fix performance.

7.3 Scenario 3 – Connection Leak in Java Service

A Tomcat‑deployed Java service leaked 1‑2 connections per request due to missing close() in a try block. Over hours, connections grew from 50 to the 151 limit, triggering critical alerts.

Detection: Custom script detect_conn_leak.sh showed increasing Sleep connections with large MAX_TIME for the app user.

Resolution: Refactor code to use try‑with‑resources or explicit finally close, and schedule a periodic kill script for any remaining leaked connections.

7.4 Scenario 4 – Undersized max_connections

A newly provisioned data‑warehouse started with the default max_connections = 151. As business grew, the server regularly approached the limit, causing occasional refusals.

Evaluation: Script evaluate_max_connections.sh calculated a recommended range of 800‑1000 based on available memory and per‑connection memory footprint.

Adjustment: Temporarily set SET GLOBAL max_connections = 1000, then permanently update my.cnf and reload.

7.5 Scenario 5 – ProxySQL Bottleneck

When using ProxySQL as a front‑end, the proxy itself became the limiting factor. Metrics showed max_connections on backend MySQL servers were fine, but ProxySQL’s internal pool size was exhausted.

Diagnostics: Logged into ProxySQL admin interface (port 6032) and inspected stats_mysql_connection_pool and mysql_servers.

Fixes: Increase pool_size for the affected hostgroup and raise ProxySQL’s global max_connections variable.

8. Conclusion

MySQL "Too many connections" is a symptom, not the root cause. Effective remediation follows a prioritized approach:

Stop the bleeding: Verify MySQL is running, kill long‑sleeping connections, and optionally raise max_connections for immediate relief.

Identify the cause: Determine whether slow queries, connection leaks, short‑connection spikes, or an undersized max_connections setting is responsible.

Treat the disease: Optimize queries, fix application code, introduce proper connection pooling, and tune MySQL parameters.

Prevent recurrence: Deploy Prometheus + MySQL Exporter monitoring, configure sensible alert thresholds, and incorporate the provided scripts into a standard operating procedure stored under /opt/scripts/mysql/.

By preparing these tools and processes in advance, operations teams can respond rapidly under pressure, avoid data loss, and maintain service continuity.

This guide was authored for MySQL 8.0.39, Prometheus 3.0, and Grafana 11.0, and tested on CentOS Stream 9 and Ubuntu 24.04 LTS.