seon99il

Spring

Trouble Shooting: Escape from HikariCP Deadlock

3 min read

HikariConnection Pool ERROR · Issue #144 · JeongSeonggil/SubMarket

1. Problem definition

When deploying my project to a server, I ran into an issue where certain tasks were taking too long to run. It worked fine in my local environment, but I ran into problems when running it on the server.

2. Fact gathering

The problem doesn’t always happen, but I confirmed that it does happen occasionally. It occurs intermittently even when executing the same logic.

I checked the server log and found that the DB connection timed out.

Server Log

3. Cause inference

DB performance issues

Issues caused by changing the storage of AWS Free Tier RDS from SSD to Magnetic

JPA infinite reference problem

Waiting requests caused by infinite references

Network issues between servers

Network issues between servers, because I deployed to different servers (Azure and GCP)

Connection pool problems

Found while searching for an issue

4. Write a solution and solve it

About DB performance issues

At first I was sure this was the problem, but the problem continued to occur even after changing to SSD.

About JPA infinite reference problem

I requested each service directly and checked the values, and confirmed that infinite reference does not occur.

About network issues between servers

I confirmed this by logging in the controller.

There was no problem between servers, because the log appeared immediately after the request.

About connection pool problems

On checking, I found that if there is no request to the DB for a certain period of time, the connection is disconnected.

So as long as the connection was valid, there was no problem. I also configured the connection pool in application.yml.

spring:
  datasource:
    url: jdbc:mariadb://{DB Path}?autoReconnect=true
    hikari:
      # Connection lifetime
      max-lifetime: 40000
      read-only: false
      connection-timeout: 9000
      # Max DB connection pool size : 8
      maximum-pool-size: 8
      # Test SQL connection
      connection-init-sql: SELECT 1
      # Default connection size : 4
      minimum-idle: 4
    dbcp2:
      # Check when borrowing a connection
      test-on-borrow: true
      # Validation query
      validation-query: SELECT 1
      # Test while idle
      test-while-idle: true

hikari

HikariCP is a lightweight, high-speed JDBC connection pool framework. If you’ve ever used Spring Boot, you’ve probably heard the name at least once. Spring Boot uses HikariCP for connection pool management.

  • spring.datasource.hikari.max-lifetime=40000 Sets the maximum lifetime of a connection
  • spring.datasource.hikari.maximum-pool-size: 10 Sets the maximum number of connections that can be kept in the pool, including idle and busy connections.
  • spring.datasource.hikari.minimum-idle: 4 Minimum number of connections in the CP (Connection Pool)
  • spring.datasource.hikari.connection-timeout: 9000 Maximum time to wait before getting a connection from the pool

Dbcp2

  • spring.datasource.dbcp2.test-on-borrow=true Validates before getting a connection from the pool
  • spring.datasource.dbcp2.validation-query=SELECT 1 Query to be used when checking valid connections Use the lightest one, SELECT 1
  • spring.datasource.dbcp2.test-while-idle=true Validates in the idle state

Connection pool size and performance

“The bigger the connection pool, the better?” At first glance, more seems to be advantageous. However, if the size of the connection pool is greater than the number of threads using the connections, unused connections are created and memory is wasted.

MySQL’s official reference mentions that 15–20 connections are sufficient to handle 600 users. MySQL recommends setting the maximum number of connections to unlimited and then performing a load test to find an optimized value.

Pool size setting formula

pool size = Tn * (Cm - 1) + 1 Tn: total number of threads Cm: number of connections required simultaneously in one task

If you want to know more about the size and performance of the connection pool, please refer to the following. Number Of Database Connections

Previous posts

View all posts →

Comments