Principle and Configuration of Database Connection Pool

Problem Description
A database connection pool is a technology for reusing database connections, designed to reduce the performance overhead caused by frequently creating and closing connections. In high-concurrency scenarios, directly and frequently creating connections may lead to database resource exhaustion or response delays. The connection pool pre-creates and manages a certain number of connections, allowing applications to borrow and return them on demand, thereby enhancing performance. Interviews often require explaining its core principles, key configuration parameters, and common issues.

Solution Process

1. Core Principle of Connection Pool

   • Initialization Phase: When the connection pool starts, it pre-creates a specified number of database connections based on the configured initial size (e.g., initialSize=5) and places them in the pool for later use.
   • Connection Acquisition: When an application needs a connection, it allocates an idle connection from the pool; if there are no idle connections and the maximum capacity has not been reached, a new connection is created; if the maximum capacity is reached, the request may wait or throw an exception.
   • Connection Return: After use, the application calls the close() method (actually proxied by the connection pool) to mark the connection as idle instead of truly closing it, allowing it to be reused later.
   • Connection Management: The connection pool periodically checks the liveness of idle connections (e.g., via the testWhileIdle parameter), closes invalid connections, and replenishes new connections to maintain the minimum number of idle connections.

2. Detailed Explanation of Key Configuration Parameters

   • Basic Capacity Parameters:
     • initialSize: The initial number of connections, avoiding delays from creating connections on the first request.
     • maxTotal: The maximum number of connections, preventing database overload. It should be adjusted based on the database and system load (e.g., set to 20-50).
     • minIdle: The minimum number of idle connections, ensuring a quick response to sudden requests.
   • Connection Validity Management:
     • maxWaitMillis: The maximum waiting time to acquire a connection; if exceeded, an exception is thrown to avoid thread blocking.
     • testOnBorrow/testOnReturn: Validates connection effectiveness (e.g., executing SELECT 1) when borrowing or returning, but frequent validation incurs performance overhead.
     • testWhileIdle: A background thread periodically checks idle connections, which is more efficient (recommended to enable).
   • Connection Reclamation Strategy:
     • timeBetweenEvictionRunsMillis: The interval for checking idle connections (e.g., 60 seconds).
     • minEvictableIdleTimeMillis: Connections idle longer than this time are reclaimed (e.g., 30 minutes).

3. Common Issues and Solutions

   • Connection Leaks: The application fails to properly return connections, leading to exhaustion of connections in the pool.
     • Solution: Enable connection leak detection (e.g., Druid's removeAbandonedTimeout parameter) to forcibly reclaim connections not returned within the timeout period.
   • Database Connection Timeout: The database actively closes idle connections, but the connection pool is unaware, causing errors after allocation.
     • Solution: Periodically validate connections via testWhileIdle or validationQuery, and discard invalid connections.
   • Insufficient Connections: maxTotal is set too low, causing many threads to wait for connections during high concurrency.
     • Solution: Monitor connection pool metrics (active connections, waiting threads), adjust maxTotal appropriately, and optimize code to reduce connection holding time.

Summary
The connection pool reduces overhead by reusing connections. When configuring, balance performance and resources (e.g., maxTotal should not be too large to avoid database pressure). Additionally, combine monitoring tools (e.g., Druid's monitoring interface) to adjust parameters in real-time. In practical applications, attention should also be paid to the selection of connection pools (e.g., HikariCP is known for high performance) and their adaptation to business scenarios.