Under SpringBoot multi-data source configuration, what is the reason why database access is slow during the day and fast during the night?

Troubleshooting database performance issues under SpringBoot multi-data source configuration

This article analyzes a case where Atomikos multi-data source configuration is used in SpringBoot projects that lead to database access performance problems. The project connects to three databases (base1, base2, base3), and the base3 database access speed is extremely slow during the day and even timed out, but it returns to normal at night. Base1 and base2 databases always maintain normal access speed. The configuration, data structure and interface of the three databases are completely consistent and are only used to distinguish data from different regions. The project is a real-time large-screen kanban, which frequently requests and switches data sources.

Problem Description: The base3 database (presumably 9771db) has an intermittent delay during the day, with two accesses being about one minute apart, while the night access is normal. This is not a simple connection pool resource exhaustion problem, as resource exhaustion results in continuous performance degradation rather than intermittent delays.

Atomikos data source configuration:

<bean abstract="true" class="com.atomikos.jdbc.AtomikosDataSourceBean" destroy-method="close" id="dataSourceConfig" init-method="init">
    <property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"></property>
    <property name="poolSize" value="10"></property>
    <property name="maxPoolSize" value="20"></property>
    <property name="minPoolSize" value="5"></property>
    <property name="maxIdleTime" value="5"></property>
    <property name="maxLifetime" value="60"></property>
    <property name="borrowConnectionTimeout" value="60"></property>
    <property name="testQuery" value="SELECT 'x' from dual"></property>
    <property name="isLoad" value="${datasource.loadDB.haier}"></property>
</bean>

This configuration uses the Atomikos connection pool and sets the connection pool size, maximum number of connections, minimum number of connections, maximum idle time, maximum life cycle, connection timeout time and test query statements.

Problem analysis: The problem of intermittent delay during the day is not a simple connection pool configuration or insufficient resources. More detailed runtime data is needed to determine the root cause of the problem, such as network latency, database load, or application code issues.

Solution: It is recommended to use AOP sectional programming to record the detailed information of each database access request, including: data source name, access start time, end time, interface path, etc. By analyzing these data, performance bottlenecks can be accurately positioned and whether delays occur in the network layer, database layer, or application layer code. This will help identify whether the database is overloaded (surge in daytime visits), network connectivity issues (daily network congestion), or application code logic issues.

The above is the detailed content of Under SpringBoot multi-data source configuration, what is the reason why database access is slow during the day and fast during the night?. For more information, please follow other related articles on the PHP Chinese website!