Introduction:

While interacting with databases, connections play a predominant role in overall performance and robustness of the system. One of the trivial technique used by many engineers is a connection pooling to avoid the overhead of creating a new connection every time for running database queries.

Why connection Pooling:

Database connections are precious resources and managing them effectively will help to build robust applications.

Downsides of creating new connections every time:

Creating new connection costs us significant memory allocation. Each newly created connection consumes around 6MB to10 MB of memory. This memory is different from the memory required by database to run memory intensive queries i.e dedicated 6MB to 10MB for connection. With growing number of requests it’s hard to manage memory by creating new connections each time.

Creating new connection requires username, password, TSL specifications in order to authenticate with database and it becomes overhead with growing number of connections.

View Hikari CP from here.

In my scenario, we were adding multiple tenants(Dynamic datasource based on origin url per request), but we had a shared hikari pool. Lets start with the tenant information to start with

Given the tenants, each tenant could have multiple origin url. While starting the server all the tenants used to work normally. But after letting the system rest for a while, only one would work normally.

My Hikari-CP Config

minimum-idle=5
idle-timeout=95000
maximum-pool-size=20
auto-commit=true
connection-timeout=30000
max-lifetime=100000
pool-name=HikariCorePool
leak-detection-threshold=30000
connection-test-query=SELECT 1

All of my other tenants were timing out to get the connection.

My Tenant Config

package tech.hobes.usersservice.config.multitenant;

import lombok.extern.slf4j.Slf4j;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Component
@Slf4j
public class MultitenantConfiguration {

    private final String DEFAULT_TENANT = "https://x.x.x";
    @Bean
    @ConfigurationProperties(prefix = "tenants")
    public DataSource dataSource() {
        File[] files = Paths.get("allTenants").toFile().listFiles();
        Map<Object, Object> resolvedDataSources = new HashMap<>();

        for (File propertyFile : files) {
            Properties tenantProperties = new Properties();
            DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();

            try {
                tenantProperties.load(new FileInputStream(propertyFile));
                String tenantId = tenantProperties.getProperty("name");
                String domain = tenantProperties.getProperty("domain");
                log.info(domain);

                dataSourceBuilder.driverClassName(tenantProperties.getProperty("datasource.driver-class-name"));
                dataSourceBuilder.username(tenantProperties.getProperty("datasource.username"));
                dataSourceBuilder.password(tenantProperties.getProperty("datasource.password"));
                dataSourceBuilder.url(tenantProperties.getProperty("datasource.url"));
                resolvedDataSources.put(domain, dataSourceBuilder.build());
            } catch (IOException exp) {
                throw new RuntimeException("Problem in tenant datasource:" + exp);
            }
        }

        AbstractRoutingDataSource dataSource = new MultitenantDataSource();
        dataSource.setDefaultTargetDataSource(resolvedDataSources.get(DEFAULT_TENANT));
        dataSource.setTargetDataSources(resolvedDataSources);

        dataSource.afterPropertiesSet();
        return dataSource;
    }


    public Map<String, DataSource> getAllDataSources() {
        File[] files = Paths.get("allTenants").toFile().listFiles();
        Map<String, DataSource> resolvedDataSources = new HashMap<>();

        for (File propertyFile : files) {
            Properties tenantProperties = new Properties();
            DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();

            try(FileInputStream fileInputStream = new FileInputStream(propertyFile)) {
                tenantProperties.load(fileInputStream);
                String tenantId = tenantProperties.getProperty("name");

                dataSourceBuilder.driverClassName(tenantProperties.getProperty("datasource.driver-class-name"));
                dataSourceBuilder.username(tenantProperties.getProperty("datasource.username"));
                dataSourceBuilder.password(tenantProperties.getProperty("datasource.password"));
                dataSourceBuilder.url(tenantProperties.getProperty("datasource.url"));
                resolvedDataSources.put(tenantId, dataSourceBuilder.build());
            } catch (IOException exp) {
                throw new RuntimeException("Problem in tenant datasource:" + exp);
            }

//            resolvedDataSources.entrySet().stream().filter(y -> !y.getKey().equals(DEFAULT_TENANT)).forEach(x -> {
//                    Flyway flyway = Flyway.configure().dataSource(x.getValue()).locations("/db/migration", "/db/schema").load();
//                    flyway.repair();
//                    flyway.migrate();
//                }
//        );
        }
        return resolvedDataSources;

    }
}

My Tenant Filter as

package tech.hobes.usersservice.config.multitenant;

import lombok.extern.slf4j.Slf4j;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;

@Component
@Order(1)
@Slf4j
public class TenantFilter implements Filter {
    @Override
    public void doFilter(ServletRequest request, ServletResponse response,
                         FilterChain chain) throws IOException, ServletException {

        HttpServletRequest req = (HttpServletRequest) request;
        String url = String.valueOf(((HttpServletRequest) request).getRequestURL());
        String domain = req.getHeader("Origin");
        TenantContext.setCurrentTenant(domain);
        log.info("============================= URL =================================" + url);
        log.info("============================= DOMAIN =================================" + domain);

        try {
            chain.doFilter(request, response);
        } finally {
            TenantContext.setCurrentTenant("");
        }
    }
}

and Tenant Thread Context as

package tech.hobes.usersservice.config.multitenant;

public class TenantContext {
    private static final ThreadLocal<String> CURRENT_TENANT = new ThreadLocal<>();

    public static String getCurrentTenant() {
        return CURRENT_TENANT.get();
    }

    public static void setCurrentTenant(String tenant) {
        CURRENT_TENANT.set(tenant);
    }
}

Now we are facing an issue on getting a connection from the pool.