Home/Learn/MySQL/Connection Pooling

Connection Pooling

Intermediate

Scaling

MySQL cannot handle thousands of persistent connections efficiently; use a connection pool (HikariCP, PgBouncer, ProxySQL) to multiplex application threads over fewer DB connections.

Overview

Each MySQL connection spawns a thread on the server and holds memory (typically 1–2 MB per connection). At scale, thousands of application threads opening individual connections saturate MySQL's thread pool and cause context-switching overhead. Connection pooling solves this by maintaining a small pool of persistent connections that application threads borrow and return. HikariCP is the default Spring Boot pool — extremely fast and lightweight. For microservice deployments with many pods, each pod has its own HikariCP pool, meaning the actual connection count reaching MySQL is pods × pool-size. For very large fleets, a middleware proxy like ProxySQL multiplexes thousands of application connections over a smaller number of real MySQL connections.

HikariCP configuration and pool sizing

The correct HikariCP pool size is often much smaller than developers expect. HikariCP's own documentation recommends: pool size = (core_count * 2) + effective_spindle_count for databases on spinning disk; for SSDs, even smaller pools often outperform larger ones. Over-sizing the pool causes contention and queue starvation. The key settings: maximumPoolSize (max connections), minimumIdle (idle connections to maintain), connectionTimeout (wait for available connection), and idleTimeout (when to close idle connections).

YAML — HikariCP pool configuration for Spring Boot

# application.yml — HikariCP configuration
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/shop?useSSL=true&serverTimezone=UTC
    username: app_user
    password: ${DB_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 10          # max connections per app instance
      minimum-idle: 5                # keep 5 idle connections warm
      connection-timeout: 30000      # 30s wait for available connection
      idle-timeout: 600000           # remove idle connections after 10 min
      max-lifetime: 1800000          # replace connections older than 30 min
      pool-name: OrderServicePool
      connection-test-query: SELECT 1  # validation query for pre-5.x drivers
      # auto-commit: false           # set false if using Spring @Transactional

# Pool sizing formula (SSD database):
# 10 pods × 10 connections = 100 total MySQL connections
# MySQL max_connections should be set >= total expected pool connections + buffer

Diagnosing pool exhaustion and connection leaks

Pool exhaustion occurs when all connections are in use and new requests time out waiting. Common causes: slow queries holding connections too long, connection leaks (connections borrowed but never returned), or under-sized pool. HikariCP logs pool stats; Spring Boot Actuator exposes HikariCP metrics at /actuator/metrics/hikaricp.connections.*.

YAML + SQL — pool exhaustion detection and leak identification

# HikariCP logging — detect pool exhaustion
# application.yml
logging:
  level:
    com.zaxxer.hikari: DEBUG          # enables pool stats logging
    com.zaxxer.hikari.HikariConfig: DEBUG

# Metrics (Micrometer / Prometheus)
# hikaricp_connections_active       — currently borrowed connections
# hikaricp_connections_idle         — available connections
# hikaricp_connections_pending      — threads waiting for a connection
# hikaricp_connections_timeout_total — total connection timeout events

# PromQL alert: pool >80% utilised
hikaricp_connections_active / hikaricp_connections_max > 0.8

# Enable leak detection (logs stack trace if connection held > 2s)
spring:
  datasource:
    hikari:
      leak-detection-threshold: 2000  # ms — logs WARNING with stack trace

# MySQL server: check open connections
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;       -- see what each connection is doing

ProxySQL for large-scale connection multiplexing

When a fleet of microservice pods would create tens of thousands of MySQL connections, ProxySQL acts as a middleware layer: applications connect to ProxySQL (fast, no authentication overhead), and ProxySQL multiplexes them over a much smaller pool of real MySQL connections using connection multiplexing. ProxySQL also provides query routing (read/write splitting to primary/replicas), query caching, and connection retries on failover.

YAML — ProxySQL connection multiplexing and read/write splitting

# Architecture:
# 500 pods × 10 HikariCP = 5000 connection attempts to MySQL
# ProxySQL sits between app and MySQL:
# 500 pods → ProxySQL (5000 frontend conns) → MySQL (200 backend conns)

# application.yml — point to ProxySQL instead of MySQL directly
spring:
  datasource:
    url: jdbc:mysql://proxysql-host:6033/shop   # ProxySQL port 6033
    hikari:
      maximum-pool-size: 10

# ProxySQL config (proxysql.cnf) — simplified
mysql_servers:
  - address: "mysql-primary"
    port: 3306
    hostgroup: 0        # write group
    max_connections: 100

  - address: "mysql-replica-1"
    port: 3306
    hostgroup: 1        # read group
    max_connections: 200

mysql_query_rules:
  - rule_id: 1
    match_pattern: "^SELECT"
    destination_hostgroup: 1    # route SELECTs to read replicas
    apply: 1
  - rule_id: 2
    match_pattern: ".*"
    destination_hostgroup: 0    # all other queries to primary

Key Points to Remember

1MySQL creates one thread per connection — thousands of connections cause memory and context-switching overhead
2HikariCP pool size formula: (core_count × 2) + spindle_count — often 5–20 is optimal, not hundreds
3Over-sizing the pool causes thread contention and latency spikes; under-sizing causes timeout errors
4leak-detection-threshold logs a stack trace if a connection is held longer than the threshold — essential for diagnosing leaks
5In a microservice fleet, total MySQL connections = pods × pool-size; ProxySQL multiplexes these to fewer real connections
6Monitor hikaricp_connections_pending — a persistently non-zero value means the pool is the bottleneck

Interview Questions

What is the HikariCP recommended pool sizing formula and why is a large pool often counterproductive?

MediumThoughtworks

How would you detect a connection leak in a Spring Boot application with HikariCP?

MediumAmazon

You have 200 pods each with maximumPoolSize=50. How many MySQL connections does your server see and is this a problem?

HardNetflix

What is ProxySQL and how does it reduce the connection count on a MySQL primary?

HardFlipkart

What Prometheus metric would you alert on to detect HikariCP pool saturation?

EasyGoogle

Ask Aria about Connection Pooling

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Sharding Concepts

Backup & Recovery