FATAL: sorry, too many clients already , hikari pool maxsize exceeded

Question

I am trying to configure my master & read-only data sources as below,

But when I start the application it opens 6 Hikari pools as shown in the log,

Do you have an opinion on why it is creating 6 pools already?

And also another situation occurs when I run my tests,

On my test configurations, I have two application.yml's which only differentiate from the DB value.

And when I start to run all my test suites and when it hits the test file using different db configuration it starts to get the following error.

SQL State : 53300 Error Code : 0 Message : FATAL: sorry, too many clients already

How could I solve these two problems?

Thanks.

o.m.c.d.d.DataSourcesConfiguration       : Hit masterDataSource() data source
2023-02-20 10:23:05.930  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2023-02-20 10:23:05.939  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2023-02-20 10:23:06.025  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2023-02-20 10:23:06.034  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2023-02-20 10:23:06.034  INFO 40442 --- [  restartedMain] o.m.c.d.d.DataSourcesConfiguration       : Hit readonlyDataSource() data source
2023-02-20 10:23:06.034  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Starting...
2023-02-20 10:23:06.034  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-4 - Starting...
2023-02-20 10:23:06.055  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-4 - Start completed.
2023-02-20 10:23:06.058  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Start completed.
2023-02-20 10:23:06.058  INFO 40442 --- [  restartedMain] o.m.c.d.d.DataSourcesConfiguration       : Set default data source as master
2023-02-20 10:23:06.058  INFO 40442 --- [  restartedMain] o.m.c.d.d.DataSourcesConfiguration       : Hit masterDataSource() data source
2023-02-20 10:23:06.058  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-5 - Starting...
2023-02-20 10:23:06.058  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-6 - Starting...
2023-02-20 10:23:06.071  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-6 - Start completed.
2023-02-20 10:23:06.074  INFO 40442 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-5 - Start completed.

@Configuration
@ComponentScan
@Getter
@Setter
public class DataSourcesConfiguration {

    @Autowired
    DataSourceConfig dataSourceConfig;

    private int minimumIdle = 0
    private int maximumPoolSize = 4
    private int keepaliveTime = 240000


    @Bean
    public DataSource dataSource() {

        RoutingDataSource masterSlaveRoutingDataSource = new RoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.MASTER, masterDataSource());
        targetDataSources.put(DataSourceType.READONLY, readonlyDataSource());
        masterSlaveRoutingDataSource.setTargetDataSources(targetDataSources);

        log.info("Set default data source as master");
        masterSlaveRoutingDataSource.setDefaultTargetDataSource(masterDataSource());
        return masterSlaveRoutingDataSource;
    }

    @Qualifier("readonlyDataSource")
    public DataSource readonlyDataSource() {
        log.info("Hit readonlyDataSource() data source");
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setJdbcUrl(dataSourceConfig.getReadonlyUrl());
        return connectionPoolDataSource(hikariDataSource);
    }

    @Primary
    public DataSource masterDataSource() {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setJdbcUrl(dataSourceConfig.getMasterUrl());
        return connectionPoolDataSource(hikariDataSource);
    }

    
    protected HikariConfig hikariConfig(DataSource dataSource) {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setDataSource(dataSource);
        hikariConfig.setMinimumIdle(minimumIdle);
        hikariConfig.setMaximumPoolSize(maximumPoolSize);
        hikariConfig.setKeepaliveTime(keepaliveTime);
        return hikariConfig;
    }

    protected HikariDataSource connectionPoolDataSource(DataSource dataSource) {
        return new HikariDataSource(hikariConfig(dataSource));
    }

}

Answer 1

Your configuration is wrong on multiple levels.

1. Your readonlyDataSource and masterDataSource are missing an @Bean
2. The @Primary should be on your dataSource (currently it doesn't do anything as it isn't on an @Bean method)
3. Each call to either readonlyDataSource or masterDataSource creates 2 instances of a datasource. As you have no @Bean and are calling masterDataSource 2 times, this results in 3 * 2 is 6 instances.

In short fix your configuration as it is way to convoluted.

@Configuration
@ComponentScan
@Getter
@Setter
public class DataSourcesConfiguration {

    @Autowired
    DataSourceConfig dataSourceConfig;

    private int minimumIdle = 0
    private int maximumPoolSize = 4
    private int keepaliveTime = 240000

    @Bean
    @Primary
    public DataSource dataSource() {

        RoutingDataSource masterSlaveRoutingDataSource = new RoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.MASTER, masterDataSource());
        targetDataSources.put(DataSourceType.READONLY, readonlyDataSource());
        masterSlaveRoutingDataSource.setTargetDataSources(targetDataSources);

        log.info("Set default data source as master");
        masterSlaveRoutingDataSource.setDefaultTargetDataSource(masterDataSource());
        return masterSlaveRoutingDataSource;
    }

    @Bean
    public DataSource readonlyDataSource() {
        return configure(dataSourceConfig.getReadonlyUrl());
    }

    @Bean
    public DataSource masterDataSource() {
        return configure(dataSourceConfig.getMasterUrl());
    }

    
    private HikariDataSource configure(String url) {
        HikariDataSource datasource = new HikariDataSource();
        datasource.setJdbcUrl(url);
        datasource.setMinimumIdle(minimumIdle);
        datasource.setMaximumPoolSize(maximumPoolSize);
        datasource.setKeepaliveTime(keepaliveTime);
        return datasource;
    }
}

This will only create 2 instances in a much more readable way.

Another thing is, which people tend to forget, is that by default Spring Boot enables the open-entity-manager-in-view pattern. Which leads to obtaining a connection to the database very early in the process. As your masterDataSource is the default one it will always open one to that datasource.

You can disable it by setting spring.jpa.open-in-view to false in your application.properties.

spring.jpa.open-in-view=false

WARNING: Be aware that this might (or rather will) affect other parts of your application that rely (unknowingly) on lazy loading. Those won't work anymore as the EntityManager will be closed right after the transaction commits/rollbacks.