Instructions for using the database connection pool Druid

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

Druid:druid-1.0.29

Database Mysql.5.6.17

Replacement target: Replace C3P0 with druid

Replacement reason:

1. In terms of performance, hikariCP>druid>tomcat-jdbc>dbcp>c3p0. The high performance of hikariCP is due to the maximum avoidance of lock contention.

2. Druid has the most comprehensive functions, including SQL interception and other functions, and has relatively comprehensive statistical data and good scalability.

3. In terms of comprehensive performance and scalability, you can consider using druid or hikariCP connection pool, which is more convenient for monitoring and tracking jdbc interfaces.

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

psCache is private to the connection, so there is no thread contention problem. Enabling pscache will not cause performance loss due to contention.

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

5. 3p0 has a long history, and its code is extremely complex, 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

How it works:

The database connection pool will create initialSize connections during initialization, and when there is a database operation, a connection will be taken out of the pool. If the number of connections currently in use in the pool is equal to maxActive, it will wait for a while, waiting for other operations to release a connection. If the waiting time exceeds maxWait, an error will be reported; if the number of connections currently in use does not reach maxActive, it will determine whether there is an idle connection. If there is, the idle connection will be used directly. If not, a new connection will be established. After the connection is used, instead of closing its physical connection, it is put into the pool and waits for reuse by other operations. At the same time, there is a mechanism inside the connection pool to determine if the current total number of connections is less than miniIdle, a new idle connection will be established to ensure that the number of connections is miniIdle. If a connection in the current connection pool is still not used after being idle for timeBetweenEvictionRunsMillis time, it will be physically closed. Some database connections have timeout limits (MySQL connections are disconnected after 8 hours), or the connection pool connection may become invalid due to network interruptions and other reasons. In this case, setting a testWhileIdle parameter to true can ensure that the connection pool periodically detects the availability of connections. Unavailable connections will be discarded or rebuilt, and the Connection object obtained from the connection pool is guaranteed to be available in the best case. Of course, to ensure absolute availability, you can also use testOnBorrow to be true (that is, check the availability of the Connection object when obtaining it), but this will affect performance.

If you want to perform SQL monitoring, you can 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 abandoned connection cleanup are managed by these three threads

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

Summarize

The above is all the content of this article about the use of database connection pool Druid. I hope it will be helpful to everyone. Interested friends can refer to: Detailed Explanation of MySQL Prepare Principles and other related topics. If you have any questions, you can leave a message at any time and the editor will reply to you in time.