Java Framework database Connection pool comparison (C3P0,DBCP and Proxool)
Article Category: Java programming
Now common open source data connection pool mainly has C3P0,DBCP and proxool three kinds, among them:
¨hibernate Development Group recommended the use of c3p0;
¨spring Development Group recommended the use of DBCP (DBCP connection pool has WebLogic connection pool The same problem is forced to close the connection or the database restart, cannot reconnect, tell the connection is reset, this setting can be resolved);
¨hibernate in action recommends the use of c3p0 and proxool;
The following is a detailed description of each connection pool survey results:
1. apache-dbcp
? Basicdatasource Related parameter description
DataSource: DataSource to connect (usually we don't define in Server.xml)
Defaultautocommit: The default value is true for transactions that are autocommit
Defaultreadonly: The default value is false for database read only
Driverclassname: The JDBC Driver Class used to connect to the database,
Maxactive: Maximum number of objects that can be fetched from the object pool, 0 means no limit, default is 8
Maxidle: Maximum number of waiting connections, set 0 to No limit (the maximum number of objects in the object pool)
Minidle: Minimum number of objects in the object pool
Maxwait: Maximum wait seconds, in MS, over time will throw error message
Password: Password used to log in to the database
URL: The URL of the connection database
Username: Account number used to log into the database
Validationquery: Verify that the connection is successful and that the SQL SELECT directive returns at least one row
Removeabandoned: Is self-interrupting, default is False
Removeabandonedtimeout: The removeabandoned must be true after a few seconds of self-interruption
Logabandoned: If an interrupt event is logged, the default is False
Minevictableidletimemillis: Greater than 0, the connection idle time is judged, or 0, the idle connection is not verified; default 30 minutes
Timebetweenevictionrunsmillis: Failure Check thread run interval, if less than equals 0, does not start check thread, Default-1
Testonborrow: Whether to validate when an object is obtained, check if the object is valid, default to False
Testonreturn: Whether to validate when returning an object, check if the object is valid, default to False
Testwhileidle: Whether to validate when idle, check if the object is valid, default to False
? When using the DBCP, if the default value is used, the connection to the database connection is actually invalid when it is disconnected from the connection pool for some reason and the connection is not validated. Therefore, in order to prevent the access to the database connection failure, it is best to ensure that:
Username: Account number used to log into the database
Validationquery:select COUNT (*) from DUAL
Testonborrow, Testonreturn, Testwhileidle: Best set to True
Minevictableidletimemillis: Greater than 0, the connection idle time is judged, or 0, the idle connection is not verified
Timebetweenevictionrunsmillis: Failure Check thread run time interval, if less than equals 0, check thread does not start
? PS: When constructing Genericobjectpool [Basicdatasource also uses Genericobjectpool] in its CreateDataSource () method, an inline class evictor is generated, Implement the self-runnable interface. If Timebetweenevictionrunsmillis is greater than 0, every timebetweenevictionrunsmillis milliseconds Evictor calls the evict () method to check if the object is idle longer than Minevictableidletimemillis milliseconds (_minevictableidletimemillis is less than or equal to 0 o'clock is ignored, the default is 30 minutes), the object is destroyed, otherwise the object is activated and validated, Then call the Ensureminidle method to check to make sure that the number of objects in the pool is not less than _minidle. When you call the Returnobject method to put the object back into the object pool, first check that the object is valid, and then call Poolableobjectfactory's Passivateobject method to make the object inactive. If the number of objects in the object pool is less than maxidle, then you can put this object back into the object pool or destroy the object
? The setting of the above features has been verified in the code, whether the specific performance can be implemented to verify the actual
2. c3p0
? The data source used in C3P0 's official example is Combopooleddatasource, an online article detailing the meanings of the C3P0 connection pool configuration [The meaning of these configuration items after downloading the compressed package of the decompression c3p0 the directory doc\ Index.html in the configuration section also has a detailed introduction, here steal lazy: P], now excerpt as follows:
<c3p0-config>
<default-config>
<!--c3p0 The number of connections that are fetched at the same time when the connection in the connection pool is exhausted. Default:3--
<property name= "Acquireincrement" >3</property>
<!--defines the number of repeated attempts to obtain a new connection from the database after a failure. Default:30--
<property name= "Acquireretryattempts" >30</property>
<!--the interval between two connections, in milliseconds. default:1000--
<property name= "Acquireretrydelay" >1000</property>
< All uncommitted actions are rolled back by default when the connection is closed!--. Default:false--
<property name= "Autocommitonclose" >false</property>
<!--C3P0 will build a blank table named Test and test it with its own query statement. If this parameter is defined, then
Property Preferredtestquery will be ignored. You cannot do anything on this test sheet, it will be for C3P0 testing only
Use. Default:null-->
<property name= "Automatictesttable" >Test</property>
<!--getting a connection failure will cause any thread that waits for the connection pool to get the connection to throw an exception. But the data source is still valid
Reserved and continue trying to get the connection the next time you call Getconnection (). If set to true, then try to
When a connection failure is obtained, the data source declares that it has been fractured and closed permanently. Default:false-->
<property name= "Breakafteracquirefailure" >false</property>
<!--the time when the client calls getconnection () after the connection pool has run out, and then throws
SQLException, if set to 0, waits indefinitely. Unit milliseconds. Default:0--
<property name= "Checkouttimeout" >100</property>
<!--test the connection by implementing a class of Connectiontester or Queryconnectiontester. The class name needs to be set to the full path.
Default:com.mchange.v2.c3p0.impl.defaultconnectiontester-->
<property name= "Connectiontesterclassname" ></property>
<!--Specify the path to the C3P0 libraries, if (as is often the case) you can get it locally without setting, default null
Default:null-->
<property name= "Factoryclasslocation" >null</property>
<!--strongly disrecommended. Setting this to true may leads to subtle and bizarre bugs.
Author strongly recommends not to use a property--
<property name= "Forceignoreunresolvedtransactions" >false</property>
<!--check for idle connections in all connection pools every 60 seconds. Default:0--
<property name= "Idleconnectiontestperiod" >60</property>
<!--get three connections when initializing, the value should be between Minpoolsize and Maxpoolsize. Default:3--
<property name= "Initialpoolsize" >3</property>
<!--maximum idle time, unused in 60 seconds, the connection is discarded. If 0, it will never be discarded. Default:0--
<property name= "MaxIdleTime" >60</property>
<!--The maximum number of connections that are kept in the connection pool. Default:15--
<property name= "Maxpoolsize" >15</property>
<!--the standard parameters of JDBC to control the number of preparedstatements loaded within the data source. But due to the statements of the pre-cache
belong to a single connection instead of the entire connection pool. So setting this parameter takes into account a variety of factors.
If both maxstatements and maxstatementsperconnection are 0, the cache is closed. Default:0-->
<property name= "Maxstatements" >100</property>
<!--maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. Default:0--
<property name= "Maxstatementsperconnection" ></property>
<!--C3P0 is asynchronous, and slow JDBC operations are done through the help process. Extending these operations can effectively improve performance
Multiple operations are performed at the same time through multithreading. Default:3-->
<property name= "Numhelperthreads" >3</property>
<!--when a user calls getconnection () to make the root user the user who is going to get the connection. Primarily for connection pooling connections non-C3P0
Data source. Default:null-->
<property name= "Overridedefaultuser" >root</property>
<!--a parameter that corresponds to the Overridedefaultuser parameter. Default:null-->
<property name= "Overridedefaultpassword" >password</property>
<!--password. Default:null-->
<property name= "Password" ></property>
<!--define test statements that are executed by all connection tests. This one significantly improves the test speed in the case of connection testing. Attention:
The test table must exist at the time of the initial data source. Default:null-->
<property name= "preferredtestquery" >select ID from test where id=1</property>
<!--the user to wait up to 300 seconds before modifying the system configuration parameters. DEFAULT:300--
<property name= "Propertycycle" >300</property>
<!--use it only when you need it, due to its high performance consumption. If set to true then each connection commits the
Officer the validity of the test. It is recommended to use Idleconnectiontestperiod or automatictesttable
and other methods to improve the performance of the connection test. Default:false--
<property name= "Testconnectiononcheckout" >false</property>
<!--If set to true then officer the validity of the connection while the connection is made. Default:false--
<property name= "Testconnectiononcheckin" >true</property>
<!--user name. Default:null-->
<property name= "User" >root</property>
<!--earlier versions of C3P0 use dynamic reflection proxies for JDBC interfaces. In the case of a wide range of previous versions, this parameter
Allows the user to revert to the dynamic reflection agent to resolve an unstable failure. The newest non-reflection agent is faster and has started
Widely used, so this parameter may not be useful. Now the original dynamic reflection and the new non-reflection agent are
Supported, but possible future versions may not support dynamic reflection agents. Default:false-->
<property name= "Usestraditionalreflectiveproxies" >false</property>
<property name= "Automatictesttable" >con_test</property>
<property name= "Checkouttimeout" >30000</property>
<property name= "Idleconnectiontestperiod" >30</property>
<property name= "Initialpoolsize" >10</property>
<property name= "MaxIdleTime" >30</property>
<property name= "Maxpoolsize" >25</property>
<property name= "Minpoolsize" >10</property>
<property name= "Maxstatements" >0</property>
<user-overrides user= "Swaldman" >
</user-overrides>
</default-config>
<named-config name= "Dumbtestconfig" >
<property name= "Maxstatements" >200</property>
<user-overrides user= "Poop" >
<property name= "Maxstatements" >300</property>
</user-overrides>
</named-config>
</c3p0-config>
? The setting of the above features has been verified in the code, whether the specific performance can be implemented to verify the actual
? From the content of the configuration items, both C3P0 and DBCP have a more detailed configuration of the connection detection guarantee, and we can see that C3P0 can control the number of preparedstatements loaded in the data source, and can set the number of helper threads to increase the speed of the JDBC operation. These are not provided by DBCP; In addition, from the network evaluation, DBCP the frequency of the occurrence of bugs more than C3P0, but this is to our own actual detection.
3. Proxool
? The use of Proxool is slightly different from dbcp and c3p0, and we need to load the Org.logicalcobwebs.proxool.ProxoolDriver driver class only before using the basic Java.sql.DriverManager , and follow the URL format defined by Proxool ["Proxool." + Alias + ":" + Driverclass + ":" + Driverurl, where alias is a custom alias for the connection pool] to get connection; specifically, see P Roxool Doc Userguide, or the sample code attached to this article. The following is a detailed description of the connection pool configuration [This is the translation of their own, not necessarily accurate, if there is a problem, please refer to the properties under Doc ~].
N fatal-sql-exception
A comma-separated list of exceptions that, when set, will be compared to the list of exception items whenever sqlexception occurs, and if the match is considered to be fatal exceptions, this will cause connection to be discarded. And no matter what happens, the exception will be re-thrown one time to inform the user of what happened. Default value is null
N Fatal-sql-exception-wrapper-class
If Fatal-sql-exception is configured, the default action is to discard the cause of the sqlexception and just throw the original exception. Using the Fatal-sql-exception-wrapper-class feature, you can wrap SqlException into any exception class that inherits SqlException or RuntimeException. Proxool provides two classes for use with fatalsqlexception and fatalruntimeexception, and if you use these two classes, set this option to ' Org.logicalcobwebs.proxool.FatalSQLException ' or ' org.logicalcobwebs.proxool.FatalRuntimeException '. Default value is null
N House-keeping-sleep-time
Proxool automatically detects the time interval (in milliseconds) of each connection state, detects the idle connection and immediately reclaims it, and destroys the timeout, with a default value of 30 seconds
N House-keeping-test-sql
If the Scout thread finds an idle connection, the SQL statement is used to check these connections, and the statement for this setting should be able to be executed very quickly, such as querying the current time [Info.setproperty ("Proxool.house-keeping-test-sql "," select Current_date ");]. If not set then this option is ignored
n Injectable-connection-interface, Injectable-statement-interface, Injectable-prepared-statement-interface, Injectable-callable-statement-interface
N JMX
If this key is set to True, the connection pool will be registered as the Mbean of the JMS server with the name "Proxool:type=pool, name=<alias>". The default value is False
N Jmx-agent-id
used when and only if the JMX option is set to true, a comma-delimited list of JMS proxy names registered to the connection, and if not set, all registered JMX servers will be used
N Maximum-active-time
The maximum lifetime of a thread, a thread that exceeds this time will be killed by the daemon thread, with a default value of 5 minutes
N Maximum-connection-count
To the maximum number of connections to the database, more than this connection, and then request, queued in the queue, the maximum number of waiting requests determined by simultaneous-build-throttle; The default value is 15
N Maximum-connection-lifetime
Maximum lifetime of the connection, in milliseconds, with a default value of 4 hours
N Minimum-connection-count
The minimum number of connections that remain open regardless of whether they are being used, the default value is 5
N Overload-without-refusal-lifetime
Used to determine the connection pool state, if the connection is rejected within the set time of this option (in milliseconds), the load is considered excessive. The default value is 60 seconds
N Prototype-count
Minimum number of idle connections to keep, note to differentiate from Minimum-connection-count. The default value is 0
N Simultaneous-build-throttle&NBSP;
The maximum number of wait requests, the default value is 10&NBSP;
N Test-before-use&NBSP;
If set to True, connection is tested with the statement set by House-keeping-test-sql before use, and if the test does not pass the connection is discarded and a connection is reassigned. The default is False&NBSP;
N test-after-use If set to True connection will be tested with the House-keeping-test-sql set before closing (putting back the connection pool) if the test does not pass connection will be discarded. The default value is False&NBSP;
? Unlike other connection pooling features, Proxool does not provide a set method. All features have to be info.setproperty ("Proxool.jmx", "false"), &NBSP;
? The setting of the above features has been verified in the code, whether the specific performance can be achieved to verify the actual &NBSP;
? Comparing the performance of Proxool with DBCP and C3P0, there are many opinions on the Internet, which need to be tested by ourselves. http://www.oioj.net/blog/user1/2853/archives/2006/298381.shtml This article has done the performance evaluation to the Proxool and the DBCP, its method is worth our test reference &NBSP;
Three data Sources (Dbcp/c3po/proxool) (I feel very good)
