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

This article mainly explains "analyzing the mechanism of MyBatis idle connection detection". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "analyze the mechanism of MyBatis idle connection detection".

Recently, there is a phenomenon that an application will not be executed until 21:00-23:00 every day, connecting to the database to perform operations, and the interval has a connection timeout error.

Connection timed out (Read failed)

Because the application and database are across network segments, after consulting, the firewall timeout is configured for 30 minutes, and the MyBatis connection pool used by the application is configured as follows.

The relevant parameters are explained as follows

The implementation of a data source such as POOLED- uses the concept of "pooling" to organize JDBC connection objects, avoiding the initialization and authentication time necessary to create new connection instances. This processing method is very popular and enables concurrent Web applications to respond to requests quickly.

In addition to the properties mentioned above under UNPOOLED, there are more properties that can be used to configure the data source for POOLED:

PoolMaximumActiveConnections-number of active (in use) connections that can exist at any time, default: 10

PoolMaximumIdleConnections-the number of idle connections that may exist at any time.

PoolMaximumCheckoutTime-time that connections are checked out (checked out) in the pool before being forced to return. Default: 20000 milliseconds (i.e. 20 seconds)

PoolTimeToWait-this is an underlying setting, and if it takes a long time to get a connection, the connection pool prints the status log and retries to obtain a connection (to avoid failing all the time in the case of misconfiguration and not printing the log). The default value is 20000 milliseconds (that is, 20 seconds).

PoolMaximumLocalBadConnectionTolerance-this is an underlying setting for bad connection tolerance that works for every thread that tries to get a connection from the cache pool. If the thread gets a bad connection, the data source allows the thread to try to reacquire a new connection, but the number of retries should not exceed the sum of poolMaximumIdleConnections and poolMaximumLocalBadConnectionTolerance. Default value: 3 (added to 3.4.5)

PoolPingQuery-A probe query sent to the database to verify that the connection is working and ready to accept the request. The default is "NO PING QUERY SET", which causes most database drivers to return appropriate error messages when they go wrong.

PoolPingEnabled-whether detect queries are enabled. If enabled, you need to set the poolPingQuery property to an executable SQL statement (preferably a very fast SQL statement). The default value is false.

PoolPingConnectionsNotUsedFor-configure the frequency of the poolPingQuery. Can be set to be the same as the database connection timeout to avoid unnecessary detection, the default value: 0 (that is, all connections are detected at every moment-of course, only if poolPingEnabled is true).

P.S.

Https://mybatis.org/mybatis-3/zh/configuration.html#environments

In this literal sense, at the beginning, we understand that the poolPingConnectionsNotUsedFor parameter controls how long the connection is unavailable, that is, it is idle. When the parameter poolPingEnabled is turned on, the SQL active probe defined by poolPingQuery will be performed.

If according to this understanding, poolPingConnectionsNotUsedFor set 3000, that is, 3 seconds, far less than the 30-minute firewall timeout setting, there should not be a connection timeout.

We suspected the configuration of the firewall, but from the application side, not all requests timed out, and the firewall side, did not see anything unusual. In the database layer, the relevant configuration should not be set.

What's the reason?

As a mature product, it is unlikely that there is a deviation in the understanding of bug because of it.

Download the source code of 3.3.0, and the link is as follows

Https://github.com/mybatis/mybatis-3/releases/tag/mybatis-3.3.0

Search the file where these parameters are located and find the PooledDataSource class. You can see that all three parameters have their initial values set.

Take a look at this pingConnection method

If the connection is not closed, the judgment logic is as follows

1. The value of poolPingConnectionsNotUsedFor > = 0

2. GetTimeElapsedSinceLastUse () > poolPingConnectionsNotUsedFor

GetTimeElapsedSinceLastUse () is defined as follows

LastUsedTimestamp is defined in the constructor PooledConnection

PooledConnection is called when getting connection (popConnection) and reclaiming connection (pushConnection), and getting connection and reclaiming connection are called by getConnection () and invoke (), so (2) means whether the current idle time of this connection is longer than that defined by this parameter poolPingConnectionsNotUsedFor.

3. If conditions (1) and (2) are met, the SQL of poolPingQuery is executed, in this case "select 1 from dual", and if execution fails, the connection is closed

You can see this information from the application log

Testing connection 0000000000... Execution of ping query 'select 1 from dual' failed: Connection timed out (Read failed)

The crux of this problem is that when this pingConnection is called, it determines when poolPingConnectionsNotUsedFor works. As you can see, it is called in this isValid method.

And this isValid is called every time it acquires and reclaims the connection. In other words, it is called passively, not actively when we think it is idle, so this application just runs at night, and it is normal to connect idle for more than 30 minutes.

When debug is enabled in the application, the interval between the two periods is the time to get the timeout connection.

After single-threaded testing, it took about 15 minutes

Therefore, the connection detection mechanism of this testOnBorrow has its own advantages and disadvantages. The advantage is that it will ensure that the normal business requests of the application will get available connections to a certain extent. After all, unavailable connections have been tested by SQL defined by poolPingQuery. In general, normal business requests will not report errors unless the connection pool does not have any available connections. The disadvantage is that if the configured poolPingConnectionsNotUsedFor is very small, some requests will be verified before execution, but from another point of view, if it is high concurrency, as long as the parameter is not 0, the conditions that need to be verified may not be met. If set to 0, there may be many SQL execution defined by pingQuery. Moreover, if a single-threaded operation like above, he will try to connect to a connection, and the interval between waiting for a connection to time out is 15 minutes, which is very inefficient.

The selection and configuration of connection pools really have to be decided according to the needs of the actual scenario.

Through this question, at least let me understand that the "self-right" mechanism is right or wrong, or depends on his implementation, this is the most reliable verification, and, through his logic, we can learn from some design paths. more consideration of the meaning and impact behind what he did will help us to use it in the right scenario.

Thank you for your reading, the above is the content of "analyzing the mechanism of MyBatis idle connection detection". After the study of this article, I believe you have a deeper understanding of analyzing the mechanism of MyBatis idle connection detection, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.