Spring Boot Database Connection Timeout: How to Effectively Configure Connection Pools?

Spring Boot Connection Timeout Issue with Database

Issue Summary

Spring Boot applications utilizing Spring Data JPA and Hibernate with MySQL may encounter connection timeout errors. Specifically, the error message "CommunicationsException: The last packet successfully received from the server was >4<24 milliseconds ago" indicates that the connection between the application and the database has timed out.

Possible Solution Using autoReconnect

While using spring.datasource.testonborrow=true and spring.datasource.validationQuery=SELECT 1 is a viable solution, it is not recommended. A better approach involves configuring the connection pool appropriately.

Recommended Solution: Connection Pool Configuration

1. Set Maximum Number of Connections:

   • spring.datasource.max-active=10
   • This specifies the maximum number of concurrent connections allowed in the pool.

2. Set Initial and Idle Connection Limits:

   • spring.datasource.initial-size=5
   • This sets the number of initial connections in the pool.
   • spring.datasource.max-idle=5
   • This sets the maximum number of idle connections in the pool.
   • spring.datasource.min-idle=1
   • This sets the minimum number of idle connections in the pool.

3. Enable Connection Validation:

   • spring.datasource.test-while-idle=true
   • This validates connections while they are idle.
   • spring.datasource.test-on-borrow=true
   • This validates connections when they are retrieved from the pool.
   • spring.datasource.validation-query=SELECT 1
   • This sets the validation query to execute.

4. Set Validation Period and Idle Timeout:

   • spring.datasource.time-between-eviction-runs-millis=5000
   • This specifies how often to run the validation query.
   • spring.datasource.min-evictable-idle-time-millis=60000
   • This sets the maximum age of an idle connection before it is considered evictable.

By configuring the connection pool in this manner, connections are validated periodically, idle connections are purged regularly, and new connections are acquired when necessary. Spring Boot automatically handles the reconnection process, ensuring that connections are always available.

The above is the detailed content of Spring Boot Database Connection Timeout: How to Effectively Configure Connection Pools?. For more information, please follow other related articles on the PHP Chinese website!