Configuring hikari connection pool

user_504717 0
Viewed 931

I have following settings for my database (I have multiple databases, so they are configured in spring.datasource hierarchy.

spring:
  datasource:
    db-write:
      url: jdbc:sqlserver://whatever.database.windows.net:1433;database=dbname;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
      username: 'myusername'
      password: 'mynotsosecretpassword'
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

Then I am configuring my datasource here

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.datasources.dbwrite.repository",
        entityManagerFactoryRef = "entityManagerFactoryDbWrite",
        transactionManagerRef= "transactionManagerDbWrite"
)
public class DataSourceConfigurationDbWrite {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.db-write")
    public DataSourceProperties dataSourcePropertiesDbWrite() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.db-write.configuration")
    public DataSource dataSourceDbWrite() {
        return dataSourcePropertiesDbWrite().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Primary
    @Bean(name = "entityManagerFactoryDbWrite")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryDbWrite(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(dataSourceDbWrite())
                .packages("com.datasources.dbwrite.models")
                .build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager transactionManagerDbWrite(
            final @Qualifier("entityManagerFactoryDbWrite") LocalContainerEntityManagerFactoryBean entityManagerFactoryDbWrite) {
        return new JpaTransactionManager(Objects.requireNonNull(entityManagerFactoryDbWrite.getObject()));
    }
}

I am configuring my hikari datasource in dataSourceDbWrite method based on the properties i read in dataSourcePropertiesDbWrite method. I believe i need to configure properties in specific hierarchy so that dataSourceDbWrite method can easily detect which properties are needed for hikari. Is that correct?

What that hierarchy would be?

Moreover, how can and where can i find what properties i can configure for hikari? connection-timeout? connection pool size etc?

hikaricp java-11 spring-boot azure-sql-database java
2 Answers

Me personally prefer application.yml than code to configurate Hikari:

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: {JDBC URL}
    username: {USERNAME}
    password: {PASSWORD}
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      minimum-idle: 5
      idle-timeout: 600000
      maximum-pool-size: 10
      auto-commit: true
      pool-name: HikariCorePool
      max-lifetime: 1800000
      connection-timeout: 30000
      connection-test-query: select * from information_schema.tables limit 1

(BTW, that piece of configuration was originally writen by a colleague years ago. We didn't change it and just copy-and-paste into any new projects those years.)

If you want to check out all configurable fields, those spring.datasource.hikari.* keys inorg.springframework.boot:spring-boot-autoconfigure:{VERSION}/META-INF/spring/spring-configuration-metadata.json may could help.

And javadoc in com.zaxxer.hikari.HikariConfigMXBean could help too.

user_99077510

See example in article, the properties hierarchy are according to @ConfigurationProperties's value

If we want to configure Hikari, we just need to add a @ConfigurationProperties to the data source definition:

@Bean
@ConfigurationProperties("spring.datasource.todos.hikari")
public DataSource todosDataSource() {
     return todosDataSourceProperties()
       .initializeDataSourceBuilder()
       .build();
}

Then, we can insert the following lines into the application.properties file:

spring.datasource.todos.hikari.connectionTimeout=30000 
spring.datasource.todos.hikari.idleTimeout=600000 
spring.datasource.todos.hikari.maxLifetime=1800000

See relevant hikari's spring properties

spring.datasource.hikari.connection-timeout
spring.datasource.hikari.data-source-class-name
spring.datasource.hikari.data-source-properties
spring.datasource.hikari.driver-class-name
spring.datasource.hikari.idle-timeout
spring.datasource.hikari.initialization-fail-timeout
spring.datasource.hikari.jdbc-url
spring.datasource.hikari.leak-detection-threshold
spring.datasource.hikari.login-timeout
spring.datasource.hikari.max-lifetime
spring.datasource.hikari.maximum-pool-size
spring.datasource.hikari.minimum-idle
spring.datasource.hikari.validation-timeout

And explanation on each property in HikariCP, for example

connectionTimeout This property controls the maximum number of milliseconds that a client (that's you) will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. Lowest acceptable connection timeout is 250 ms. Default: 30000 (30 seconds)

Notice that camelCase hikari properties (connectionTimeout) is shown as snake-case in spring (connection-timeout)

user_72949000
Related