When building enterprise Java applications on the Jakarta EE platform, database connection management is critical for both performance and stability. One common issue that can severely impact application performance is connection and statement leaks. In this Nugget Friday, we’ll explore how Payara Server helps detect and prevent these leaks, ensuring your applications run smoothly and efficiently.
The Problem: The Silent Performance Killer
Connection and statement leaks are like slow memory leaks – they start small but can bring down even the most powerful applications. Here’s what typically happens:
Connection Leaks
- A database connection is acquired but never returned to the pool
- Exception handling paths don’t properly close connections
- Connections are held across long-running processes
- Application code assumes framework or container will handle cleanup
Statement Leaks
- PreparedStatements created but not closed
- ResultSet objects remaining open
- Cursor exhaustion in the database
- Memory leaks from accumulated statement metadata
Consider this common problematic pattern:
public class LeakProneDAO {
@Resource(lookup = "jdbc/MyPool")
DataSource dataSource;
public User findUser(String id) {
Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setString(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
// Resource leak: Connection, PreparedStatement, and ResultSet never closed
return new User(rs.getString("id"), rs.getString("name"));
}
return null;
}
}
The Solution: Payara’s Multi-layered Leak Detection
Payara Server provides a sophisticated leak detection system with multiple layers of protection. Let’s explore each component.
Connection Leak Detection and Reclamation
First, configure basic connection leak detection:
asadmin set resources.jdbc-connection-pool.test-pool.connection-leak-timeout-in-seconds=5
asadmin set resources.jdbc-connection-pool.test-pool.connection-leak-reclaim=true
But the real power comes from combining it with validation:
Enable connection validation
asadmin set resources.jdbc-connection-pool.test-pool.is-connection-validation-required=true
asadmin set resources.jdbc-connection-pool.test-pool.connection-validation-method=custom-validation
asadmin set resources.jdbc-connection-pool.test-pool.validation-classname=org.glassfish.api.jdbc.validation.MySQLConnectionValidation
Configure validation frequency
asadmin set resources.jdbc-connection-pool.test-pool.validate-atmost-once-period-in-seconds=30
Statement Leak Detection
Statement leaks require a different approach. Configure both detection and prevention:
Enable statement leak detection
asadmin set resources.jdbc-connection-pool.test-pool.statement-leak-timeout-in-seconds=3
asadmin set resources.jdbc-connection-pool.test-pool.statement-leak-reclaim=true
Configure statement caching for performance
asadmin set resources.jdbc-connection-pool.test-pool.statement-cache-size=50
Advanced Pool Sizing and Management
Proper pool sizing is important for leak detection:
Configure pool sizing
asadmin set resources.jdbc-connection-pool.test-pool.steady-pool-size=10
asadmin set resources.jdbc-connection-pool.test-pool.max-pool-size=100
asadmin set resources.jdbc-connection-pool.test-pool.pool-resize-quantity=2
Set idle timeout to help identify potential leaks
asadmin set resources.jdbc-connection-pool.test-pool.idle-timeout-in-seconds=300
Once these values are set, if connection or statement leaks are detected, you will see messages similar to the example below in the application log:
WARNING: A potential connection leak detected for connection pool test-pool. The stack trace of the thread is provided below:
Implementing a Comprehensive Solution
Here’s a pattern that combines all these features with proper resource management:
@Stateless
public class LeakProofDAO {
@Resource(lookup = "jdbc/MyPool")
DataSource dataSource;
public User findUser(String id) {
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
ps.setString(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return new User(rs.getString("id"), rs.getString("name"));
}
return null;
}
} catch (SQLException e) {
// Log and handle exception
throw new DataAccessException("Error finding user", e);
}
}
}
Real-world Monitoring and Tuning
Monitor Leak Detection Events
Enable SQL trace logging to catch issues early:
asadmin set resources.jdbc-connection-pool.test-pool.sql-trace-listeners=com.example.SQLTraceLogger
Implement the custom SQL trace listener:
public class SQLTraceLogger implements SQLTraceListener {
@Override
public void sqlTrace(SQLTraceRecord record) {
if (record.getExecutionTime() > 1000) {
Logger.warning("Slow SQL detected: " + record.getSQL());
}
}
}
Fine-tuning Based on Metrics
Monitor these key metrics to optimize your settings:
- Connection wait times
- Statement execution times
- Pool size utilization
- Leak detection events
- Statement cache hits/misses
Environment-specific Configurations
Development Environment
asadmin set resources.jdbc-connection-pool.test-pool.connection-leak-timeout-in-seconds=2
asadmin set resources.jdbc-connection-pool.test-pool.statement-leak-timeout-in-seconds=1
Production Environment
asadmin set resources.jdbc-connection-pool.test-pool.connection-leak-timeout-in-seconds=30
asadmin set resources.jdbc-connection-pool.test-pool.statement-leak-timeout-in-seconds=15
Best Practices and Common Pitfalls
Always Use Try-with-Resources
- Automatically closes resources
- Handles exceptions properly
- Works with multiple resources
Pool Sizing Guidelines
- steady-pool-size = average concurrent connections
- max-pool-size = peak concurrent connections * 1.2
- pool-resize-quantity = (max-pool-size – steady-pool-size) / 10
Validation Strategies
- Use custom validation for specific database types
- Balance validation frequency with performance
- Consider connection age in validation rules
Conclusions
Properly configured connection pool leak detection is important for maintaining healthy Jakarta EE applications. By combining Payara Server’s leak detection features with proper resource management patterns and monitoring, you can prevent connection and statement leaks from impacting your application’s performance and reliability.
Remember to:
- Configure appropriate timeouts for your use case
- Implement proper resource management patterns
- Monitor and adjust based on real usage patterns
- Use environment-specific configurations
- Regularly review and update leak detection settings
Ready to take control of your database connections? Download a free trial of Payara Server Enterprise to test these features in your environment and prevent resource leaks before they impact production. Your applications (and your team) will thank you! Happy coding!
{{cta(‘5a2eba92-d5bf-4f74-a84a-c170a659fe46’)}}
3 minutes
