Connection Pooling

Why Connection Pooling?

Creating a database connection is an expensive operation. It involves network handshakes, authentication, memory allocation, and resource initialization. Without pooling, each database request would create a new connection, severely impacting performance.

The Problem: Connection Creation Cost

// Without pooling - SLOW!
public User findUser(long id) {
    // Step 1: Create connection (100-500ms!)
    // - TCP handshake with database server
    // - SSL/TLS negotiation (if enabled)
    // - Authentication (username/password)
    // - Session setup on database
    Connection conn = DriverManager.getConnection(url, user, password);  // SLOW!

    // Step 2: Execute query (1-10ms)
    User user = executeQuery(conn, id);  // Fast

    // Step 3: Close connection (5-20ms)
    conn.close();  // Resources freed, connection destroyed

    return user;
}

// For 1000 requests per second:
// Without pooling: 1000 * 200ms = 200 seconds of connection overhead!
// With pooling:    1000 * 1ms = 1 second (reuse existing connections)

The Solution: Connection Pool

// With pooling - FAST!
┌─────────────────────────────────────────────────────────────────────┐
│                      CONNECTION POOL                                 │
│                                                                      │
│   ┌─────┐  ┌─────┐  ┌─────┐  ┌─────┐  ┌─────┐                      │
│   │Conn1│  │Conn2│  │Conn3│  │Conn4│  │Conn5│  ... (pre-created)   │
│   │ OK  │  │BUSY │  │ OK  │  │ OK  │  │BUSY │                      │
│   └─────┘  └─────┘  └─────┘  └─────┘  └─────┘                      │
│                                                                      │
│   getConnection() → Returns available connection instantly!          │
│   close() → Returns connection to pool (doesn't destroy it)         │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

public User findUser(long id) {
    // Step 1: Get connection from pool (<1ms!)
    Connection conn = dataSource.getConnection();  // Already created!

    // Step 2: Execute query (1-10ms)
    User user = executeQuery(conn, id);

    // Step 3: Return to pool (<1ms)
    conn.close();  // Doesn't close! Returns to pool for reuse.

    return user;
}

Popular Connection Pools

HikariCP Configuration

Maven Dependency

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

<!-- Spring Boot includes HikariCP by default with: -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Programmatic Configuration

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DatabaseConfig {

    public static HikariDataSource createDataSource() {
        HikariConfig config = new HikariConfig();

        // Essential settings
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("user");
        config.setPassword("password");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");

        // Pool sizing (most important!)
        config.setMaximumPoolSize(10);        // Max connections in pool
        config.setMinimumIdle(5);             // Min idle connections to maintain

        // Timeouts
        config.setConnectionTimeout(30000);   // 30s - wait for connection from pool
        config.setIdleTimeout(600000);        // 10min - idle connection timeout
        config.setMaxLifetime(1800000);       // 30min - max connection lifetime
        config.setKeepaliveTime(300000);      // 5min - keepalive query interval

        // Validation
        config.setConnectionTestQuery("SELECT 1");  // Optional for JDBC4+ drivers

        // Performance
        config.setAutoCommit(true);            // Default auto-commit state
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

        // Pool name (for monitoring)
        config.setPoolName("MyAppPool");

        return new HikariDataSource(config);
    }
}

Spring Boot Configuration

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb
    username: user
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver

    # HikariCP specific settings
    hikari:
      pool-name: MyAppPool
      maximum-pool-size: 10
      minimum-idle: 5
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-timeout: 30000
      keepalive-time: 300000

      # MySQL optimizations
      data-source-properties:
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
        useLocalSessionState: true
        rewriteBatchedStatements: true
        cacheResultSetMetadata: true
        cacheServerConfiguration: true
        elideSetAutoCommits: true
        maintainTimeStats: false

Configuration Explained

// Pool Size Configuration - The most critical settings!

┌─────────────────────────────────────────────────────────────────────┐
│                    POOL SIZING VISUALIZATION                         │
│                                                                      │
│   maximumPoolSize: 10                                               │
│   ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │
│   │  1  │ │  2  │ │  3  │ │  4  │ │  5  │ │  6  │ │  7  │ │  8  │ │
│   └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ │
│   │ minimumIdle: 5 (always ready) │  │ Created on demand  │        │
│   └───────────────────────────────┘  └────────────────────┘        │
│                                                                      │
│   - Start with minimumIdle connections                              │
│   - Create more as needed, up to maximumPoolSize                    │
│   - Destroy excess idle connections after idleTimeout               │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

// Key parameters:

// maximumPoolSize: Total connections the pool can have
// - Too low: Requests queue waiting for connections
// - Too high: Database overwhelmed, memory wasted
// - Formula: connections = ((core_count * 2) + effective_spindle_count)
// - For SSD: core_count * 2 + 1 (typically 5-10 for most apps)

// minimumIdle: Connections maintained even when idle
// - Set equal to maximumPoolSize for consistent performance
// - Set lower to reduce resource usage during idle periods

// connectionTimeout: How long to wait for a connection
// - If pool exhausted and no connection available within this time
// - SQLException is thrown
// - 30 seconds is a good default

// idleTimeout: How long a connection can sit idle before removal
// - Only applies when connections > minimumIdle
// - Set to 10 minutes for most cases

// maxLifetime: Maximum connection lifetime
// - Should be less than database's wait_timeout
// - MySQL default wait_timeout: 8 hours
// - Set to 30 minutes for safety

Pool Sizing Guidelines

The Formula

// HikariCP Creator's Formula:
// connections = ((core_count * 2) + effective_spindle_count)

// For a 4-core server with SSD:
// connections = (4 * 2) + 1 = 9

// Why so few?
// - More connections ≠ More throughput
// - Database spends time context-switching between connections
// - Each connection uses memory (both app and DB side)

// Analogy: Highway lanes
// - 4-lane highway at capacity: cars moving smoothly
// - Add 100 more lanes: traffic jams at intersections!

Common Scenarios

// Scenario 1: Simple Web Application
// - 4 core server, 100 concurrent users
maximumPoolSize: 10
minimumIdle: 5

// Scenario 2: High-Traffic API
// - 8 core server, microservice, fast queries
maximumPoolSize: 15
minimumIdle: 10

// Scenario 3: Batch Processing
// - Long-running queries, fewer concurrent
maximumPoolSize: 5
minimumIdle: 2
connectionTimeout: 60000  // Longer timeout

// Scenario 4: Multiple Datasources
// - Main DB + Read replica
// - Split pool size between them
mainPool.maximumPoolSize: 8
readReplicaPool.maximumPoolSize: 5

Calculating Based on Load

// If you know your workload:

// Given:
// - 1000 requests/second
// - Average query time: 10ms
// - Maximum query time: 100ms

// Minimum connections needed:
// connections = requests/second × average_query_time
// connections = 1000 × 0.01 = 10

// Add buffer for spikes:
// maximumPoolSize = 15

// Monitor and adjust based on:
// - Connection wait times
// - Pool utilization
// - Database CPU/connections

Monitoring and Troubleshooting

HikariCP Metrics

import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;

public class PoolMonitor {

    private final HikariDataSource dataSource;

    public void printStats() {
        HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();

        System.out.println("=== HikariCP Stats ===");
        System.out.println("Active Connections: " + poolMXBean.getActiveConnections());
        System.out.println("Idle Connections: " + poolMXBean.getIdleConnections());
        System.out.println("Total Connections: " + poolMXBean.getTotalConnections());
        System.out.println("Threads Waiting: " + poolMXBean.getThreadsAwaitingConnection());
    }
}

// Spring Boot Actuator integration
// Add to application.yml:
management:
  endpoints:
    web:
      exposure:
        include: health,metrics,hikaricp

// Access metrics at:
// GET /actuator/metrics/hikaricp.connections.active
// GET /actuator/metrics/hikaricp.connections.idle
// GET /actuator/metrics/hikaricp.connections.pending

Common Issues and Solutions

// Issue 1: Connection Timeout (pool exhausted)
// Error: "Connection is not available, request timed out after 30000ms"

// Causes:
// - Pool too small for load
// - Connection leaks (not closed properly)
// - Slow queries holding connections

// Solutions:
// 1. Enable leak detection:
config.setLeakDetectionThreshold(60000);  // Warn if connection held > 60s

// 2. Find and fix leaks:
try (Connection conn = dataSource.getConnection()) {
    // Always use try-with-resources!
}

// 3. Review slow queries
config.addDataSourceProperty("logger", "com.mysql.cj.log.Slf4JLogger");
config.addDataSourceProperty("profileSQL", "true");

// Issue 2: Connection Reset / Stale Connections
// Error: "Communications link failure" or "Connection reset"

// Cause: Database closed idle connections

// Solution: Configure keepalive and max lifetime
config.setMaxLifetime(1800000);    // 30 minutes
config.setKeepaliveTime(300000);   // 5 minutes

// Issue 3: Too Many Connections
// Error: "Too many connections" from database

// Cause: Multiple app instances, each with large pool

// Solution: Calculate total across all instances
// 5 app instances × 10 connections = 50 database connections
// MySQL default max_connections = 151

Logging Configuration

# logback.xml - Enable HikariCP debug logging
<logger name="com.zaxxer.hikari" level="DEBUG"/>
<logger name="com.zaxxer.hikari.HikariConfig" level="DEBUG"/>

# application.yml
logging:
  level:
    com.zaxxer.hikari: DEBUG
    com.zaxxer.hikari.HikariConfig: DEBUG

# Sample debug output:
# HikariPool-1 - Pool stats (total=10, active=2, idle=8, waiting=0)

Connection Pool with JPA/Hibernate

<!-- persistence.xml with HikariCP -->
<persistence-unit name="myPU">
    <properties>
        <!-- HikariCP as connection provider -->
        <property name="hibernate.connection.provider_class"
                  value="org.hibernate.hikaricp.internal.HikariCPConnectionProvider"/>

        <!-- HikariCP settings -->
        <property name="hibernate.hikari.dataSourceClassName"
                  value="com.mysql.cj.jdbc.MysqlDataSource"/>
        <property name="hibernate.hikari.dataSource.url"
                  value="jdbc:mysql://localhost:3306/mydb"/>
        <property name="hibernate.hikari.dataSource.user"
                  value="user"/>
        <property name="hibernate.hikari.dataSource.password"
                  value="password"/>
        <property name="hibernate.hikari.maximumPoolSize"
                  value="10"/>
        <property name="hibernate.hikari.minimumIdle"
                  value="5"/>
    </properties>
</persistence-unit>

// Spring Boot - automatic (no extra config needed!)
// Just add spring-boot-starter-data-jpa and configure datasource
// HikariCP is used automatically

Other Connection Pools

Apache DBCP2

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.11.0</version>
</dependency>

// Configuration
BasicDataSource ds = new BasicDataSource();
ds.setUrl("jdbc:mysql://localhost:3306/mydb");
ds.setUsername("user");
ds.setPassword("password");
ds.setMaxTotal(10);
ds.setMaxIdle(5);
ds.setMinIdle(2);
ds.setMaxWaitMillis(30000);

Tomcat JDBC Pool

<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jdbc</artifactId>
    <version>10.1.18</version>
</dependency>

// Configuration
PoolProperties p = new PoolProperties();
p.setUrl("jdbc:mysql://localhost:3306/mydb");
p.setUsername("user");
p.setPassword("password");
p.setMaxActive(10);
p.setMaxIdle(5);
p.setMinIdle(2);
p.setMaxWait(30000);

DataSource ds = new DataSource(p);

Best Practices

Summary