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));
}
}
Your configuration is wrong on multiple levels.
readonlyDataSource and masterDataSource are missing an @Bean@Primary should be on your dataSource (currently it doesn't do anything as it isn't on an @Bean method)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.