Nugget Friday - Preventing Memory Leaks with JDBC Connection Pool Management in Payara Server

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!