Database Connection Pool Druid usage instructions, database connection druid

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.