0
# Connection Pooling
1
2
Built-in connection pooling with lifecycle management, JMX monitoring, and configurable pool behavior for enterprise applications.
3
4
## Capabilities
5
6
### Pooled Connection Management
7
8
Individual pooled connections with event notification and lifecycle management.
9
10
```java { .api }
11
/**
12
* Pooled connection implementation
13
* Implements both PooledConnection and XAConnection interfaces
14
*/
15
public class MariaDbPoolConnection implements PooledConnection, XAConnection {
16
/**
17
* Get the underlying database connection
18
* @return Connection instance
19
* @throws SQLException if connection retrieval fails
20
*/
21
public Connection getConnection() throws SQLException;
22
23
/**
24
* Close the pooled connection and return it to pool
25
* @throws SQLException if closing fails
26
*/
27
public void close() throws SQLException;
28
29
/**
30
* Add listener for connection events
31
* @param listener Event listener for connection lifecycle
32
*/
33
public void addConnectionEventListener(ConnectionEventListener listener);
34
35
/**
36
* Remove connection event listener
37
* @param listener Event listener to remove
38
*/
39
public void removeConnectionEventListener(ConnectionEventListener listener);
40
41
/**
42
* Add listener for statement events
43
* @param listener Statement event listener
44
*/
45
public void addStatementEventListener(StatementEventListener listener);
46
47
/**
48
* Remove statement event listener
49
* @param listener Statement event listener to remove
50
*/
51
public void removeStatementEventListener(StatementEventListener listener);
52
53
// XA transaction support
54
/**
55
* Get XA resource for distributed transactions
56
* @return XAResource instance
57
* @throws SQLException if XA resource retrieval fails
58
*/
59
public XAResource getXAResource() throws SQLException;
60
}
61
62
/**
63
* Extended pooled connection for global transactions
64
* Maintains connection affinity for transaction consistency
65
*/
66
public class MariaDbPoolPinnedConnection extends MariaDbPoolConnection {
67
// Provides connection pinning for global transaction contexts
68
// Ensures same physical connection is used throughout transaction
69
}
70
```
71
72
**Usage Examples:**
73
74
```java
75
// Basic pooled connection usage
76
MariaDbDataSource dataSource = new MariaDbDataSource();
77
dataSource.setUrl("jdbc:mariadb://localhost:3306/mydb");
78
dataSource.setUser("user");
79
dataSource.setPassword("password");
80
81
// Get pooled connection
82
PooledConnection pooledConn = dataSource.getPooledConnection();
83
84
// Add connection event listener
85
pooledConn.addConnectionEventListener(new ConnectionEventListener() {
86
public void connectionClosed(ConnectionEvent event) {
87
System.out.println("Pooled connection returned to pool");
88
}
89
90
public void connectionErrorOccurred(ConnectionEvent event) {
91
System.err.println("Pooled connection error: " + event.getSQLException().getMessage());
92
// Pool can invalidate this connection
93
}
94
});
95
96
// Get and use the actual connection
97
Connection conn = pooledConn.getConnection();
98
try (Statement stmt = conn.createStatement()) {
99
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
100
// Use connection...
101
} finally {
102
conn.close(); // Returns connection to pool, triggers connectionClosed event
103
}
104
105
// Close pooled connection when done
106
pooledConn.close();
107
```
108
109
### Connection Pool Implementation
110
111
Internal connection pool with automatic lifecycle management.
112
113
```java { .api }
114
/**
115
* Internal connection pool implementation
116
*/
117
public class Pool {
118
/**
119
* Get connection from pool
120
* @return Pooled connection
121
* @throws SQLException if no connection available
122
*/
123
public Connection getConnection() throws SQLException;
124
125
/**
126
* Get connection with timeout
127
* @param timeout Maximum wait time in milliseconds
128
* @return Pooled connection
129
* @throws SQLException if timeout exceeded or error occurs
130
*/
131
public Connection getConnection(long timeout) throws SQLException;
132
133
/**
134
* Close connection pool and all connections
135
* @throws SQLException if closing fails
136
*/
137
public void close() throws SQLException;
138
139
/**
140
* Get current pool statistics
141
* @return Pool statistics object
142
*/
143
public PoolStats getStats();
144
145
// Internal pool management methods
146
void addConnection();
147
void removeConnection(Connection conn);
148
void validateConnections();
149
}
150
151
/**
152
* Pool management and factory
153
*/
154
public class Pools {
155
/**
156
* Get or create named pool
157
* @param poolName Pool identifier
158
* @param config Pool configuration
159
* @return Pool instance
160
*/
161
public static Pool getPool(String poolName, Configuration config);
162
163
/**
164
* Remove and close named pool
165
* @param poolName Pool to remove
166
*/
167
public static void remove(String poolName);
168
169
/**
170
* Close all pools
171
*/
172
public static void close();
173
174
/**
175
* Get all active pool names
176
* @return Set of pool names
177
*/
178
public static Set<String> getPoolNames();
179
}
180
```
181
182
**Usage Examples:**
183
184
```java
185
// Using pool directly (advanced usage)
186
Configuration config = Configuration.parse(
187
"jdbc:mariadb://localhost:3306/mydb?pool=true&maxPoolSize=20&minPoolSize=5",
188
new Properties()
189
);
190
191
Pool pool = Pools.getPool("MyApplicationPool", config);
192
193
// Get connection from pool
194
Connection conn = pool.getConnection();
195
try {
196
// Use connection
197
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
198
stmt.setInt(1, 123);
199
ResultSet rs = stmt.executeQuery();
200
// Process results...
201
} finally {
202
conn.close(); // Returns to pool
203
}
204
205
// Pool statistics
206
PoolStats stats = pool.getStats();
207
System.out.println("Active connections: " + stats.getActiveConnections());
208
System.out.println("Idle connections: " + stats.getIdleConnections());
209
210
// Close pool when application shuts down
211
pool.close();
212
```
213
214
### JMX Monitoring
215
216
JMX management interface for monitoring connection pool health and performance.
217
218
```java { .api }
219
/**
220
* JMX monitoring interface for connection pools
221
*/
222
public interface PoolMBean {
223
/**
224
* Get number of active (in-use) connections
225
* @return Active connection count
226
*/
227
int getActiveConnections();
228
229
/**
230
* Get number of idle (available) connections
231
* @return Idle connection count
232
*/
233
int getIdleConnections();
234
235
/**
236
* Get total number of connections in pool
237
* @return Total connection count
238
*/
239
int getTotalConnections();
240
241
/**
242
* Get maximum pool size
243
* @return Maximum connections allowed
244
*/
245
int getMaxPoolSize();
246
247
/**
248
* Get minimum pool size
249
* @return Minimum connections maintained
250
*/
251
int getMinPoolSize();
252
253
/**
254
* Get number of connection requests served
255
* @return Total requests served
256
*/
257
long getConnectionsServed();
258
259
/**
260
* Get number of failed connection attempts
261
* @return Failed connection count
262
*/
263
long getConnectionFailures();
264
265
/**
266
* Get average connection acquisition time
267
* @return Average time in milliseconds
268
*/
269
double getAverageAcquisitionTime();
270
271
/**
272
* Get pool creation timestamp
273
* @return Pool creation time
274
*/
275
long getPoolCreationTime();
276
277
/**
278
* Force pool validation (check all idle connections)
279
*/
280
void validatePool();
281
282
/**
283
* Reset pool statistics
284
*/
285
void resetStatistics();
286
}
287
```
288
289
**Usage Examples:**
290
291
```java
292
// Enable JMX monitoring for connection pool
293
String jmxUrl = "jdbc:mariadb://localhost:3306/mydb?" +
294
"pool=true&" +
295
"registerJmxPool=true&" +
296
"poolName=ProductionPool&" +
297
"maxPoolSize=25&" +
298
"minPoolSize=5";
299
300
MariaDbPoolDataSource poolDataSource = new MariaDbPoolDataSource();
301
poolDataSource.setUrl(jmxUrl);
302
poolDataSource.setUser("user");
303
poolDataSource.setPassword("password");
304
305
// Pool will be registered as JMX MBean at:
306
// org.mariadb.jdbc.pool:type=Pool,name=ProductionPool
307
308
// Access via JMX programmatically
309
MBeanServer server = ManagementFactory.getPlatformMBeanServer();
310
ObjectName poolName = new ObjectName("org.mariadb.jdbc.pool:type=Pool,name=ProductionPool");
311
312
// Get pool statistics
313
Integer activeConnections = (Integer) server.getAttribute(poolName, "ActiveConnections");
314
Integer idleConnections = (Integer) server.getAttribute(poolName, "IdleConnections");
315
Long connectionsServed = (Long) server.getAttribute(poolName, "ConnectionsServed");
316
317
System.out.println("Active: " + activeConnections);
318
System.out.println("Idle: " + idleConnections);
319
System.out.println("Total served: " + connectionsServed);
320
321
// Invoke pool operations
322
server.invoke(poolName, "validatePool", null, null);
323
server.invoke(poolName, "resetStatistics", null, null);
324
```
325
326
## Pool Configuration
327
328
### Basic Pool Configuration
329
330
```java
331
// Enable connection pooling with basic settings
332
String basicPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
333
"pool=true&" + // Enable pooling
334
"maxPoolSize=20&" + // Maximum 20 connections
335
"minPoolSize=5"; // Minimum 5 connections
336
337
// Pool with connection validation
338
String validatedPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
339
"pool=true&" +
340
"maxPoolSize=15&" +
341
"minPoolSize=3&" +
342
"maxIdleTime=600&" + // Close idle connections after 10 minutes
343
"poolValidMinDelay=1000"; // Minimum 1 second between validations
344
```
345
346
### Advanced Pool Configuration
347
348
```java
349
// Production-ready pool configuration
350
String productionPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
351
// Pool sizing
352
"pool=true&" +
353
"maxPoolSize=50&" + // High concurrency support
354
"minPoolSize=10&" + // Always maintain 10 connections
355
356
// Connection lifecycle
357
"maxIdleTime=300&" + // 5 minute idle timeout
358
"poolValidMinDelay=5000&" + // Validate every 5 seconds minimum
359
"useResetConnection=true&" + // Use RESET CONNECTION for cleanup
360
361
// Monitoring and naming
362
"registerJmxPool=true&" + // Enable JMX monitoring
363
"poolName=ProductionAppPool&" + // Named pool for identification
364
365
// Connection optimization
366
"connectTimeout=5000&" + // 5 second connect timeout
367
"socketTimeout=30000&" + // 30 second socket timeout
368
"tcpKeepAlive=true&" + // Keep connections alive
369
370
// Performance tuning
371
"cachePrepStmts=true&" + // Cache prepared statements
372
"prepStmtCacheSize=250&" + // Statement cache size
373
"useServerPrepStmts=true&" + // Use server-side prepared statements
374
"useCompression=true"; // Enable protocol compression
375
```
376
377
### Pool Lifecycle Management
378
379
```java
380
// Proper pool lifecycle management
381
public class DatabaseManager {
382
private MariaDbPoolDataSource poolDataSource;
383
private final String poolUrl;
384
385
public DatabaseManager() {
386
this.poolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
387
"pool=true&maxPoolSize=20&minPoolSize=5®isterJmxPool=true&poolName=AppPool";
388
}
389
390
public void initialize() throws SQLException {
391
poolDataSource = new MariaDbPoolDataSource();
392
poolDataSource.setUrl(poolUrl);
393
poolDataSource.setUser(System.getenv("DB_USER"));
394
poolDataSource.setPassword(System.getenv("DB_PASSWORD"));
395
396
// Test pool connectivity
397
try (Connection testConn = poolDataSource.getConnection()) {
398
try (Statement stmt = testConn.createStatement()) {
399
stmt.executeQuery("SELECT 1");
400
}
401
}
402
403
System.out.println("Database pool initialized successfully");
404
}
405
406
public Connection getConnection() throws SQLException {
407
if (poolDataSource == null) {
408
throw new SQLException("Pool not initialized");
409
}
410
return poolDataSource.getConnection();
411
}
412
413
public void shutdown() {
414
if (poolDataSource != null) {
415
try {
416
poolDataSource.close();
417
System.out.println("Database pool closed");
418
} catch (SQLException e) {
419
System.err.println("Error closing pool: " + e.getMessage());
420
}
421
}
422
}
423
424
// JVM shutdown hook
425
static {
426
Runtime.getRuntime().addShutdownHook(new Thread(() -> {
427
// Close all pools on JVM shutdown
428
Pools.close();
429
}));
430
}
431
}
432
433
// Usage in application
434
DatabaseManager dbManager = new DatabaseManager();
435
dbManager.initialize();
436
437
// Use connections throughout application
438
try (Connection conn = dbManager.getConnection()) {
439
// Database operations
440
}
441
442
// Shutdown gracefully
443
dbManager.shutdown();
444
```
445
446
## High Availability with Pooling
447
448
### Pooled High Availability Configuration
449
450
```java
451
// Replication with connection pooling
452
String replicationPoolUrl = "jdbc:mariadb:replication://primary:3306,replica1:3306,replica2:3306/mydb?" +
453
// High availability
454
"retriesAllDown=3&" +
455
"transactionReplay=true&" +
456
457
// Connection pooling
458
"pool=true&" +
459
"maxPoolSize=30&" + // Higher pool size for HA
460
"minPoolSize=10&" +
461
"maxIdleTime=300&" +
462
463
// Pool monitoring
464
"registerJmxPool=true&" +
465
"poolName=ReplicationPool&" +
466
467
// Connection optimization for HA
468
"connectTimeout=3000&" + // Faster failover detection
469
"socketTimeout=15000"; // Shorter socket timeout
470
471
// Load balancing with pooling
472
String loadBalancePoolUrl = "jdbc:mariadb:loadbalance://host1:3306,host2:3306,host3:3306/mydb?" +
473
"pool=true&" +
474
"maxPoolSize=40&" + // Distribute across multiple hosts
475
"minPoolSize=12&" + // 4 connections per host minimum
476
"registerJmxPool=true&" +
477
"poolName=LoadBalancePool";
478
```
479
480
### Pool Health Monitoring
481
482
```java
483
// Health check implementation for pooled connections
484
public class PoolHealthMonitor {
485
private final PoolMBean poolMBean;
486
private final ScheduledExecutorService scheduler;
487
488
public PoolHealthMonitor(String poolName) throws Exception {
489
MBeanServer server = ManagementFactory.getPlatformMBeanServer();
490
ObjectName objectName = new ObjectName("org.mariadb.jdbc.pool:type=Pool,name=" + poolName);
491
this.poolMBean = JMX.newMBeanProxy(server, objectName, PoolMBean.class);
492
this.scheduler = Executors.newScheduledThreadPool(1);
493
}
494
495
public void startMonitoring() {
496
scheduler.scheduleAtFixedRate(this::checkPoolHealth, 0, 30, TimeUnit.SECONDS);
497
}
498
499
private void checkPoolHealth() {
500
try {
501
int active = poolMBean.getActiveConnections();
502
int idle = poolMBean.getIdleConnections();
503
int total = poolMBean.getTotalConnections();
504
int maxSize = poolMBean.getMaxPoolSize();
505
long failures = poolMBean.getConnectionFailures();
506
507
System.out.printf("Pool Health - Active: %d, Idle: %d, Total: %d/%d, Failures: %d%n",
508
active, idle, total, maxSize, failures);
509
510
// Alert if pool is near capacity
511
if (total > maxSize * 0.9) {
512
System.err.println("WARNING: Pool near capacity!");
513
}
514
515
// Alert if too many failures
516
if (failures > 10) {
517
System.err.println("WARNING: High connection failure rate!");
518
poolMBean.resetStatistics(); // Reset after alerting
519
}
520
521
// Validate pool periodically
522
if (System.currentTimeMillis() % 300000 < 30000) { // Every 5 minutes
523
poolMBean.validatePool();
524
}
525
526
} catch (Exception e) {
527
System.err.println("Error monitoring pool: " + e.getMessage());
528
}
529
}
530
531
public void shutdown() {
532
scheduler.shutdown();
533
}
534
}
535
536
// Usage
537
PoolHealthMonitor monitor = new PoolHealthMonitor("ProductionPool");
538
monitor.startMonitoring();
539
540
// Shutdown monitor when application closes
541
Runtime.getRuntime().addShutdownHook(new Thread(monitor::shutdown));
542
```
543
544
### Connection Pool Tuning
545
546
```java
547
// Performance tuning guidelines for different scenarios
548
549
// High throughput OLTP applications
550
String oltpPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
551
"pool=true&" +
552
"maxPoolSize=100&" + // Large pool for high concurrency
553
"minPoolSize=25&" + // Always ready connections
554
"maxIdleTime=60&" + // Quick idle timeout
555
"poolValidMinDelay=500&" + // Frequent validation
556
"useServerPrepStmts=true&" + // Server-side prepared statements
557
"cachePrepStmts=true&" +
558
"prepStmtCacheSize=500"; // Large statement cache
559
560
// Batch processing applications
561
String batchPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
562
"pool=true&" +
563
"maxPoolSize=10&" + // Fewer connections for batch
564
"minPoolSize=5&" +
565
"maxIdleTime=1800&" + // Longer idle timeout
566
"useBulkStmts=true&" + // Bulk operations
567
"useCompression=true&" + // Compression for large data
568
"socketTimeout=120000"; // Longer socket timeout
569
570
// Web application with variable load
571
String webPoolUrl = "jdbc:mariadb://localhost:3306/mydb?" +
572
"pool=true&" +
573
"maxPoolSize=30&" + // Handle peak load
574
"minPoolSize=5&" + // Low baseline
575
"maxIdleTime=300&" + // 5 minute idle timeout
576
"poolValidMinDelay=10000&" + // Infrequent validation
577
"registerJmxPool=true&" + // Monitor web app pools
578
"poolName=WebAppPool";
579
```
580
581
## Best Practices
582
583
### Pool Sizing Guidelines
584
585
```java
586
// Pool sizing formula considerations:
587
// maxPoolSize = (available_memory_for_connections / memory_per_connection)
588
// * utilization_factor
589
//
590
// Typical sizing:
591
// - CPU-bound: pool_size ≈ CPU_cores + 1
592
// - I/O-bound: pool_size ≈ CPU_cores * 2 to 4
593
// - Mixed workload: Start with CPU_cores * 2, tune based on monitoring
594
595
// Example sizing for different server types
596
String smallServerUrl = "jdbc:mariadb://localhost:3306/mydb?" +
597
"pool=true&maxPoolSize=10&minPoolSize=2"; // 2-4 CPU cores
598
599
String mediumServerUrl = "jdbc:mariadb://localhost:3306/mydb?" +
600
"pool=true&maxPoolSize=25&minPoolSize=5"; // 8-16 CPU cores
601
602
String largeServerUrl = "jdbc:mariadb://localhost:3306/mydb?" +
603
"pool=true&maxPoolSize=50&minPoolSize=10"; // 16+ CPU cores
604
```
605
606
### Error Handling and Recovery
607
608
```java
609
// Robust error handling with connection pools
610
public class RobustDatabaseAccess {
611
private final MariaDbPoolDataSource poolDataSource;
612
private final int maxRetries = 3;
613
614
public <T> T executeWithRetry(DatabaseOperation<T> operation) throws SQLException {
615
SQLException lastException = null;
616
617
for (int attempt = 1; attempt <= maxRetries; attempt++) {
618
try (Connection conn = poolDataSource.getConnection()) {
619
return operation.execute(conn);
620
621
} catch (SQLException e) {
622
lastException = e;
623
624
// Don't retry for certain error types
625
if (isNonRetryableError(e)) {
626
throw e;
627
}
628
629
// Log retry attempt
630
System.err.printf("Database operation failed (attempt %d/%d): %s%n",
631
attempt, maxRetries, e.getMessage());
632
633
// Wait before retry (exponential backoff)
634
if (attempt < maxRetries) {
635
try {
636
Thread.sleep(1000 * attempt);
637
} catch (InterruptedException ie) {
638
Thread.currentThread().interrupt();
639
throw new SQLException("Interrupted during retry", ie);
640
}
641
}
642
}
643
}
644
645
throw new SQLException("Max retries exceeded", lastException);
646
}
647
648
private boolean isNonRetryableError(SQLException e) {
649
// Don't retry for syntax errors, constraint violations, etc.
650
String sqlState = e.getSQLState();
651
return sqlState != null && (
652
sqlState.startsWith("42") || // Syntax error
653
sqlState.startsWith("23") // Constraint violation
654
);
655
}
656
657
@FunctionalInterface
658
public interface DatabaseOperation<T> {
659
T execute(Connection conn) throws SQLException;
660
}
661
}
662
663
// Usage
664
RobustDatabaseAccess db = new RobustDatabaseAccess(poolDataSource);
665
666
// Automatically retries on connection failures
667
List<User> users = db.executeWithRetry(conn -> {
668
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE active = 1");
669
ResultSet rs = stmt.executeQuery();
670
return mapResultsToUsers(rs);
671
});
672
```