Connection Pool Management

MySQL Connection Pooling and Management Guide

Efficient connection management is critical for scalability. This guide covers connection pool architecture, configuration, and performance optimization.

1. Connection Pool Architecture

1.1 Why Connection Pooling?

WITHOUT CONNECTION POOLING (Anti-pattern):

New connection for every query:
conn = mysql.getConnection();  -- Wait 50-100ms (handshake)
result = conn.execute(query);  -- 1ms
conn.close();  -- Release

Total latency: 50-100ms + 1ms = ~101ms per query

WITH CONNECTION POOLING (Best practice):

conn = pool.getConnection();  -- Already open (1ms)
result = conn.execute(query);  -- 1ms
pool.returnConnection(conn);  -- 0.1ms

Total latency: 1ms + 1ms + 0.1ms = ~2ms per query

Benefits:
├─ 50x faster query latency
├─ Fewer TCP connections (50 vs 5000)
├─ Lower server resource usage
└─ Better scalability

CONNECTION LIFECYCLE:

1. Pool startup
   └─ Pre-create N connections (min_connections)

2. Connection in-use
   └─ Application query runs
   └─ Kept alive, metrics tracked

3. Connection idle
   └─ Returned to pool after query
   └─ Kept open for next request
   └─ Idle timeout (typically 5-30 minutes)

4. Connection eviction
   └─ If idle > timeout: close connection
   └─ If max_connections reached: queue new requests
   └─ If pool growth requested: create new connections

1.2 Pool Configuration Parameters

CRITICAL PARAMETERS:

min_connections (core pool size):
├─ Default: 5-10
├─ Pre-created at startup
├─ Always maintained open
└─ Rule of thumb: = app threads / 2

Example: 100 app threads → 50 min_connections

max_connections (max pool size):
├─ Default: 20-50
├─ Hard limit on simultaneous connections
├─ Queue requests if at limit
└─ Calculate: (threads) × (max_concurrent_queries_per_thread)

Example: 100 app threads → 200 max_connections (2 per thread)

connection_timeout:
├─ Wait time for available connection (milliseconds)
├─ Default: 30000 (30 seconds)
└─ If exceeded: ConnectionTimeoutException

idle_timeout:
├─ Idle connection eviction time (minutes)
├─ Default: 10 minutes
└─ Recommendation: 15 minutes

max_lifetime (connection max age):
├─ Maximum connection age (minutes)
├─ Older connections recycled
└─ Recommendation: 30 minutes

RECOMMENDED CONFIGURATION:

For 100 concurrent app servers, 1 MySQL instance:

min_connections: 10
max_connections: 100
connection_timeout: 30000
idle_timeout: 15
max_lifetime: 30

HikariCP configuration (Java):
dataSource.setMinimumIdle(10);
dataSource.setMaximumPoolSize(100);
dataSource.setConnectionTimeout(30000);
dataSource.setIdleTimeout(900000);  // 15 minutes
dataSource.setMaxLifetime(1800000);  // 30 minutes

ProxySQL configuration (proxy-based):
[mysql_variables]
interfaces="127.0.0.1:6033"
max_connections=2000
default_max_connections=100

[[mysql_servers]]
hostgroup_id=0
hostname="192.168.1.10"
port=3306
weight=1
max_connections=100

2. Proxy-Based Connection Pooling

2.1 ProxySQL and MyCat Comparison

PROXYSQL (Recommended for most use cases)

Architecture:
Application → ProxySQL (port 6033) → MySQL

Features:
✅ Connection pooling (per thread)
✅ Query caching
✅ Read/write splitting
✅ Sharding support
✅ Connection multiplexing
✅ Dynamic configuration (no restart)
✅ Query rewriting rules
✅ Admin interface

Example:
// Application connects to ProxySQL
mysql -h 127.0.0.1 -P 6033 -u app_user

// ProxySQL multiplexes to backend MySQL
// 100 app connections → 10 MySQL connections

Query routing:
SELECT * FROM users         → read replicas
INSERT INTO orders ...      → master

Configuration in ProxySQL:

INSERT INTO mysql_users (username, password, active, default_hostgroup)
VALUES ('app_user', 'password_hash', 1, 0);

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (0, 'master.example.com', 3306, 100),
       (1, 'replica1.example.com', 3306, 100),
       (1, 'replica2.example.com', 3306, 100);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT .*FOR UPDATE', 0),  -- Master
       (2, 1, '^SELECT', 1);               -- Replica

LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

MYCAT (For sharding-focused workloads)

Features:
✅ Sharding (horizontal partitioning)
✅ Connection pooling
✅ Read/write splitting
❌ Slower than ProxySQL
❌ More complex configuration
✅ Java-based (requires JVM)

COMPARISON:

Feature              | ProxySQL | MyCat
─────────────────────┼──────────┼──────
Pooling             | ✅       | ✅
Read/Write split    | ✅       | ✅
Sharding            | Limited  | ✅✅
Performance         | Fast     | Medium
Complexity          | Low      | High
Learning curve      | Easy     | Steep

Recommendation:
├─ < 100GB data: Use application pooling (HikariCP)
├─ 100GB - 1TB: Add ProxySQL for pooling
└─ > 1TB sharded: Add MyCat for sharding routing

2.2 Connection Reuse and Keepalive

TCP KEEPALIVE (prevents connection timeout):

MySQL: SET SESSION wait_timeout = 28800 (8 hours)

Application-level keepalive:

Option 1: Connection pool test
HikariCP:
dataSource.setConnectionTestQuery("SELECT 1");
dataSource.setLeakDetectionThreshold(60000);  // 60 second leak

Option 2: ProxySQL multiplexing
One idle connection reused by multiple app connections
ProxySQL handles keepalive automatically

Heartbeat configuration in ProxySQL:
mysql-ping_interval_server_msec=10000
mysql-ping_timeout_server_msec=800

CONNECTION REUSE PATTERNS:

Good pattern:
1. Get connection
2. Execute 1-N queries
3. Return to pool
4. Repeat from step 1

Bad pattern (don't do this):
global.dbConn = pool.getConnection();  // Anti-pattern!
// Connection idle, wasting pool slot

Example (good - Java pseudocode):
conn = pool.getConnection();
try {
  user = conn.query("SELECT * FROM users WHERE id=?", userId);
  orders = conn.query("SELECT * FROM orders WHERE user_id=?", userId);
  return {user, orders};
} finally {
  pool.returnConnection(conn);
}

3. Performance Tuning and Monitoring

MONITORING POOL HEALTH:

Monitor:
├─ Active connections (currently in use)
├─ Idle connections (waiting for use)
├─ Queue depth (waiting for connection)
├─ Connection creation rate
└─ Connection wait time

HikariCP metrics:
hikaricp_active_connections: Currently executing queries
hikaricp_idle_connections: Available for reuse
hikaricp_pending_threads: Waiting for connection
hikaricp_connection_timeout_total: Timeouts

Alert on:
- active_connections > 80% of max
- queue_depth > 10 (many requests waiting)
- connection_creation_rate > 1 per second (churn)
- connection_wait_time > 1 second (contention)

ProxySQL monitoring:
SHOW STATS_MYSQL_CONNECTION_POOL;

TUNING FOR PERFORMANCE:

If queue_depth > 0 (connections waiting):
1. Increase max_connections
   [For ProxySQL] max_connections = 2000
   [For HikariCP] dataSource.setMaximumPoolSize(200);

2. Reduce query time
   -- Optimize slow queries
   -- Add missing indexes

3. Increase min_connections
   -- Keeps connections pre-opened
   -- Reduces connection creation overhead

Connection creation overhead:
├─ 50-100ms per connection (TCP handshake)
├─ 10-50ms SSL negotiation
├─ 1-5ms authentication
└─ Total: 61-155ms

BULK OPERATION OPTIMIZATION:

Bad: One connection per operation
for batch in batches:
  conn = pool.getConnection();
  INSERT batch;
  pool.returnConnection(conn);

Better: Batch multiple operations
conn = pool.getConnection();
for batch in batches:
  INSERT batch;
pool.returnConnection(conn);

Best: Prepared statement batch insert
conn = pool.getConnection();
stmt = conn.prepareStatement("INSERT INTO users VALUES (?,?,?)");
for user in users:
  stmt.setString(1, user.name);
  stmt.setString(2, user.email);
  stmt.addBatch();
stmt.executeBatch();
pool.returnConnection(conn);

Result: 1000x faster for 10K inserts