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

Transferred from: http://agapple.iteye.com/blog/772507 recently read some dbcp-related content, by the way to make a note, lest he forget. 1. Introduction of dbcp (option 1.4) Java code com.alibaba.external jakarta.commons.dbcp 1.4

2. Basic configuration of dbcp

Related configuration instructions:

InitialSize: the number of initialization connections created when the connection pool starts (default is 0) maxActive: the maximum number of connections in the connection pool that can be connected at the same time (the default is 8, adjusted to 20, and the peak single machine is about 20 concurrent, depending on the application scenario) maxIdle: the maximum number of idle connections in the connection pool, and the excess idle connections will be released. If it is set to a negative number, there is no limit (default is 8, maxIdle cannot be too small) Because in the case of high load, the opening time of the connection is faster than the closing time, which will cause the number of idle in the connection pool to rise more than maxIdle, resulting in frequent connection destruction and creation, similar to the Xmx setting in the jvm parameter) minIdle: the minimum number of idle connections in the connection pool, below which new connections will be created (default is 0 and adjusted to 5. The closer this parameter is to maxIdle, the better the performance will be. Because the creation and destruction of connections require resource consumption. But it cannot be too large, because when the machine is idle, the number of connections less than minidle will be created, similar to the Xmn setting in the jvm parameter) maxWait: maximum wait time. When no connections are available, the connection pool waits for the connection to be released. If this time limit is exceeded, an exception will be thrown. If you set-1 to infinite wait (default is infinite, adjust to 60000ms, to avoid insufficient thread pool) PoolPreparedStatements: open the prepared of the pool (default is false, which is not adjusted. After testing, the performance of enabled is not as good as that of closed. ) maxOpenPreparedStatements: maximum number of connections at the same time after the prepared of the pool is enabled (unlimited by default, same as above, not configured) minEvictableIdleTimeMillis: the connection in the connection pool, which has been idle during the time period, is expelled from the connection pool (default is 30 minutes, which can be adjusted appropriately, and needs to be related to the policy configuration of the backend server) removeAbandonedTimeout: reclaim useless (obsolete) connections after the time limit (default is 300 seconds) Adjust to 180) removeAbandoned: whether to enter the recycling of useless connections (obsolete) after the removeAbandonedTimeout time has elapsed (default is false, adjusted to true)

RemoveAbandoned parameter explanation: if removeAbandoned is enabled, when getNumIdle ()

GetMaxActive ()-3) is triggered. For example, "removeAbandoned" can be triggered when maxActive=20, active connection is 18 and idle connection is 1. But the active connection is recycled only if the unused time exceeds "removeAbandonedTimeout": marks whether the program's stack traces log is printed when the connection is recycled (default is false, unadjusted)

There are usually several situations that require removeAbandoned: the code does not release connection in finally, but we all use sqlmapClientTemplate, and the underlying link release process encounters database deadlocks. In the past, the back-end stored procedure locked the table, which caused all the connection pools in the foreground cluster to be occupied by block, and all the subsequent business processes failed because they could not get the links.

An optimized configuration: basic configuration code "dataSource" class= "org.apache.commons.dbcp.BasicDataSource" destroy-method= "close" > "driverClassName" value= "com.mysql.jdbc.Driver" / > "url" value= "xxxx" / > "username" > xxxx "password" > xxxxx "maxActive" > 20 "initialSize" > 1 "maxWait" > 60000 "maxIdle" > 20 "minIdle" > 3 "removeAbandoned" > true "removeAbandonedTimeout" > 180 "connectionProperties" > clientEncoding=GBK

2. Dbcp's link validate configuration dbcp uses commons-pool as its connection pool management, testOnBorrow,testOnReturn and testWhileIdle are several verification mechanisms provided by pool. Call back the relevant database links (validationQuery) of dbcp through external hooks to verify dbcp-related external hook classes: PoolableConnectionFactory, inherited from common-pool PoolableObjectFactorydbcp through the entry of GenericObjectPool, borrow,return processing of connection pool testOnBorrow: as clearly suggested, that is, when borrowObject is processed Perform validateObject verification testOnReturn on the obtained connection: as suggested, it means that returnObject is performing validateObject verification on the returned connection. Personally, it is of little significance to manage the database connection pool. TestWhileIdle: focus on pool management in GenericObjectPool, a TimerTask timed thread of Evict is controlled (you can set the parameter timeBetweenEvictionRunsMillis > 0), regular validateObject check is performed on the links in the thread pool, and ensureMinIdle is called when invalid links are closed. Properly establish links to ensure a minimum number of minIdle connections. TimeBetweenEvictionRunsMillis, the time of the evict thread set (in ms) is greater than 0 before the EVET check thread is started.

ValidateQuery, which represents the sqlvalidateQueryTimeout checked, represents the number of links checked each time the check is performed through the statement setting and statement.setQueryTimeout (validationQueryTimeout) numTestsPerEvictionRun. It is recommended that the setting is as large as maxActive, so that you can effectively check all links each time. Validate configuration code "testWhileIdle" > true "testOnBorrow" > false "testOnReturn" > false "validationQuery" > select sysdate from dual "validationQueryTimeout" > 1 "timeBetweenEvictionRunsMillis" > 30000 "numTestsPerEvictionRun" > 20 related configuration requirements:

At present, most of the bottlenecks in the application of the website are still in the ISQL O, which is still at this level of the database. Each request may call about 10 SQL queries. If you do not leave the transaction, a request will get the link repeatedly. If you do validateObject every time you get the link, the performance cost is not very acceptable. It can be assumed that a SQL operation consumes 0.5 ~ 1ms (generally, this is basically the number of network requests). The frequency of abnormal network disconnection is very low, and it is generally carried out only when the database is upgraded and the exercise is maintained, and it is generally selected at night, with relatively low traffic, and there are generally personnel on duty to pay attention, so asynchronous validateObject is acceptable. However, a prerequisite is to ensure that the database can be automatically reconnected within a reasonable period of time.

Briefly introduce the validate implementation of dbcp from the code level:

1. PoolableObjectFactory provided by common-pools, management operation interface for pool pool

Java code public interface PoolableObjectFactory {Object makeObject () throws Exception; void destroyObject (Object obj) throws Exception; boolean validateObject (Object obj); void activateObject (Object obj) throws Exception; void passivateObject (Object obj) throws Exception;}

2. Pool slave pool management operation implemented by dbcp

A related validate code is posted here. For more information, please see: PoolableConnectionFactory.validateConnection ()

Java code public class PoolableConnectionFactory implements PoolableObjectFactory {. Public boolean validateObject (Object obj) {/ / verify validateObject if (obj instanceof Connection) {try {validateConnection ((Connection) obj); return true;} catch (Exception e) {return false;}} else {return false } public void validateConnection (Connection conn) throws SQLException {String query = _ validationQuery; if (conn.isClosed ()) {throw new SQLException ("validateConnection: connection closed");} if (null! = query) {Statement stmt = null; ResultSet rset = null; try {stmt = conn.createStatement () If (_ validationQueryTimeout > 0) {stmt.setQueryTimeout (_ validationQueryTimeout);} rset = stmt.executeQuery (query); if (! rset.next ()) {throw new SQLException ("validationQuery didn't return a row") }} finally {if (rset! = null) {try {rset.close () } catch (Exception t) {/ / ignored}} if (stmt! = null) {try {stmt.close () } catch (Exception t) {/ / ignored}. }

3. Evict call code of pool pool: GenericObjectPool (apache commons pool version 1.5.4)

Java code protected synchronized void startEvictor (long delay) {/ / launch Evictor is TimerTask if (null! = _ evictor) {EvictionTimer.cancel (_ evictor); _ evictor = null;} if (delay > 0) {_ evictor = new Evictor (); EvictionTimer.schedule (_ evictor, delay, delay) }} for (int iS0Magna Magnum i final ObjectTimestampPair pair; (); Boolean removeObject = false; / / Idle link handling final long idleTimeMilis = System.currentTimeMillis ()-pair.tst if ((getMinEvictableIdleTimeMillis () > 0) & & (idleTimeMilis > getMinEvictableIdleTimeMillis () {removeObject = true } else if ((getSoftMinEvictableIdleTimeMillis () > 0) & & (idleTimeMilis > getSoftMinEvictableIdleTimeMillis ()) & & ((getNumIdle () + 1) > getMinIdle ()) {removeObject = true } / / testWhileIdle sql check processing if (getTestWhileIdle () & &! removeObject) {boolean active = false; try {_ factory.activateObject (pair.value); active = true;} catch (Exception e) {removeObject=true } if (active) {if (! _ factory.validateObject (pair.value)) {removeObject=true;} else {try {_ factory.passivateObject (pair.value) } catch (Exception e) {removeObject=true } / / really close if (removeObject) {try {_ factory.destroyObject (pair.value) } catch (Exception e) {/ / ignored}}.

Note: at present, the pool implementation of dbcp uses a common apache common pools for extension processing, so with the native connection pool handling, the code looks a little awkward, feeling that automatic reconnection of this exception handling is not good, I only focus on this part of the code.

3. Link automatic heavy chain related test of dbcp

Related scenarios:

After an unexpected restart of the database, the original database connection pool can automatically discard the old useless links, and when the network of new database links is abnormally interrupted, the original established tcp links should be able to switch automatically.

Test requirements step 1

Establish a testCase code to configure the mysql database loop execution during the SQL query process to restart the mysql database abnormally

Test requirements 2 steps

Set up a testCase code to configure the mysql database loop to execute disable network links through iptables during SQL query

/ sbin/iptables-An INPUT-s 10.16.2.69-j REJECT

/ sbin/iptables-A FORWARD-p tcp-s 10.16.2.69-- dport 3306-m state-- state NEW,ESTABLISHED-j DROP

5. Iptables-F clears the rule and restores the link channel.

Test requirements problem record

Two configurations are tested, with validateObject configuration and related configuration without validateObject.

1. No validate configuration

Problem 1: after an abnormal restart of the mysql database, the link can be restored automatically, and the sql query is normal.

I tracked the code and found this problem:

When the database is shut down, pool in client gets an exception link through borrowObject and returns it to clientclient. There is an error in the sql call using the specific exception link, throwing the exception in finally and calling connection.close (). It is intended that pool should be called into the pool returned through returnObject, but when tracking the code, there is no returnObject calling GenericObjectPool to continue to check. It is found that when dbcp PoolingDataSource (implements DataSource API) calls PoolableConnection (dbcp pool-related delegate operation) to close accordingly, it will check _ conn.isClosed (). For DataSource, if isClosed returns as true, returnObject will not be called, and the link will be discarded directly.

Explanation:

It is precisely because after obtaining the abnormal link, because the _ conn.isClosed () judgment is made, the abnormal link is not returned to the connection pool, so after the database restart and recovery, every time pool is called to reconstruct a new connection, so the normal _ conn.isClosed () is safe or not, from the api description of jdk: A connection is closed if the method close has been called on it or if certain fatal errors have occurred. There are two situations, one is that the closed method is called, and the other is that it is ambiguous when some exceptions occur.

Problem 2: when validateObject is called, the validationQueryTimeout set by dbcp has no effect.

I looked at the mysql statement code implementation and found the answer.

Mysql com.mysql.jdbc.statemen partial code

Timeout time processing:

Java code timeoutTask = new CancelTask (); / / start a certain time task Connection.getCancelTimer () .schedule (timeoutTask, this.timeoutInMillis) through TimerTask

Code of the corresponding CancelTask:

Java code class CancelTask extends TimerTask {long connectionId = 0; CancelTask () throws SQLException {connectionId = connection.getIO (). GetThreadId ();} public void run () {Thread cancelThread = new Thread () {public void run () {Connection cancelConn = null; java.sql.Statement cancelStmt = null Try {cancelConn = connection.duplicate (); cancelStmt = cancelConn.createStatement (); / / simple violence, initiate another KILL SQL and close the previous sql thread id cancelStmt.execute ("KILL QUERY" + connectionId) WasCancelled = true;} catch (SQLException sqlEx) {throw new RuntimeException (sqlEx.toString ());} finally {if (cancelStmt! = null) {try {cancelStmt.close () } catch (SQLException sqlEx) {throw new RuntimeException (sqlEx.toString ()) }} if (cancelConn! = null) {try {cancelConn.close () } catch (SQLException sqlEx) {throw new RuntimeException (sqlEx.toString ());}; cancelThread.start () }}

To sum up: queryTimeout is implemented through mechanisms provided by the underlying database, such as KILL QUERY pid. If the network is blocked at this time, blocking occurs, and the corresponding kill command cannot be sent, so the timeout set by timeout has no effect.

4. Last

Finally, we decided to configure testWhileIdle scanning, mainly considering:

If the links in the pool pool are not used, you can check the links through testWhileIdle to avoid always failing after using them. You can timely prevent minEvictableIdleTimeMillis (idle links) and removeAbandoned (unreleased links) that cooperate with the connection pool, which can better avoid problems caused by some abnormal situations and prevent them from happening. For example, using some distributed database middleware, there will be idle link closing action, dynamic scaling connection pool, which needs to be found in time to avoid request failure. TestOnBorrow is not recommended to be used personally. There are performance problems. Think about what will happen to the connection. The network or server terminal has an abnormal idle link. When the network is interrupted, your testOnBorrow check finds that it is not right to take another link. If the idle link processing is abnormally closed, you can consider the retry strategy of the good business side, and configure the idle link timeout of the client, maxIdle,minIdle and so on.

-

New additions:

5.dbcp password encryption processing

In the past, the way to use jboss's jndi data source is by configuring oracle-ds.xml, you can set EncryptDBPassword, and reference the encryption configuration of jboss login-config.xml configuration.

Java code "EncryptDBPassword" > "org.jboss.resource.security.SecureIdentityLoginModule" flag= "required" > "username" > ${username} "password" > ${password_encrypt} "managedConnectionFactoryName" > jboss.jca:service=LocalTxCM,name=$ {jndiName}

In order to achieve the same effect, when switching to the spring dbcp configuration, there is also a function similar to password encryption. The run time carries on the password decode, and finally carries on the data link.

The way of implementation is very simple. Analyzing the corresponding SecureIdentityLoginModule implementation of jboss is nothing more than taking the Blowfish encryption algorithm and making a copy of it yourself.

Java code private static String encode (String secret) throws NoSuchPaddingException, NoSuchAlgorithmException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException {byte [] kbytes = "jaas is the way" .getBytes (); SecretKeySpec key = new SecretKeySpec (kbytes, "Blowfish"); Cipher cipher = Cipher.getInstance ("Blowfish"); cipher.init (Cipher.ENCRYPT_MODE, key) Byte [] encoding = cipher.doFinal (secret.getBytes ()); BigInteger n = new BigInteger (encoding); return n.toString (16);} private static char [] decode (String secret) throws NoSuchPaddingException, NoSuchAlgorithmException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException {byte [] kbytes = "jaas is the way" .getBytes () SecretKeySpec key = new SecretKeySpec (kbytes, "Blowfish"); BigInteger n = new BigInteger (secret, 16); byte [] encoding = n.toByteArray (); Cipher cipher = Cipher.getInstance ("Blowfish"); cipher.init (Cipher.DECRYPT_MODE, key); byte [] decode = cipher.doFinal (encoding); return new String (decode). ToCharArray ();}

The final configuration is replaced by:

Xml code. .

-

New additions:

6. Database reconnection mechanism

Common questions:

1. After the database restarts unexpectedly, the original database connection pool can automatically discard the old useless links and establish new database links.

two。 After the abnormal interruption of the network, the previously established tcp links should be able to switch automatically. For example, the restart of the switch in the website exercise will cause the network to break in an instant.

3. Distributed database middleware, such as amoeba, will periodically close idle links, and clients will have half-open idle links.

The general solution is:

1. Sql heartbeat test

Proactive, that is, the sql validate-related configuration that I mentioned earlier

two。 Request for mine detection

Sacrifice the ego to fulfill the spirit of the greater self. Try with the link, found that the processing failed to discard the link, mine detection requests will always fail several, that is, the problem encountered earlier is that dbcp already supports this feature and does not need additional configuration.

3. Set a reasonable timeout

Solve the half-open link. In general, database mysql,oracle has a mechanism for free links to be broken, and when you use some distributed middleware (such as software), the control of idle links will be more stringent, so setting a reasonable timeout can effectively avoid half-open links.

General timeout, dbcp is mainly minEvictableIdleTimeMillis (idle link), removeAbandonedTimeout (link leakage). You can see the previous parameter explanation.

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.