Database Connection Pool Druid usage instructions, database connection druid

Source: Internet
Author: User

Database Connection Pool Druid usage instructions, database connection druid

Replace it with the optimal database connection pool based on factors such as comprehensive performance, reliability, stability, scalability, and ease of use.

Druid: druid-1.0.29

Database Mysql.5.6.17

Replacement target: replace C3P0 with druid

Reason for replacement:

1. Performance hikariCP> druid> tomcat-jdbc> dbcp> c3p0. HikariCP's high performance benefits from avoiding lock competition to the maximum extent.

2. druid provides the most comprehensive functions, SQL interception, and other functions. It provides comprehensive statistics and good scalability.

3. In terms of overall performance and scalability, you can use druid or hikariCP connection pools to conveniently monitor and track jdbc interfaces.

4. You can enable the prepareStatement cache, which will improve the performance by about 20%.

PsCache is private to connection, so there is no thread competition problem. Enabling pscache will not cause any performance loss.

The key of psCache is the SQL statement and catalog executed by prepare, and the value corresponds to the prepareStatement object. Enabling cache mainly reduces the overhead of SQL parsing.

5. 3p0 has a long history and complicated code, which is not conducive to maintenance. And there is a potential risk of deadlock.

6. Druid can print SQL and slow query logs.

Druid Parameters

Configuration parameters Default Value Value set by the game server Parameter description
InitialSize 0 4 Initial connection count
MinIdle 0 4 Minimum number of idle connections
MaxActive 8 8 Maximum number of concurrent connections
MaxWait -1L 60000 Obtains the maximum waiting time for a connection, in milliseconds. After configuring maxWait,
The fair lock is enabled by default, and the concurrency efficiency will decrease,
If necessary, you can use an unfair lock by configuring the useUnfairLock attribute to true.
TimeBetweenEvictionRunsMillis 60000 60000 How long is the configuration interval before detecting idle connections that need to be closed, measured in milliseconds
The Destroy thread detects the connection interval.
MinEvictableIdleTimeMillis 1800000 1800000 Configure the minimum time for a connection to survive in the pool, in milliseconds
ValidationQuery Null Select 1 The SQL statement used to check whether the connection is valid must be a query statement.
TestOnBorrow FALSE FALSE When applying for a connection, run validationQuery to check whether the connection is valid. This configuration will reduce the performance.
TestOnReturn FALSE FALSE When the connection is returned, run validationQuery to check whether the connection is valid. This configuration will reduce the performance.
TestWhileIdle TRUE TRUE We recommend that you set this parameter to true without affecting performance and ensuring security. When applying for a connection, if
If the idle time is greater than timeBetweenEvictionRunsMillis, run validationQuery to check whether the connection is valid.
PoolPreparedStatements FALSE TRUE False indicates whether to cache preparedStatement, that is, PSCache.
PSCache significantly improves the performance of databases that support cursors, such as oracle.
PSCache is not available in versions earlier than mysql. We recommend that you disable it.
PSCache is available for versions 5.5 and later. We recommend that you enable it.
MaxPoolPreparedStatementPerConnectionSize 10 100 To enable PSCache, the value must be greater than 0. If the value is greater than 0,
PoolPreparedStatements is automatically triggered to change to true.
A single connnection exclusive statement cache, that is, maxOpenPreparedStatements is for a single connection

Operating principle:

The database connection pool will create initialSize connections during initialization. When there is a database operation, a connection will be retrieved from the pool. If the number of connections being used in the current pool is equal to maxActive, a connection will be released after a period of time. If the waiting time exceeds maxWait, an error will be reported; if the number of connections currently in use does not reach maxActive, it determines whether the current connection is idle. If yes, it directly Uses idle connections. If not, a new connection is created. After the connection is used up, instead of closing the physical connection, the connection is put into the pool to wait for other operations to be reused. At the same time, there is a mechanism in the connection pool to determine that if the current total number of connections is less than the miniIdle, a new idle connection will be established to ensure that the number of connections gets the miniIdle. If a connection in the current connection pool remains idle after the timeBetweenEvictionRunsMillis time, it is physically disabled. Some databases have a timeout limit (mysql is disconnected after 8 hours), or the connection pool fails due to network interruptions or other reasons, at this time, the testWhileIdle parameter is set to true, which can ensure regular detection of connection availability in the connection pool. unavailable connections will be discarded or rebuilt, ensure that the Connection object obtained from the Connection pool is available. Of course, to ensure absolute availability, you can also use testOnBorrow to be true (that is, to check the availability of the Connection object), but this will affect the performance.

To perform SQL monitoring, add the following code:

Log4j2Filter log4j2 = new Log4j2Filter(); log4j2.setResultSetLogEnabled(false); log4j2.setStatementSqlPrettyFormat(false); log4j2.setStatementExecutableSqlLogEnable(true);  log4j2.setDataSourceLogEnabled(false); log4j2.setConnectionLogEnabled(false); log4j2.setStatementLogEnabled(false); log4j2.setResultSetLogEnabled(false); ret.setProxyFilters(Arrays.asList(log4j2)); 

Idle detection, connection creation, and disconnection cleaning are managed by these three threads

Daemon Thread [Abandoned connection cleanup thread] Daemon Thread [Druid-ConnectionPool-Create-1184124073] Daemon Thread [Druid-ConnectionPool-Destroy-1184124073] 

Summary

The above is all about the Druid usage instructions for the database connection pool in this article, and I hope to help you. If you are interested, please refer to MySQL prepare principles and other related topics. If you have any questions, please feel free to leave a message.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.