The Database Connection Pool Exhaustion
A web application uses a connection pool to a MySQL database. The application is hosted on two web servers behind a load balancer. Each web server has a connection pool configured with a maximum of 50 connections. The MySQL server has a max_connections setting of 100. During peak hours, users report that the application becomes unresponsive and displays 'Cannot connect to database' errors. The IT team checks the MySQL server and sees that the number of active connections is at 100, and many connections are in 'Sleep' state for long periods. The application logs show that some queries are taking longer than usual. The team also notices that the application code does not always close connections properly in case of exceptions. The database server has 8 GB RAM and is running on a VM with 4 vCPUs. The team is considering increasing max_connections to 200, but they are unsure if that will solve the problem.
1. What is the root cause of the connection exhaustion, and why might increasing max_connections not solve the issue?
Read the worked answer
The root cause is connection leaks: the application does not close connections properly, leading to connections remaining open in 'Sleep' state. Over time, these idle connections accumulate and exhaust the pool and the database's max_connections. Increasing max_connections might temporarily alleviate the symptom but will not fix the leak; it will only delay exhaustion and may worsen performance because each connection consumes memory (8 GB RAM) and the server may become overloaded. Also, the 'Sleep' connections are idle but still hold resources. The long-running queries indicate possible inefficient queries or locks, which could also contribute. The pool size on each web server is 50, total 100, matching max_connections. If both pools are fully utilized, the database is at capacity. The leak means connections are not returned, so the pool eventually runs out. Increasing max_connections to 200 would allow more connections, but if leaks persist, they will eventually exhaust that too, and the server may run out of memory or hit other limits. The proper fix is to address the leak: ensure connections are closed in finally blocks, use connection pool monitoring, and set a timeout for idle connections. Also, optimize queries to reduce long-running transactions.
2. What steps should the IT team take to diagnose and resolve the issue, and what configuration changes are recommended?
Read the worked answer
Diagnosis: (1) Enable MySQL slow query log to identify long-running queries. (2) Use 'SHOW PROCESSLIST' to see connections and their states. (3) Monitor connection pool metrics (active, idle, waiting). (4) Review application code for connection handling, especially exception paths. (5) Check for connection leaks using tools like p6spy or connection pool logs. Resolution: (1) Fix code to always close connections (use try-with-resources or finally). (2) Set a connection timeout and idle timeout in the pool (e.g., remove abandoned connections). (3) Reduce the maximum pool size if not needed; 50 per server may be too high. (4) Optimize queries and add indexes. (5) Consider using a connection pool that supports leak detection (e.g., HikariCP). (6) Increase max_connections only after fixing leaks, but also consider memory: each connection uses memory, so 200 may be too high for 8 GB. Calculate: MySQL uses ~ per connection; 200 might be okay but monitor. (7) Implement monitoring and alerts for connection usage. (8) Consider a database proxy or load balancer for MySQL if scaling needed.