Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Optimization of Druid connection Pool parameters after MySQL read-write Separation

2025-02-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

Share

Shulou(Shulou.com)06/03 Report--

This article mainly talks about "how to optimize the parameters of Druid connection pool after MySQL read-write separation". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the optimization of Druid connection pool parameters after MySQL read-write separation".

Problem Review exception Log description:

From the reflection of abnormal information, there are two key points in the problem.

The database connection pool timeout setting is greater than wait_timeout

Log hint, you can avoid this exception by verifying the database connection or by setting: autoReconnect=true

It can be inferred from the above two points

First, there is a problem in setting the timeout parameter of the application database connection pool.

Second, when installing Mysql database, wait_timeout, the intrinsic parameter of Mysql, is not optimized in the actual scenario.

Problem positioning

Specific purpose of wait_timeout parameter

Wait_timeout specifically refers to the number of seconds the server waits for activity before closing a non-interactive connection. In the default configuration of MySQL, the initial value of wait_timeout is 28800 seconds, or 8 hours. If the wait_timeout timeout setting is too large, a large number of SLEEP processes in the MySQL management system will not be released in time, which will lead to server system performance degradation; at the same time, if this parameter is set too small, it will cause Mysql to deal with some transactions that have not been processed and the connection is unavailable.

That is, if the database connection Connection is in an idle waiting state during the wait_timeout setup, the connection will be automatically closed inside the mysql, and the application will not be aware of it and still think that the connection pool legally holds the connection. The above exception error occurs when the application side uses the connection for database operation again.

Application-side Druid database connection pool parameter troubleshooting

It is found that one of the MaxWait parameters in the connection pool is set too large: 60000 milliseconds.

DruidDataSource.setMaxWait (60000)

Then on CSDN, I found that a colleague encountered the same problem:

It is found that the database wait timeout (wait_timeout) is 28800s, that is, 8 hours, and the application connection pool parameter max-wait: 30000, which causes the project to determine that the link is available, and mysql determines that the connection is unavailable resulting in connection failure.

Solution.

According to the above analysis idea, we checked the Mysql production library and found that the default Mysql timeout (wait_timeout) is also 28800s, but the application layer connection pool MaxWait parameter is set to 60000, so I set the MaxWait parameter to 10000, which is less than the Mysql timeout (wait_timeout): 28800. After waiting for 8 hours in the test environment, the error report disappeared.

Other expansion ideas (source network)

Idea 1: add & autoReconnect=true after jdbc-url. It is invalid after use. The solution checked is only valid for previous versions of Mysql4.

Idea 2: it takes longer for mysql to reclaim idle connections. The default recovery time for mysql is 8 hours. You can change the time to 1 day by adding the following configuration to my.ini in the mysql directory. The unit is seconds, and the maximum seems to be 24 days. This configuration can be a drag on database performance, so deprecate this scenario.

Idea 3: configure the druid link pool, use the three attributes of validation-query test-on-borrow: true test-while-idle: true, and determine whether the connection is available each time you get a database connection. Set druidDataSource.setPhyTimeoutMillis parameters at the same time

Maximum connection lifetime. Default is-1 (no physical connection time is limited). It starts from the creation of the connection. If this time is exceeded, the druidDataSource.setPhyTimeoutMillis (15000) will be cleaned.

Reference example

For the optimization of connection pool parameters that tend to be stable in the project, please refer to the following:

Official description of Druid connection pool parameters:

At this point, I believe you have a deeper understanding of "the optimization of Druid connection pool parameters after MySQL read-write separation". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 260

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Development

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report