Fixing MySQL ER_CON_COUNT_ERROR: Resolving Intermittent Timeouts from Connection Pool Exhaustion
Quick Fix Summary
TL;DRKill idle connections and increase max_connections temporarily.
This error occurs when the number of simultaneous client connections exceeds the server's max_connections limit, causing new connections to be rejected.
Diagnosis & Causes
Recovery Steps
Step 1: Verify Current Connection Count and Limits
Check current connection usage and server limits to confirm exhaustion.
mysql -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -e "SHOW PROCESSLIST;" | head -20Step 2: Kill Idle Connections (Immediate Relief)
Terminate long-idle connections to free up slots for active requests.
mysql -e "SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE command = 'Sleep' AND time > 300 ORDER BY time DESC;"
mysql -e "KILL <connection_id>;" # Replace with actual IDStep 3: Temporarily Increase max_connections
Raise the connection limit without restarting MySQL (if using MySQL 5.7+).
mysql -e "SET GLOBAL max_connections = 500;"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"Step 4: Make Permanent Configuration Change
Update my.cnf to persist the increased connection limit after restart.
sudo sed -i '/max_connections/c\max_connections = 500' /etc/mysql/my.cnf
sudo systemctl restart mysqlStep 5: Configure Connection Pool in Application
Set proper pool size and timeout in application configuration (example for Java/HikariCP).
# In application.properties or similar:
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000Step 6: Monitor for Connection Leaks
Set up monitoring to alert when connections approach the limit.
# Sample monitoring query for Prometheus/Grafana:
mysql -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}'Step 7: Adjust wait_timeout and interactive_timeout
Reduce idle connection lifetime to free connections faster.
mysql -e "SET GLOBAL wait_timeout = 120;"
mysql -e "SET GLOBAL interactive_timeout = 120;"
# Add to my.cnf: wait_timeout = 120, interactive_timeout = 120Architect's Pro Tip
"This often happens when application servers are restarted but don't properly close existing database connections, causing a buildup of 'Sleep' connections that count against max_connections."
Frequently Asked Questions
Why do I see intermittent timeouts instead of constant errors?
Connection exhaustion typically happens during traffic spikes when many application threads try to connect simultaneously. During normal load, connections stay under the limit.
What's a safe value for max_connections?
Start with 500 for most applications. Monitor RAM usage (each connection uses ~256KB-1MB). Don't exceed available memory: max_connections ≈ (Available RAM - System RAM) / 1MB.