Optimization and configuration of database connection 'sleep' state in Spring Boot multi-threaded application

This article discusses the problem that the MySQL database connection stays in the "sleep" state for a long time after performing a large number of data operations in Spring Boot multi-threaded applications. This phenomenon usually stems from the default behavior of connection pools (such as HikariCP) to reuse connections in order to improve performance. We will conduct an in-depth analysis of the reasons that cause connections to "sleep" and provide specific methods to optimize connection life cycle management by configuring key attributes of the HikariCP connection pool (such as `maxLifetime` and `idleTimeout`), thereby effectively solving the connection resource occupation problem and improving the overall performance of the application.

Understand multi-threaded tasks and database connection behavior

In Spring Boot applications, especially when processing scheduled tasks or asynchronous batch processing, we often use multi-threading to speed up data processing. For example, a scheduled task may start multiple threads through ThreadPoolExecutor, and each thread is responsible for inserting a large amount of data (such as 200K items) into the MySQL-compatible AWS Aurora Serverless database. Although these threads will be shut down normally after completing data insertion, in the MySQL process list, the corresponding connection may be displayed in the "sleep" state for a long time instead of being terminated immediately. Connections in this "sleeping" state will continue to occupy database resources and even affect the overall performance of the application.

The original code example shows typical Spring Boot scheduled task configuration, including:

• Use @EnableScheduling and SchedulingConfigurer to configure a ThreadPoolTaskScheduler to manage scheduled tasks.
• In the scheduled task method annotated with @Scheduled, use Executors.newFixedThreadPool to create a thread pool and submit the Runnable task.
• Wait for all subtasks to complete through executor.shutdown() and executor.awaitTermination().

Although the code ensures thread closure at the application level, the life cycle management of the database connection is the responsibility of the JDBC connection pool and is not directly determined by the termination behavior of the application thread.

Database connection pool: The role of HikariCP

Spring Boot applications use HikariCP as their database connection pool by default. The core purpose of the connection pool is to improve the efficiency and performance of database operations. Creating a database connection is a time-consuming and resource-intensive operation, and connection pooling optimizes this process in the following ways:

1. Pre-create connections: Create a certain number of database connections when the application starts and put them into the pool.
2. Connection reuse: When an application needs a database connection, "borrow" an existing connection from the pool instead of creating a new one each time.
3. Connection recycling: When the application completes the database operation, the connection is "returned" to the pool instead of closing it directly. These returned connections will remain open, waiting for the next reuse.

Therefore, when you see processes in the "sleep" state in the MySQL console, these are usually idle connections in the HikariCP connection pool. They are not actively closed by the application logic, but are retained by the connection pool for future use. If these idle connections are not used for a long time, or their life cycle is not managed properly, resources may be wasted.

Optimize HikariCP connection life cycle

In order to effectively manage these "sleeping" connections, we need to configure key properties of the HikariCP connection pool. These attributes determine the maximum survival time, maximum idle time, etc. of the connection in the pool.

Key configuration properties

HikariCP provides multiple properties to finely control the life cycle of the connection. Among them, the two most relevant to the "sleep" connection problem are:

1. spring.datasource.hikari.max-lifetime

   • Function: Defines the maximum life cycle of the connection in the pool. The maximum time between creation and destruction of a connection, regardless of whether it is active or not. Even if the connection is in use, once this lifetime is reached it will be closed and recreated when returned to the pool.
   • Importance: This property is critical to prevent network problems caused by long-term connection survival, forced disconnection of the database server, or expired credentials. It should be slightly smaller than the database server's wait_timeout (or interactive_timeout) value to ensure that HikariCP recycles and refreshes the connection before the database actively disconnects, thus avoiding SQLException.

2. spring.datasource.hikari.idle-timeout

   • Function: Defines the maximum time that a connection in the pool is allowed to be idle. If a connection remains idle in the pool for longer than this time, it will be closed and removed from the pool.
   • Importance: This attribute directly solves the problem of idle connections occupying resources for a long time. If the minimum-idle attribute is set, connections will not be removed due to idle timeout when the number of connections is lower than minimum-idle.

Configuration example

You can configure these HikariCP properties in application.properties or application.yml files:

application.properties example:

 # HikariCP connection pool configuration # Minimum number of idle connections: Ensure that there is always a certain number of idle connections in the pool to cope with burst traffic.
spring.datasource.hikari.minimum-idle=10

#Maximum connection lifetime (milliseconds):
# It is recommended to set it to a value slightly smaller than the wait_timeout value of the database server.
# For example, if MySQL's wait_timeout is 30 minutes (1800 seconds), it can be set to 29 minutes (1740000 milliseconds).
spring.datasource.hikari.max-lifetime=1740000 

#Connection idle timeout (milliseconds):
# If a connection is idle for more than this time, it will be closed and removed from the pool (provided that the number of connections is greater than minimum-idle).
# For example, set to 10 minutes (600000 milliseconds).
spring.datasource.hikari.idle-timeout=600000

#Maximum number of connections: The maximum number of connections allowed in the pool, including idle and in-use connections.
spring.datasource.hikari.maximum-pool-size=20

Configuration suggestions:

• The value of max-lifetime should be adjusted based on your database server configuration (especially wait_timeout). For example, if MySQL's wait_timeout is 30 minutes, you can set max-lifetime to 28-29 minutes to ensure that the connection is recycled by the connection pool before the database is forcefully closed.
• The value of idle-timeout can be set based on your application load pattern and tolerance for idle resource occupation. If your application intermittently processes large amounts of data, a lower idle-timeout can release idle connections faster; if the load is continuous and smooth, it can be increased appropriately to reduce the overhead of connection creation.
• minimum-idle and maximum-pool-size also need to be adjusted according to actual concurrency requirements and database carrying capacity.

Things to note and best practices

1. Database wait_timeout: Be sure to understand and consider your MySQL server's wait_timeout configuration. max-lifetime must be less than this value, otherwise it may cause the application to try to use a connection that has been closed by the database, thus throwing a SQLException.
2. Transaction management: The @Transactional annotation is mainly used to define the boundaries of transactions and ensure atomic operations. It will return the connection to the connection pool after the transaction is committed or rolled back, but the connection pool itself may still keep the connection in the "idle" state, waiting for reuse. Therefore, @Transactional cannot directly solve the life cycle problem of idle connections in the connection pool, which still needs to be managed by HikariCP configuration.
3. Resource release: Even when using connection pooling, in custom JDBC operations (if not using Spring Data JPA/JDBC templates), you should ensure that ResultSet, Statement and Connection objects are explicitly closed. Although the connection pool handles the recycling of connections, good practices can help avoid potential resource leaks.
4. Monitoring: After deploying changes, HikariCP's metrics (such as number of active connections, number of idle connections, waiting time for connections) and MySQL's process list should be closely monitored. This helps verify whether the configuration achieves the expected effect and further tune it based on actual operating conditions.
5. Container environment: In containerized deployment (such as Docker/Kubernetes) and application server environments such as Tomcat, the configuration of the connection pool is particularly important. Improper configuration can lead to resource exhaustion or performance bottlenecks.

Summarize

In Spring Boot multi-threaded applications, the MySQL connection is in the "sleep" state for a long time. This is not an error, but the normal behavior of the HikariCP connection pool to reuse connections for performance optimization. To solve the resource occupation problem caused by this, the key is to effectively manage the life cycle of the connection by finely configuring HikariCP's maxLifetime and idleTimeout attributes. By setting these parameters appropriately, we can ensure that idle connections are recycled at the appropriate time to avoid resource waste, thereby improving the overall performance and stability of the application. Be sure to set maxLifetime slightly less than the database server's wait_timeout to prevent unexpected disconnect errors.

The above is the detailed content of Optimization and configuration of database connection 'sleep' state in Spring Boot multi-threaded application. For more information, please follow other related articles on the PHP Chinese website!