What to Do First When MySQL Connections Are Maxed Out

Background

MySQL connection‑limit exhaustion (error Too many connections) occurs when the server cannot allocate a new connection handle, causing new client requests to be rejected. Typical causes include traffic spikes, slow queries, connection leaks, or mis‑configured parameters.

1. Symptom and Immediate Assessment

1.1 Typical symptoms

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, but ordinary accounts cannot open new connections.

1.2 Emergency checks

Check if the MySQL process is alive and the port is listening:

# Check MySQL process
ps aux | grep mysqld | grep -v grep

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

# System load
uptime
w

Verify connection status:

# Show process list count (may fail if connections are full)
mysql -u root -S /var/lib/mysql/mysql.sock -e "SHOW PROCESSLIST;" 2>/dev/null | wc -l

If SHOW PROCESSLIST cannot run, inspect the error log and OS‑level connection counts:

# Tail error log for "too many connections"
tail -100 /var/log/mysql/error.log | grep -i "too many connections"

# Count TCP connections to MySQL
ss -ant | grep :3306 | wc -l

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

Calculate the ratio of current connections to max_connections:

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

2. Quick Stop‑Gap Actions

Warning: These commands are invasive; ensure you have backups and have notified stakeholders.

2.1 Kill idle sleep connections

Most overloads involve many connections stuck in SLEEP state. Kill them with:

KILL CONNECTION IF EXISTS (
  SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST
  WHERE COMMAND='Sleep' AND TIME>30
);

If the above syntax is unsupported (MySQL 8.0), generate a kill script:

# Generate kill statements for sleep connections
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‑click cleanup script:

#!/bin/bash
# kill_mysql_sleep_conn.sh
# Usage: ./kill_mysql_sleep_conn.sh 30 (threshold in seconds)
SOCKET="/var/lib/mysql/mysql.sock"
THRESHOLD=${1:-30}
mysql -u root -S "$SOCKET" -N -e "
SELECT CONCAT('KILL ', ID, ';')
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > $THRESHOLD;
" | mysql -u root -S "$SOCKET" -N

echo "$(date '+%Y-%m-%d %H:%M:%S') - Cleaned sleep connections"

2.2 Temporarily raise max_connections

SET GLOBAL max_connections = 500;

Or edit my.cnf and reload:

# my.cnf
[mysqld]
max_connections = 500

2.3 Kill specific long‑running queries

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

Kill a specific query:

KILL QUERY 1234;

2.4 Real‑time connection monitoring script

#!/bin/bash
SOCKET="/var/lib/mysql/mysql.sock"
WARN_THRESHOLD=100
CRIT_THRESHOLD=130
CURRENT_CONN=$(mysql -u root -S $SOCKET -N -e "SHOW STATUS LIKE 'Threads_connected';" | awk '{print $2}')
MAX_CONN=$(mysql -u root -S $SOCKET -N -e "SHOW VARIABLES LIKE 'max_connections';" | awk '{print $2}')
RUNNING=$(mysql -u root -S $SOCKET -N -e "SHOW STATUS LIKE 'Threads_running';" | awk '{print $2}')

echo "$(date '+%Y-%m-%d %H:%M:%S') - Connections: $CURRENT_CONN/$MAX_CONN, Running: $RUNNING"
if [ $CURRENT_CONN -ge $CRIT_THRESHOLD ]; then
  echo "CRITICAL: connection count $CURRENT_CONN exceeds $CRIT_THRESHOLD"
elif [ $CURRENT_CONN -ge $WARN_THRESHOLD ]; then
  echo "WARNING: connection count $CURRENT_CONN exceeds $WARN_THRESHOLD"
fi

3. Root‑Cause Analysis

3.1 Slow queries cause connection pile‑up

When a query runs for a long time (e.g., >30 s), its connection stays in Sending data or Sorting result state, preventing reuse. Identify recent heavy queries via 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 slow‑query patterns:

Large uncommitted transactions.

Missing indexes leading to full‑table scans.

Deep pagination ( LIMIT offset, n) causing many row lookups.

Fixes include adding appropriate indexes, breaking large transactions, and avoiding deep pagination.

3.2 Connection leaks (application does not close connections)

Detect sleep connections per user/host that exceed a count threshold:

SELECT USER, HOST, COUNT(*) AS CONN_COUNT, MAX(TIME) AS MAX_TIME
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND='Sleep'
GROUP BY USER, HOST
HAVING COUNT(*)>10
ORDER BY CONN_COUNT DESC;

Typical leak code (Java) fails to close the connection in a finally block, leading to persistent SLEEP connections.

3.3 Short‑lived connections

Languages such as PHP or Python may use short‑connection mode, opening a new TCP connection for each request. Sudden traffic spikes can overwhelm the server.

Identify connection bursts:

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;

Check Aborted_connects to see if many connection attempts fail.

3.4 max_connections set too low

MySQL 8.0 default max_connections = 151 is insufficient for medium‑traffic production. Each connection consumes thread_stack (default 256 KB) plus per‑connection buffers.

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Max_used_connections';

Reasonable sizing formula:

max_connections = (available_memory - global_buffers) / (per_connection_memory + buffer_overhead)

Recommended values (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

SET PERSIST max_connections = 800;
# or edit /etc/mysql/my.cnf
[mysqld]
max_connections = 800

4. Monitoring and Alerting

4.1 Prometheus + MySQL Exporter

Deploy the Percona mysqld_exporter (2026 version) to collect metrics.

# install_mysql_exporter.sh
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 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;
"

# Systemd service (simplified)
cat <<'EOF' | sudo tee /etc/systemd/system/mysql-exporter.service
[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
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
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: "MySQL 连接错误率 {{ $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 import)

{
  "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 Solutions

5.1 Connection‑pool configuration

HikariCP (Java Spring) example:

# application.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 20   # adjust to workload
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-test-query: SELECT 1

PgBouncer (transaction‑level pool) example for MySQL:

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
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

5.2 Proper max_connections sizing

Calculate based on server memory and expected concurrency:

# calc_max_connections.sh
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';" 2>/dev/null | 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))
echo "Suggested max_connections: $MAX_CONN"

5.3 Tune wait_timeout and interactive_timeout

SET PERSIST wait_timeout = 600;          # 10 minutes
SET PERSIST interactive_timeout = 600;  # 10 minutes

Or in my.cnf:

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

5.4 Application connection‑management guidelines

Correct Python example using mysql‑connector pooling:

from mysql.connector import pooling
pool = pooling.MySQLConnectionPool(pool_name="app_pool", pool_size=10,
                                   host="localhost", database="mydb",
                                   user="app_user", password="app_password")

def query_data(sql, params=None):
    conn = None
    cursor = None
    try:
        conn = pool.get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.execute(sql, params or ())
        return cursor.fetchall()
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()  # returns to pool

Incorrect example that leads to leaks (connection never closed):

# Bad example – missing finally/with block
import mysql.connector

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

6. Diagnostic Command Cheat Sheet

6.1 Emergency diagnostics

# One‑liner to view key connection metrics
mysql -u root -S /var/lib/mysql/mysql.sock -e "
SELECT '当前连接数' AS METRIC, @@Threads_connected AS VALUE
UNION ALL SELECT '最大连接数', @@max_connections
UNION ALL SELECT '运行中线程', @@Threads_running;
"

6.2 Process‑level diagnostics

# Detailed connection list (excluding daemon threads)
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE,
       LEFT(INFO,200) AS QUERY
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND!='Daemon'
ORDER BY TIME DESC;

# Group by user to spot leaks
SELECT USER, COUNT(*) AS CNT, MAX(TIME) AS MAX_TIME
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY USER
ORDER BY CNT DESC;

# Running queries with execution time
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 diagnostics

# 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 diagnostics

# Buffer pool hit rate
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

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

6.5 Full diagnostic script

#!/bin/bash
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 Exhaustion Diagnosis =========="

echo "--- 1. Connection overview ---"
mysql -u root -S $SOCKET -N -e "
SELECT 'Threads_connected:', Threads_connected FROM performance_schema.global_status WHERE variable_name='Threads_connected';
SELECT 'max_connections:', @@max_connections;
SELECT 'Threads_running:', Threads_running FROM performance_schema.global_status WHERE variable_name='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. Top 10 longest running queries ---"
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 query count (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);"

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 or increasing max_connections."
fi

echo "Report saved to $OUTPUT"

7. Real‑World Cases

7.1 Flash‑sale traffic spike

During a flash‑sale, connections jumped from ~200 to the default limit of 151, triggering Too many connections. Emergency steps:

Confirm MySQL is alive.

Temporarily raise max_connections to 500.

Kill long‑sleep connections.

Analyze whether the surge is due to missing connection pooling.

Root cause: application created a new connection per request without a pool. Solution: enable HikariCP (or similar) with an appropriate pool size.

7.2 Slow query causing connection pile‑up

A cross‑table join without an index scanned 5 million rows, holding connections for >3 minutes. Diagnosis used performance_schema and EXPLAIN, revealing a missing index on orders.product_id. Adding the index reduced execution time and freed connections.

7.3 Connection leak in a Java service

Sleep connections grew from 50 to 151 over several hours. A code path omitted conn.close() after catching an exception. Rewriting with try‑with‑resources eliminated the leak.

7.4 Undersized max_connections configuration

Baseline server (8 CPU 16 GB) used the default max_connections=151. A script calculated a safe limit of ~800 based on memory, and the setting was updated permanently.

7.5 ProxySQL becoming the bottleneck

ProxySQL’s pool_size and global max_connections were too low, causing back‑end MySQL saturation. Adjusting pool_size and max_connections in ProxySQL resolved the issue.

8. Summary

MySQL connection exhaustion is a symptom, not a root cause. Follow this prioritized workflow:

Stop‑bleed: Verify the MySQL process, use SHOW PROCESSLIST to locate and kill idle or long‑running connections, and optionally raise max_connections temporarily.

Diagnose: Determine whether the overload stems from slow queries, connection leaks, short‑connection bursts, or an undersized max_connections setting.

Heal: Fix slow queries (add indexes, rewrite joins), correct leak bugs (ensure connections are closed), and introduce proper connection pooling.

Prevent: Deploy Prometheus + MySQL Exporter monitoring, set alert thresholds, and regularly review connection‑related metrics.

Store the provided scripts under /opt/scripts/mysql/ and maintain a standard operating procedure so the team can act quickly under pressure.

Relevant repository URLs:

GitHub: https://github.com/raymond999999

Gitee: https://gitee.com/raymond9