ERROR 1040 (HY000): Too many connections
ERROR 1040 (HY000): Too many connections
ERROR 1040 (HY000): Too many connections
max_connections
# Connect to MySQL
mysql -u root -p # Check current connections
SHOW STATUS WHERE Variable_name = 'Threads_connected';
SHOW STATUS WHERE Variable_name = 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
# Connect to MySQL
mysql -u root -p # Check current connections
SHOW STATUS WHERE Variable_name = 'Threads_connected';
SHOW STATUS WHERE Variable_name = 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
# Connect to MySQL
mysql -u root -p # Check current connections
SHOW STATUS WHERE Variable_name = 'Threads_connected';
SHOW STATUS WHERE Variable_name = 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
Threads_connected
max_connections
-- See what's connected right now
SHOW PROCESSLIST; -- Kill a specific connection (use the Id from PROCESSLIST)
KILL CONNECTION 1234;
-- See what's connected right now
SHOW PROCESSLIST; -- Kill a specific connection (use the Id from PROCESSLIST)
KILL CONNECTION 1234;
-- See what's connected right now
SHOW PROCESSLIST; -- Kill a specific connection (use the Id from PROCESSLIST)
KILL CONNECTION 1234;
-- Increase without restarting MySQL
SET GLOBAL max_connections = 300; -- Verify
SHOW VARIABLES LIKE 'max_connections';
-- Increase without restarting MySQL
SET GLOBAL max_connections = 300; -- Verify
SHOW VARIABLES LIKE 'max_connections';
-- Increase without restarting MySQL
SET GLOBAL max_connections = 300; -- Verify
SHOW VARIABLES LIKE 'max_connections';
/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
max_connections = 300
[mysqld]
max_connections = 300
[mysqld]
max_connections = 300
sudo systemctl restart mysql
sudo systemctl restart mysql
sudo systemctl restart mysql
max_connections
// Bad: creating a new connection per request
const connection = mysql.createConnection({...}); // Good: use a pool
const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'mydb', waitForConnections: true, connectionLimit: 10, // Max simultaneous connections queueLimit: 0
}); // Use promise wrapper
const promisePool = pool.promise();
// Bad: creating a new connection per request
const connection = mysql.createConnection({...}); // Good: use a pool
const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'mydb', waitForConnections: true, connectionLimit: 10, // Max simultaneous connections queueLimit: 0
}); // Use promise wrapper
const promisePool = pool.promise();
// Bad: creating a new connection per request
const connection = mysql.createConnection({...}); // Good: use a pool
const pool = mysql.createPool({ host: 'localhost', user: 'root', database: 'mydb', waitForConnections: true, connectionLimit: 10, // Max simultaneous connections queueLimit: 0
}); // Use promise wrapper
const promisePool = pool.promise();
from sqlalchemy import create_engine engine = create_engine( 'mysql+pymysql://user:pass@localhost/db', pool_size=10, max_overflow=20, pool_pre_ping=True, # Detect stale connections pool_recycle=3600, # Recycle connections after 1 hour
)
from sqlalchemy import create_engine engine = create_engine( 'mysql+pymysql://user:pass@localhost/db', pool_size=10, max_overflow=20, pool_pre_ping=True, # Detect stale connections pool_recycle=3600, # Recycle connections after 1 hour
)
from sqlalchemy import create_engine engine = create_engine( 'mysql+pymysql://user:pass@localhost/db', pool_size=10, max_overflow=20, pool_pre_ping=True, # Detect stale connections pool_recycle=3600, # Recycle connections after 1 hour
)
-- Watch connections in real time (run every 5 seconds)
SHOW STATUS WHERE Variable_name IN ('Threads_connected','Threads_running'); -- Find long-running queries
SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 30
ORDER BY time DESC;
-- Watch connections in real time (run every 5 seconds)
SHOW STATUS WHERE Variable_name IN ('Threads_connected','Threads_running'); -- Find long-running queries
SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 30
ORDER BY time DESC;
-- Watch connections in real time (run every 5 seconds)
SHOW STATUS WHERE Variable_name IN ('Threads_connected','Threads_running'); -- Find long-running queries
SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 30
ORDER BY time DESC;
// Leak: connection opened, never released
async function getData() { const conn = await pool.getConnection(); const [rows] = await conn.query('SELECT * FROM users'); return rows; // conn.release() never called!
} // Fixed:
async function getData() { const conn = await pool.getConnection(); try { const [rows] = await conn.query('SELECT * FROM users'); return rows; } finally { conn.release(); // Always released }
}
// Leak: connection opened, never released
async function getData() { const conn = await pool.getConnection(); const [rows] = await conn.query('SELECT * FROM users'); return rows; // conn.release() never called!
} // Fixed:
async function getData() { const conn = await pool.getConnection(); try { const [rows] = await conn.query('SELECT * FROM users'); return rows; } finally { conn.release(); // Always released }
}
// Leak: connection opened, never released
async function getData() { const conn = await pool.getConnection(); const [rows] = await conn.query('SELECT * FROM users'); return rows; // conn.release() never called!
} // Fixed:
async function getData() { const conn = await pool.getConnection(); try { const [rows] = await conn.query('SELECT * FROM users'); return rows; } finally { conn.release(); // Always released }
}
# Add to cron — alert when connections > 80% of max
cat << 'EOF' >> /usr/local/bin/mysql-check.sh
#!/bin/bash
MAX=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW VARIABLES LIKE 'max_connections'" | awk '{print $2}')
CURRENT=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW STATUS LIKE 'Threads_connected'" | awk '{print $2}')
PERCENT=$((CURRENT * 100 / MAX))
if [ $PERCENT -gt 80 ]; then echo "MySQL connections at ${PERCENT}% (${CURRENT}/${MAX})" | mail -s "MySQL Connection Alert" [email protected]
fi
EOF
chmod +x /usr/local/bin/mysql-check.sh
# Add to cron — alert when connections > 80% of max
cat << 'EOF' >> /usr/local/bin/mysql-check.sh
#!/bin/bash
MAX=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW VARIABLES LIKE 'max_connections'" | awk '{print $2}')
CURRENT=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW STATUS LIKE 'Threads_connected'" | awk '{print $2}')
PERCENT=$((CURRENT * 100 / MAX))
if [ $PERCENT -gt 80 ]; then echo "MySQL connections at ${PERCENT}% (${CURRENT}/${MAX})" | mail -s "MySQL Connection Alert" [email protected]
fi
EOF
chmod +x /usr/local/bin/mysql-check.sh
# Add to cron — alert when connections > 80% of max
cat << 'EOF' >> /usr/local/bin/mysql-check.sh
#!/bin/bash
MAX=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW VARIABLES LIKE 'max_connections'" | awk '{print $2}')
CURRENT=$(mysql -u root -p"$MYSQL_ROOT_PASSWORD" -sN -e "SHOW STATUS LIKE 'Threads_connected'" | awk '{print $2}')
PERCENT=$((CURRENT * 100 / MAX))
if [ $PERCENT -gt 80 ]; then echo "MySQL connections at ${PERCENT}% (${CURRENT}/${MAX})" | mail -s "MySQL Connection Alert" [email protected]
fi
EOF
chmod +x /usr/local/bin/mysql-check.sh - Your app opens connections but doesn't close them properly (connection leak)
- A traffic spike creates more simultaneous connections than your limit allows
- Your app framework creates a new connection per request instead of using a pool
