ERROR 1040 (HY000): Too Many Connections

Error Message#

ERROR 1040 (HY000): Too many connections

Description#

This error occurs when the MySQL server has reached its maximum allowed number of concurrent client connections. The server is unable to accept any new connections until existing connections are closed or terminated.

Causes#

• Low max_connections setting (default is often too low, typically 151)
• Connection leaks in applications (not properly closing connections)
• Inefficient connection pooling configuration
• Long-running queries holding connections open
• Sleeping connections consuming connection slots
• Sudden increases in application traffic
• Unclosed transactions keeping connections active
• Insufficient server resources to handle more connections

Solutions#

1. Increase max_connections limit:

   -- Check current max_connections value
   SHOW VARIABLES LIKE 'max_connections';
   
   -- Increase max_connections temporarily
   SET GLOBAL max_connections = 500;
   
   -- For permanent changes, modify my.cnf/my.ini:
   # max_connections = 500

2. Implement connection pooling:

   Configure connection pools in your application:

   # Example pool settings: pool_size = 10 # Base connections pool_max_size = 20 # Maximum connections pool_idle_timeout = 300 # Seconds before idle connection is closed

3. Terminate idle connections:

   -- Show current connections and their state
   SHOW PROCESSLIST;
   
   -- Kill long-running or sleeping connections
   KILL CONNECTION connection_id;

4. Configure connection timeouts:

   -- Reduce timeout values
   SET GLOBAL wait_timeout = 300;         -- Close inactive connections after 5 minutes
   SET GLOBAL interactive_timeout = 300;   -- Close inactive client connections after 5 minutes

Prevention#

1. Use connection pooling in your applications (HikariCP, DBCP, PDO)

2. Properly manage connections in application code:

   • Always close connections after use
   • Use try-finally blocks to ensure connections are released
   • Implement connection reuse

3. Monitor active connections:

   -- Check current connection count
   SHOW STATUS LIKE 'Threads_connected';

4. Optimize slow queries that hold connections open:

   -- Enable slow query log
   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;