Should know database connection pool DBCP configuration and JDBC Timeout settings __ Database

One night on the eve of 2014 618 the SQL execution times for a system was wrong:

<!--add Sync data-->
<insert id= "Insert" parameterclass= "order" >
  INSERT INTO AA (id,orderid,createdate )
  VALUES
  (seq. Nextval, #orderId #, #createDate #)
  <selectkey resultclass= "Java.lang.Long" >
    SELECT seq. Currval from DUAL
  </selectKey>
</insert>

Will throw more than 800 of the following errors

Caused by:java.sql.sqlexception:ora-01013: The user requested to cancel the current operation at
Oracle.jdbc.driver.DatabaseError.throwSqlException ( databaseerror.java:112) at
oracle.jdbc.driver.T4CTTIoer.processError (t4cttioer.java:331)
at Oracle.jdbc.driver.T4CTTIoer.processError (t4cttioer.java:288) at
oracle.jdbc.driver.T4C8Oall.receive ( t4c8oall.java:745) at
Oracle.jdbc.driver.T4CPreparedStatement.doOall8 (t4cpreparedstatement.java:219)
At Oracle.jdbc.driver.T4CPreparedStatement.executeForRows (t4cpreparedstatement.java:970)

The reason is that the SQL execution time is too long and the JDBC driver proactively cancels the operation.

Recommendations:

1, look at the average execution time of the SQL, set a timeout for the SQL. (Execution time is too long to occupy the connection, causing others to not get the connection)

2, to find a DBA consulting there is no way to optimize the SQL, for example, can be inserted in parallel. Or you can do zoning.

1. Data Source Configuration

If you are using Apache DBCP and you may experience bottlenecks in the number of connections, you can adjust the following configuration:

<!-recommends the following values as much as possible, there is no need to frequent expired idle connections (unless, for example, connection pooling resources are scarce, consider)-->
<property name= "Maxidle" value= "/>"
<property name= "Minidle" value= "/>"
<property name= "initialsize" value= "/>"
<property name= "maxactive" value= "/>"

<!-This is waiting to get connection pool connection time, also not too large, such as set in 500 milliseconds-->
<property name= "maxwait" value= "/>"

<!--Remove unreferenced connections (those that do not have close) set to false here, you need to guarantee that the connection in your program will be released-->

<property name= "removeabandoned" value= "false" ></property>
<property name= "Removeabandonedtimeout" value= "300000" ></property>
<!--How long a connection is free to remove from the pool, no judgment here-->
<property name= "Minevictableidletimemillis" value= "-1"/>
< How many times does the cyclic test!--expire (0 is equivalent to closing the timer)-->
<property name= "Numtestsperevictionrun" value= "0"/>
<!--expire connection timer cycle-->
<property name= "Timebetweenevictionrunsmillis" value= "120000"/>

<!--when the connection is free to test, that is, keep the connection has been alive, with expire connection timer-->

<property name= "Testwhileidle" value= "false" ></property>

If the MySQL library, there may be a 8-hour problem, you can consider opening the expiration timer (numtestsperevictionrun=1), a regular expiration of the connection, Timebetweenevictionrunsmillis time can be set at about 8 hours.

You can also configure the socket connection/read timeout by configuring the following:

<property name= "ConnectionProperties"

Value= "oracle.net.connect_timeout=2000;oracle.jdbc.readtimeout=2000" ></property>

(The connection and read timeout here, consider the size according to your business)

More configuration can refer to http://www.importnew.com/2466.html

2, Ibatis configuration

* * The project uses the Ibatis-sqlmap-2.3.4.726.jar version, and from 2.3.1:

O removed maxtransactions, maxrequests, maxSessions from configuration, all are now controlled by the resource providers. (that is, removed maxtransactions, maxrequests, maxsessions configuration)

So we only need to configure the following:

<settings cachemodelsenabled= "false" enhancementenabled= "true"

Lazyloadingenabled= "false" errortracingenabled= "true" maxrequests= "32"

defaultstatementtimeout= "2"/>

The defaultstatementtimeout unit is seconds, depending on the business configuration.

If you want to set the timeout for only one statement, consider: <insert ... timeout= "2" >

The front line reported the following error, because statement execution timed out.

CAUSE:JAVA.SQL.SQLEXCEPTION:ORA-01013: User requests to cancel the current operation

3. Spring Transaction Manager Configuration

Provides a global transaction-level timeout:

<bean id= "Oracletransactionmanager" class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" >

<property name= "DataSource" ref= "Oracledatasource"/>

<property name= "DefaultTimeout" value= "2"/>

</bean>

Summary:

The timeout settings are mainly as follows:

1. Connection Timeout

2, read data timeout

3, Statement timeout

4, transaction-level timeout =n* statement timeout + GC pause time

Before summarizing some of the issues of transaction timeout, interested may refer to the following:

http://jinnianshilongnian.iteye.com/blog/1986023

Http://www.importnew.com/2466.html

A point to note for another database connection pool:

<bean id= "Msqldatasource" class= "Org.apache.commons.dbcp.BasicDataSource" destroy-method= "Close" >

If the Destroy-method is not added, and the number of reboots is too frequent, causing the reboot tomcat old database connection pool connection is not released, so there will be a lot of database connections for a period of time does not release, so it is best to add Destroy-method.

Mess///////////////////////

When the maximum number of connections is exceeded, the connection is deleted.

if (config!= null && config.getremoveabandoned () && (Getnumidle () < 2) && (Getnumactive () > Getmaxactive ()-3)) {

Removeabandoned ();

}

The function of this code is to disable orphaned connections, that is, someone gets the connection but does not have close.

1, network congestion/instability when the cascade effect (such as I now write ssdb-client in the network failure (network is not available) I will set a time, in this time the request all tiemout)

The internal connection pool should be based on the current network status (such as too many timeouts), for a certain period of time (such as 100ms) all timeout, do not carry out await (maxwait).

Another is the number of people waiting to connect to the pool, such as now waiting for 1000, then the next wait is meaningless, it will also cause a snowball (Ssdb-client adopted this strategy).

2. The wait timeout should be as small as possible (unless it is necessary), even if the error page is returned, it is stronger than waiting.

DBCP is more prone to the problem is set the timeout time is too long, resulting in a large number of timed_wait, thread blocking, and is a snowball, once the problem is difficult to respond immediately, and this can be resolved by [1] said.

Most database client will have a function to cancel statement execution (that is, if we set querytimeout=2 seconds and if no information is returned within 2 seconds, then a task will take the initiative to send a canceled SQL to cancel the current statement execution).

1, MySQL each connection will create a timer (each timer will create a thread)

2, each create a statement will submit a timertask (each task will create a thread when executing)

That is to say, suppose we have 500 connection pools, each of which performs 1 statement and, at worst, creates:

500*1+500*1=1000 a thread.

Assuming that there are three MySQL libraries in an application, the worst case scenario is:

1000*3=3000 created by a thread.

If our database uses a separate table or read-write separation, it is conceivable. At a time of great stress.

If the OS is not particularly fast for thread release, the cancel thread may not be immediately available (I'm not sure, the familiar classmate can explain).

Oracle uses a different strategy:

1, each classloader a watchdog thread (similar to the MySQL timer);

2. Each statement a task, and the thread is triggered when the watchdog needs to be canceled, that is, when watchdog discovers that the statement needs cancel, it invokes a method that quickly creates a thread and runs it;

That is to say, suppose we have 500 connection pools, each of which performs 1 statement and, at worst, creates:

1+500*1=501 a thread.

Assuming that there are three MySQL libraries in an application, the worst case scenario is:

1 + 500*3=1501 threads created.

Solution:

1, the best solution is to change the MySQL implementation.

2. Modify the number of threads supported by the underlying system.

Mess///////////////////////

In addition DBCP 1.x uses Commons-pool 1.x, high concurrent performance is not very good; Consider upgrading 2.x or if new projects can be considered for use with Druid or Proxool, the old project is still a prudent migration (before I migrated is no problem, but still cautious).

Original: http://mp.weixin.qq.com/s?__biz=MzIwODA4NjMwNA==&mid=2652897787&idx=1&sn= 2725ee6a029901e026785522772f6c6b#rd