Spring Database Load Test: How to release connections

Question

I was just trying to simplify my Problem in a little Show-Case-Application.
It seems like the Connection doesn't get closed until the Thread finished. Check this out

Dependencies Information:

Servlet Engine: Apache Tomcat/8.5.5
JPA Provider: Hibernate

My pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>...</groupId>
    <artifactId>***-service</artifactId>
    <version>0.0.1</version>
    <packaging>jar</packaging>

    <name>...</name>
    <description>...</description>

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

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <spring.cloud.release.version>Brixton.SR2</spring.cloud.release.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-rest</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- eureka discovery client -->
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-eureka</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- for unit tests -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- for rest documentation -->
        <dependency>
            <groupId>org.springframework.restdocs</groupId>
            <artifactId>spring-restdocs-mockmvc</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- for (un)marshalling of json / xml -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.dataformat</groupId>
            <artifactId>jackson-dataformat-xml</artifactId>
        </dependency>

        <dependency>
            <!-- jsoup HTML parser library @ http://jsoup.org/ -->
            <groupId>org.jsoup</groupId>
            <artifactId>jsoup</artifactId>
            <version>1.9.2</version>
        </dependency>

    </dependencies>

    <dependencyManagement>
        <dependencies>
            <!-- also needed for eureka discovery -->
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring.cloud.release.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <finalName>${artifactId}</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <executable>true</executable>
                    <outputDirectory>${dir}</outputDirectory>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

I have a Spring Boot Server with MYSQL as DB. When I am Load-Testing (JMeter) with more than 100 Threads/Users, I get this Exception:

org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-nio-8015-exec-195] Timeout: Pool empty. Unable to fetch a connection in 60 seconds, none available[size:50; busy:50; idle:0; lastwait:60000].

My Datasource Configuration:

spring.datasource.tomcat.minIdle = 0
spring.datasource.tomcat.maxIdle = 10
spring.datasource.tomcat.maxActive = 50
spring.datasource.tomcat.maxWait = 60000
spring.datasource.tomcat.testOnBorrow = true
spring.datasource.tomcat.timeBetweenEvictionRunsMillis = 1800000 // Updated
spring.datasource.tomcat.numTestsPerEvictionRun = 50  // Updated
spring.datasource.tomcat.minEvictableIdleTimeMillis = 10
spring.datasource.tomcat.validationQuery = SELECT 1
spring.datasource.tomcat.testWhileIdle = true

The only query on the Database is a simple SELECT * FROM TABLE WHERE deletedat IS NULL. It returns about 4000 Entries in about 0.01 seconds (via mysql CLI).
I have installed Neor Profile SQL to Profile my MYSQL DB. When I check all processes, the processes on my Schema are all Sleeping. It seems like my Configuration doesn't release those Connections.

I am using a simple Interface which extends from CrudRepository:

public interface MyRepository extends CrudRepository<MyModel, String> {
    @Query("SELECT m from MyModel m WHERE m.deletedAt IS NULL")
    List<MyModel> findWhereDeletedAtIsNull();
}

and use it

List<MyModel> myModelList = myRepository.findWhereDeletedAtIsNull();

What am I missing?
It seems that either the Configuration or the Code needs to be adapted in order to release connections.

Hibernate Config:

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect  

Answer 1

Your jpa configuration registering the OpenEntityManagerInViewInterceptor which binds a JPA EntityManager to the worker thread for the entire processing of the request. Hence database connection are getting closed(or returned to pool) after finishing of service method (com.example.api.UserService.getAllUser()).

This keeps your DB connection open for longer time and your connection pool is getting exhausted during load.

To fix your issue you can disable the auto registration of OpenEntityManagerInViewInterceptor filter by adding below property in your application.properties

spring.jpa.open-in-view=false

This property is true by default.

See related issues: 1. Consider not registering OpenEntityManagerInViewInterceptor by default

Answer 2

What is critical, here, is to enable logAbandoned. That way, Tomcat's JDBC pool will tell you where the connections are being allocated that aren't being returned. As of now, you are merely trying to recover from the problem by covering it up: forcing the pool to release connections that haven't been properly-returned.

The java.sql.SQLException: Connection has already been closed is happening because you have a query that is taking longer than 50 seconds to complete, but it is being properly cleaned-up by your own code. So after 50 seconds, the pool closes the connection (because it's been "abandoned") and then when your code gets control back, it also closes the connection. Consider raising your abandoned-connection timeout.