Configuration _me__ database for the database connection pool in spring

BONECP

BONECP configuration file Format (bonecp-config.xml):

<?xml version= "1.0" encoding= "UTF-8"?>
<bonecp-config>
  <default-config>
   <!--  -->
 <property name= "" ></property>
  </default-config> 

Second, BONECP main configuration parameters
1.jdbcUrl
Setting the database URL
2.username
Set Database user name
3.password
Set the database password
4.partitionCount
Set the number of partitions. This parameter defaults to 1, and recommendation 3-4 (depending on the specific application).
To reduce lock contention and improve performance, get a connection from the current thread partition (thread-affinity).
That's the way it is: Partitions[thread.currentthread (). GetId ()% partitioncount]. When you have sufficient short (short-lived) threads, the larger the parameter setting, the better the performance. When a certain threshold is exceeded, the maintenance of the connection pool can have a negative effect on performance (only if the connection on the partition is exhausted).
5.maxConnectionsPerPartition
Set the maximum number of connection per partition. This parameter defaults to 2. If less than 2,BONECP will be set to 50.
For example: Partitioncount set to 3,maxconnectionperpartition set to 5, you will have a total of 15 connection.
Note: BONECP will not create these connection together, but rather to increase connection quantity gradually from Minconnectionsperpartition parameters when more connection is needed.
6.minConnectionsPerPartition
Set the maximum number of connection each partition contains. This parameter defaults to 0.
7.acquireIncrement
Sets the number of connection growth in the partition. This parameter defaults to 1.
When the connection in each partition is about to run out, BONECP dynamically batch creates connection,
This property controls how many connection (not greater than maxconnectionsperpartition) are created together.
Note: This configuration belongs to the settings for each partition.
8.poolAvailabilityThreshold
Set the connection pool threshold. This parameter defaults to 20. If less than 0 or greater than 100,BONECP will be set to 20.
The Connection pool observation thread (poolwatchthread) attempts to maintain a certain number of available connection for each partition.
This number tends to be between maxconnectionperpartition and Minconnectionperpartition. This parameter is calculated as a percentage. For example: set to 20, the following conditions if set: Free Connections/maxconnections < poolavailabilitythreshold; You will create a new connection.
In other words, the connection pool maintains a minimum of 20% available connection for each partition.
When set to 0 o'clock, the connection pool will re-create the new connection whenever a connection is needed, which may cause the application to wait a little while for a new connection.
9.connectionTimeout
Sets the time that gets the connection timeout. This parameter defaults to Long.max_value; Unit: Ms.
When the call getconnection gets the connection, the fetch time exceeds this parameter and is considered timed out and reported as an exception.

three, BONECP thread configuration parameters
1.releaseHelperThreads
Sets the number of connection helper threads. This parameter defaults to 3. If less than 0,BONECP will be set to 3.
When set to 0 o'clock, the application thread is blocked until the connection pool performs the necessary cleanup and recycling of connection and makes connection available on other threads.
setting is greater than 0 o'clock, the connection pool creates an assistant thread in each partition to process the connection after the shutdown (the application will asynchronously place the connection in a temporary queue through the helper thread).
It is useful for applications to handle a large amount of work on each connection. May decrease the speed of the operation, but will improve performance in high concurrency applications.
2.statementReleaseHelperThreads
Sets the number of statement helper threads. This parameter defaults to 3. If less than 0,BONECP will be set to 3.
When set to 0 o'clock, the application thread is blocked until the connection pool or JDBC driver shuts down the statement.
When the setting is greater than 0 o'clock, the connection pool creates an attendant thread in each partition and helps the application close statement asynchronously when the application opens a large number of statement is useful. May decrease the speed of the operation, but will improve performance in high concurrency applications.
3.maxConnectionAge
Set the connection time to live. This parameter defaults to 0, in milliseconds. Set to 0 this function fails.
The connection in each partition is observed by Connectionmaxagethread, regardless of whether connection is idle, and if the connection distance is created longer than this parameter, it is cleared. The connection currently in use is not affected until it is returned to the connection pool for processing.
4.idleMaxAge
Set the idle survival time of the connection. This parameter defaults to 60, in units: minutes. Set to 0 this function fails.
The connection in each partition is observed by Connectiontesterthread, which is cleared if the connection distance is used more than this parameter.
Note: This parameter is used only with idleconnectiontestperiod, and do not set any provocative arguments here.
5.idleConnectionTestPeriod
Sets the time interval for test connection. This parameter defaults to 240, in units: minutes. Set to 0 this function fails.
By connectiontesterthread the connection in each partition, if the connection distance is longer than this parameter and the time of the last Test is greater than this parameter, a test statement is sent to the database. If the execution fails, the connection is cleared.
Note: This value is only used with idlemaxage, and do not set any provocative arguments here.

Four, BONECP optional configuration Parameters
1.acquireRetryAttempts
Set the number of times the connection was retrieved. This parameter defaults to 5.
get a connection to try to reconnect multiple times after a failure has failed and then give up if you fail several times.
2.acquireRetryDelay
Sets the number of times the connection is retrieved again. This parameter defaults to 7000, in milliseconds. If less than or equal to 0,BONECP will be set to 1000. The
gets the interval between connection attempts to acquire connection after a failure.
3.lazyInit
Sets the connection pool initialization feature. This parameter defaults to False.
set to True, the connection pool will be initialized to empty until the first connection is obtained.
4.statementsCacheSize
Sets the number of statement caches. This parameter defaults to 0. The
5.disableJMX
setting turns off the JMX feature. This parameter defaults to False.
6.poolName
Sets the connection pool name. The suffix used as the name of the Jmx and helper threads.

The

Five, BONECP Debug configuration Parameters
1.closeConnectionWatch
setting is to turn on the connection Shutdown Monitor feature. This parameter defaults to False.
whenever getconnection () is invoked, a closethreadmonitor is created to monitor whether the connection is closed or closed two times. Warning: This parameter has a great negative impact on the performance of the connection pool and is used with caution. Used only in the debug phase. The
2.closeConnectionWatchTimeout
Setting closes the connection monitor (Closethreadmonitor) for how long it lasts. This parameter defaults to 0, in milliseconds. Setting this parameter only works if the Closeconnectionwatch parameter is set to be available.
set to 0 o'clock and never close. The
3.logStatementsEnabled
setting does not turn on logging SQL statement functionality. This parameter is false by default. The
logs executed SQL to the log (including parameter values).
4.queryExecuteTimeLimit
Sets the time-out period for executing SQL. This parameter defaults to 0, in milliseconds.
When the query statement executes longer than this parameter, the execution is logged to the log.
Set to 0 o'clock, this feature fails. The
5.disableConnectionTracking
setting turns off the connection trace feature. This parameter defaults to False.
set to True, connection pooling does not monitor connection for strict shutdown; set to false to enable tracing (tracing only through mechanisms such as spring or some transaction management to ensure proper release of connection and back into the connection pool).
6.transactionRecoveryEnabled
To set the transaction playback function. This parameter defaults to False. When
is set to True, Memorizetransactionproxy can record all operations on connection and automatically replay the previous action if the connetion operation fails, and throws an exception if it fails during playback. Note: This feature will cause the connection pool to slow down very weakly. Test configuration use Case

in
sping

<bean id= "DataSource" class= "Com.jolbox.bonecp.BoneCPDataSource" destroy-method= "Close" > <propert Y name= "Driverclass" value= "Com.mysql.jdbc.Driver"/> <property name= "Jdbcurl" value= "Jdbc:mysql://localhost" : 3306/sql "/> <property name=" username "value=" root "/> <property name=" password "value=" "/&G

        T <!--set the maximum number of connection per partition--> <property name= maxconnectionsperpartition "value="/> < !--set the minimum number of connection per partition--> <property name= "minconnectionsperpartition" value= "ten"/> <!-- Number of partitions, default 2, Min 1, recommendation 3-4, depending on the application--> <property name= "Partitioncount" value= "2"/> <!--every time I get a database connection. The sex wants to take several, default value: 2--> <property name= "Acquireincrement" value= "5"/> <!--set Connection pool threshold--> ;p roperty name= "Poolavailabilitythreshold" value= "></property> <!--cache prepared statements size, default value: 0
     -->   <property name= "statementscachesize" value= "/> <!--BONECP thread configuration parameters, each partition releases the number of link assistant processes, default value: 3 Unless one of your databases is connected There is a lot of work to do, or too much assistant process will affect your performance--> <property name= "Releasehelperthreads" value= "3"/> <property Name= "Statementreleasehelperthreads" value= "3"/> <!--connection time--> <property name= "Connectiontime Out "value=" 3000 ></property> <!--idleconnectiontestperiod and idlemaxage two parameters set the time of the failed connection check to resolve the problem of database restart--&
        Gt <!--unused links in connection pool maximum survival time, unit is divided, default value: 60, if you want to live forever set to 0--> <property name= "Idlemaxage" value= "1"/> ;!
    --Check the interval between free connections in the database connection pool, in points, by default: 240, if you want to cancel, set to 0--> <property name= "Idleconnectiontestperiod" value= "1"/>
 </bean>

DBCP

    <bean id= "DataSource" class= "Org.apache.commons.dbcp.BasicDataSource" > <property name= "Driverclassnam" E "value=" Com.mysql.jdbc.Driver "></property> <property name=" username "value=" root "&GT;&LT;/PROPERTY&G
        T <property name= "Password" value= "></property> <property name=" url "value=" Jdbc:mysql://localhost:3 306/sql ></property> <!--database connection pool configuration--> <!--maxactive: The maximum number of connections that can be connected simultaneously in the pool (default is 8, adjusted to 20, peak stand-alone The device in 20 concurrency around, depending on the application scenario set)--> <property name= "maxactive" > <value>20</value> ;/property> <!--initialsize: Number of initial connections created at Connection pool startup (default is 0)--> <property name= "InitialSize" 1 "/> <!--maxwait: Maximum wait time, when no connection is available, the maximum time that the connection pool waits for the connection to be released, exceeding that time limit throws an exception if set-1 indicates infinite wait (default is no Limited, adjusted to 60000ms, to avoid the thread pool is not enough to cause the request was indefinitely suspended--> <property name= "maxwait" value= "60000"/> <! --Maxidle: ConnectionThe maximum number of idle connections in the pool, over which idle connections are freed, and when the connection is opened more quickly than it is closed, causing the number of idle in the connection pool to rise more than maxidle, causing frequent connection destruction and creation, similar to the XMX setting in JVM parameters--> 
            <property name= "Maxidle" value= "/> <!--minidle: The minimum number of idle connections in the connection pool, below which a new connection is created (the default is 0, adjusted to 5, the closer Maxidle, the better performance, because the creation and destruction of the connection, are required to consume resources, but not too large, because when the machine is idle, will also create a connection less than the number of Minidle, Similar to the XMN setting in JVM parameters)--> <property name= "Minidle" value= "3"/> <!--removeabandoned: Over re  Moveabandonedtimeout (default to False, adjusted to true)--> <property name= "removeabandoned" value= "true" after the time is spent /> <!--removeabandonedtimeout: Over time limit, recycle unused (default 300 seconds, adjusted to 180)--> <property name= "Remo Veabandonedtimeout "value=" 180/> <!--to avoid the error of connection reset, the main solution to the database restart caused by the bug in the
            When using the DBCP, if the default value is used, the connection to the database is actually an invalid database connection if it is broken for some reason, then the connection is made from the connection pool and no authentication is made.
     Therefore, in order to prevent access to the database connection failure, it is best to ensure that-->   <!--If the object is validated, check to see if the object is valid, default to False--> <property name= "Testonborrow" value= "true"/>
    --Verify that the connection is successful, and that the SQL SELECT command must return at least one row--> <property name= "validationquery" value= "SELECT 1 from Dual"/> </bean>

demo:dbcp

maxwait=3000
removeabandoned=true
removeabandonedtimeout=10

Three parameters to solve the problem of the link being indefinitely suspended c3p0

<bean id= "Targetdatasource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method= "Close" > &LT;PR Operty name= "Driverclass" > <value>${datasource.driverClassName}</value> &LT;/PROPERTY&G
        T
        <property name= "Jdbcurl" > <value>${datasource.url}</value> </property>
        <property name= "User" > <value>${datasource.username}</value> </property> <property name= "Password" > <value>${datasource.password}</value> &LT;/PROPERTY&G
        T <!--the number of connections that are c3p0 at a time when the connection in the connection pool is depleted. Default:3--> <property name= "Acquireincrement" > <value>${c3p0.acquireincrement}</v Alue> </property> <!--Two-time interval, in milliseconds. default:1000--> <property name= "Acquireretrydelay" > <value>${c3p0.acquireretrydelay}& Lt;/value> &LT;/property> <!--The default to rollback all uncommitted operations when the connection is closed. Default:false--> <property name= "Autocommitonclose" value= "false"/> <!--getting a connection failure will cause all waiting connection pools To get the attached thread to throw an exception. However, the data source remains valid and continues to attempt to get the connection the next time you call Getconnection (). If set to True, the data source will be declared disconnected and permanently closed after the attempt to acquire the connection fails. Default:false--> <property name= "Breakafteracquirefailure" > <value>${c3p0.breakaftera Cquirefailure}</value> </property> <!--When the connection pool is exhausted, the client calls getconnection () and waits for the new connection to be fetched, and the timeout is thrown Sqlex Ception, if set to 0, wait indefinitely. Unit milliseconds. Default:0. This property is best kept by default, or it will error--> <property name= "checkouttimeout" value= "0"/> <! -Test the connection by implementing Connectiontester or Queryconnectiontester classes. The class name requires a full path. Default:com.mchange.v2.c3p0.impl.DefaultConnectionTester--> <property name= "Connectiontesterc Lassname "> <value>com.mchange.v2.c3p0.impl.DefaultConnectionTester</value> </propert Y> <!--Specifies the path of the C3P0 libraries, if (and usually is) locally available then without setting, default null can be Default:null--> <property name= "Factoryclasslocation" value= "null"/> <!--strongly disrecommended. Setting this to true may leads to subtle and bizarre bugs. (original document) the author strongly recommends that a property not used--> <property name= "Forceignoreunresolvedtransactions" value= "false"/> ;! --Gets three connections when initialized, and the value should be between Minpoolsize and Maxpoolsize. Default:3--> <property name= "Initialpoolsize" > <value>${c3p0.initialpoolsize}</val Ue> </property> <!--maximum idle time, not used within 60 seconds, the connection is discarded. If 0, it will never be discarded.
        default:0--> <property name= "MaxIdleTime" > <value>${c3p0.maxIdleTime}</value> </property> <!--The maximum number of connections retained in the connection pool.
        Default:15--> <property name= "Maxpoolsize" > <value>${c3p0.maxPoolSize}</value>
            </property> <property name= "Minpoolsize" ><value>${c3p0.minPoolSize}</value> </property> <!--JDBC Standard parameters to control the Preparedstat loaded in the data source Number of ements. However, because the cached statements belong to a single connection instead of the entire connection pool. 
            So setting this parameter takes into account many factors. If both maxstatements and maxstatementsperconnection are 0, the cache is closed. default:0--> <property name= "maxstatements" > &LT;VALUE&GT;${C3P0.MAXSTATEMENTS}&LT;/VALUE&G
        T </property> <!--maxstatementsperconnection defines the maximum number of cached statements that a single connection within a connection pool has. default:0--> <property name= "maxstatementsperconnection" value= "0"/> <!--c3p0 is asynchronous operation, slow Jdb The C operation is completed by the help process. Extending these operations can effectively improve performance through multithreading to implement multiple operations at the same time. Default:3--> <property name= "Numhelperthreads" > <value>${c3p0.numhelpert Hreads}</value> </property> <!--when a user invokes getconnection (), the root user becomes the user who is going to get the connection. Used primarily when connecting a C3P0 data source to a pool connection.
    Default:null--> <property name= "Overridedefaultuser" value= "root"/>    <!--a parameter that corresponds to the Overridedefaultuser parameter. Default:null--> <property name= "Overridedefaultpassword" value= ""/> <!--user to modify system configuration parameters to wait up to 3 00 seconds.
        default:300--> <property name= "propertycycle" value= "/>" <!--choose one of the following to resolve the bug in database restart--> <!--because of the high performance, please use it only when you need it. If set to true then the validity is officers transferred Guevara for each connection submission. It is recommended that you use methods such as Idleconnectiontestperiod or automatictesttable to improve the performance of your connection tests. Default:false--> <!--<property name= "Testconnectiononcheckout" value= "true"/>--> --C3P0 will build a blank table named Test and test it with its own query statement. If this parameter is defined, then the attribute preferredtestquery is ignored. You cannot do anything on this test table, it will only be used for C3P0 testing. Default:null--> <!--<property name= "automatictesttable" value= "Test"/>--> <!--define All Test statements that are executed by the connection test. This one significantly increases the test speed when using connection tests. Note: The test table must exist at the initial data source. Default:null--> <!--<property name= "Preferredtestquery" > <value>select 1 from te 
St</value>            </property>--> <!--check for free connections in all connection pools every 60 seconds.
        default:0--> <property name= "Idleconnectiontestperiod" > <value>1</value> </property> <!--If set to true then officers transferred Guevara the validity of the connection while obtaining the connection. Default:false--> <property name= "Testconnectiononcheckin" value= "true"/> <!--definition of getting new connection from database lost The number of repeated attempts after failure. default:30--> <property name= "acquireretryattempts" > <value>${c3p0.acquireretryattemp ts}</value> </property> </bean> <bean id= "DataSource" class= "Org.springfram" Ework.jdbc.datasource.LazyConnectionDataSourceProxy "> <