Search code examples
spring-bootspring-data-jpahikaricp

spring jdbc hikari connection pool - constantly logs on and off to database

I am using spring boot data jpa in order to connect to an Oracle database.
Spring uses HikariCP for the JDBC connection which by default creates a fixed connection pool of size 10.

The problem is that in the Oracle db logs it shows that my app constantly logging on and off from the database. I.e. almost every second there is a login and logoff request.

Extract from the oracle log which shows constant logging on / off

90% of these login/offs happen while there is not user interaction with the app which is why I am quite confused. This is an issue because it creates quite a lot of overhead on the db.

This is how I am setting up the bean for the connection:

@Repository
@Transactional(readOnly = true)
public interface OracleDao extends CrudRepository<MyOracleResource, Long> {

}

These are the application.properties related to the datasource:

spring.datasource.platform=prod
spring.jpa.database=Oracle
spring.jpa.hibernate.ddl-auto=none
spring.datasource.initialization-mode=never
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.jpa.show-sql=true
spring.datasource.url=jdbc:oracle:thin:@hostdetails...
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
spring.jpa.properties.hibernate.default_schema=schema_dbo
spring.datasource.continue-on-error=false

And finally my entry from the pom file:

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.0.RELEASE</version>
    <relativePath/>
</parent>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Thanks for the help!

Solution

  • You can change idle connection setting using idleTimeout and minimumIdle

    idleTimeout This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when minimumIdle is defined to be less than maximumPoolSize. Idle connections will not be retired once the pool reaches minimumIdle connections. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10000ms (10 seconds). Default: 600000 (10 minutes)

    Basically, decrease minimumIdle and increase idleTimeout if you have long idle times

    Also if your activity is always low, consider reducing maximumPoolSize property from its default 10 value