SpringBoot spring.datasource Hikari Ayarları

Giriş
SpringBoot2'den itibaren Connecton pool olarak Hikari Connection Pool kullanılıyor. Hikari'nin övülen bazı özellikleri şöyle

Hikari (pronounced Hi-Kaa-lee) translates to “Light”, named after its ultra-light nature (130kb only) it was developed by Brett Wooldridge. Interesting read on how Brett Wooldridge wrote the “fastest connection pool”. Hikari is the general go-to choice in most of the companies these days, its website claims it to be better than other frameworks in several tests. Their website offers a lot of test results and different pool analyses. Here are some pros of HikariCP:

1. Designed to be deadlock-free

2.Can detect connection leak by itself.

3. Provides good defaults for all configuration properties. Yes, this is a big plus and the same cant be said for other frameworks.

4. Finds the perfect balance between not overwhelming users with a lot of configuration to a lot of functional configuration.

5. Ultra light-weight (130Kb only)

6. Great benchmark results.

7. Fewer bugs

Log Mesajları

Hikari log mesajlarını açmak için şöyle yaparız

logging.level.com.zaxxer.hikari=DEBUG

Config log mesajlarını açmak için şöyle yaparız

logging.level.com.zaxxer.hikari.HikariConfig=DEBUG

Bazı mesajlar şöyle

After cleanup stats (total=10, active=0, idle=10, waiting=0)

Pool Size

Formül şöyle

pool size = total number of threads * 
           (Number of Connections Required Simultaneously in One Task -1) + 1

connection-test-query Alanı

Örnek

Şöyle yaparız

spring:
  datasource:
    url: jdbc:oracle:thin:@${host}:${port}:${name}
    username: ${username}
    password: ${password}
    hikari:
      connection-test-query: SELECT 1 FROM DUAL
      connectionTimeout: 30000
      idleTimeout: 100000
      minimumIdle: 5
      maximum-pool-size: 10
      maxLifetime: 0
      registerMbeans: true
      leak-detection-threshold: 30000
      data-source-properties: 
        v$session.program: proc-test

connection-timeout Alanı

Örnek

Şöyle yaparız

spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.connection-timeout=600000
spring.datasource.hikari.minimum-idle=5

idle-timeout Alanı

HikariConfig log'u açıksa bazen şöyle bir çıktı görebiliriz

idleTimeout has been set but has no effect because the pool is operating

Bir bağlantının havuzda ne kadar süre boş boş bekleyeceğini gösterir. Bu süre geçerse bağlantı kapatılır

lk başlarken kaç tane connection açılacağını belirtir. Açıklaması şöyle

The default value for idleTimeout is 600000 milliseconds (10 minutes). 

Örnek

application.yml dosyasında şöyle yaparız.

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:mysql://10.70.49.75:3305/jwt?useSSL=false&allowPublicKeyRetrieval=true
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: 0523
    testWhileIdle: true
    validationQuery: SELECT 1
    hikari:
      idle-timeout: 10000

initializationFailTimeout Alanı

Örnek

Açıklaması şöyle.

The option initializationFailTimeout is set to 0, meaning if a connection cannot be obtained, the pool will start anyways.

Şöyle yaparız

  datasource:
    url: jdbc:mysql://localhost:3306/webapp
    username: root
    password: example
    driverClassName: com.mysql.cj.jdbc.Driver
    hikari:
      initializationFailTimeout: 0 

leak-detection-threshold Alanı

Açıklaması şöyle. Hikari'den alınan bir connection, belirtilen süre içinde iade edilmezse bir uyarı log mesajı basılır

This property controls the amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak. A value of 0 means leak detection is disabled. Lowest acceptable value for enabling leak detection is 2000 (2 secs). Default: 0

Çıktı şuna benzer

11:28:50.535 [warn] [Hikari Housekeeping Timer (pool HikariCP Pool 1 (small) example example.com 3306)] 

com.zaxxer.hikari.pool.LeakTask - [p:] Connection leak detection triggered for connection Connection@6494a9b8, stack trace follows

java.lang.Exception: Apparent connection leak detected

at service.JDBCConnectionService.getConnection(JDBCConnectionService.java) ~[classes/:na]

...

Örnek

Şöyle yaparız

spring:
  application:
    name: "Medium"
  datasource:
    url: jdbc:postgresql://localhost:5432/medium-db
    driver-class-name: org.postgresql.Driver
    hikari:
      username: postgres
      password: password
      maximum-pool-size: 4
      minimum-idle: 1
      leak-detection-threshold: 5000
      connection-timeout: 5000
      validation-timeout: 5000
      idle-timeout: 60000
      max-life-time: 300000

minimum-idle Alanı

İlk başlarken kaç tane connection açılacağını belirtir. Açıklaması şöyle

..., idleTimeout controls how long a connection can be idle before it is closed, and minimumIdle controls how many idle connections HikariCP will keep open at all times.

For example, if you set idleTimeout to 1 minute and minimumIdle to 10, HikariCP will keep 10 connections open at all times, even if they are not in use. If a connection has been idle for 1 minute, it will be closed and a new connection will be created to replace it.

The default value for idleTimeout is 600000 milliseconds (10 minutes). The default value for minimumIdle is the same as the maximumPoolSize value.

Örnek

spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.maximum-pool-size=5

maxLifetime Alanı

Örnek

Şöyle yaparız

## HikariCP configuration
spring.datasource.hikari.minimumIdle=0
spring.datasource.hikari.maximum-pool-size=40
spring.datasource.hikari.maxLifetime=600000

Açıklaması şöyle

The spring.datasource.hikari.maxLifetime setting instructs Hikari to retire pooled connections after 10 minutes. It’s not a very good idea to keep connections open for a very long time since a connection could be closed unexpectedly by a networking failure without the pool knowing of it.

maximum-pool-size Alanı

Açılabilecek en fazla bağlantı sayısını belirtir.

Örnek

Şöyle yaparız. 

spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.pool-name=SpringBootJPAHikariCP
spring.datasource.hikari.max-lifetime=2000000
spring.datasource.hikari.connection-timeout=30000

pool-name Alanı

Örnek

Şöyle yaparız

spring.datasource.hikari.pool-name=ps-demo

registerMBeans Alanı

JMX ile bağlantı sayısı izlenebilir.

Örnek

Şöyle yaparız. 

spring.datasource.hikari.maximumPoolSize=10
spring.datasource.hikari.registerMbeans=true

transaction-isolation Alanı

Örnek

Şöyle yaparız. Burada 

1. MySQL'e mahsus server side prepared statement useServerPrepStmts = false yüzünden kullanılmıyor.

2. cachePrepStmts=true ile prepared statement cache özelliği etkinleştiriliyor.

3. prepStmtCacheSize = 500 ile cachelenecek prepared statement sayısı belirtiliyor

4. Normalde prepared statement içindeki SQL cümlesi uzunluğu 256 karaktere kadar olanlar cachelenir.  prepStmtCacheSqlLimit=1024 ile SQL cümlesi uzunluğu artırılıyor

## HikariCP configuration
spring.datasource.hikari.minimumIdle=0
spring.datasource.hikari.maximum-pool-size=40
spring.datasource.hikari.maxLifetime=600000
spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_COMMITTED
spring.datasource.hikari.auto-commit=false
spring.datasource.hikari.data-source-properties.useServerPrepStmts=false
spring.datasource.hikari.data-source-properties.cachePrepStmts=true
spring.datasource.hikari.data-source-properties.prepStmtCacheSize=500
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=1024

Açıklaması şöyle

The default isolation level is set to READ_COMMITTED to optimize the number of gap locks held by MySQL when traversing the clustered index for bulk updates or deletes.

The auto-commit mode is disabled, and we are going to let Hibernate know about this via the hibernate.connection.provider_disables_autocommit setting. This way, Hibernate can acquire the database connection lazily right before executing a query or prior to flushing the Persistence Context, as opposed to the default behavior, which makes Hibernate acquire the connection right when entering a @Transactional method.