DBCP,C3P0,TOMCAT_JDBC performance and stability test _jndi

DBCP,C3P0,TOMCAT_JDBC Performance and stability test

1. Test environment:

Hardware environment:

Database server: 2u*8 kernel 8G memory
Test server: 2u*8 kernel 6G memory

Software Environment:

Jdk:

1.6.29

Mysql:

5.0.77

Mysql_driver:

Mysql-connector-java-5.0.8-bin.jar

DBCP:

Commons-dbcp-1.4.jar

Download Address: http://commons.apache.org/dbcp/

Commons-pool-1.5.6.jar

Download Address: http://commons.apache.org/pool/

C3P0:

C3p0-0.9.1.2.jar

Download Address: http://www.mchange.com/projects/c3p0/index.html

Log4j-1.2.8.jar (C3p0 need to add this package)

Download Address: http://logging.apache.org/log4j/

TOMCAT_JDBC:

Tomcat-jdbc.jar

Download Address: https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

Or you can use it directly from the Lib directory in the Tomcat installation root directory.

Tomcat-juli.jar

Download address: (not found)

Used directly from the bin directory in the Tomcat installation root directory

Configuration information:

Database connection Timeout set to: 10

Database supports maximum number of connections set to: 2000

Initialize Connection Pool Size: 10

Connect to minimum number of active threads: 10

Maximum active threads of connection pool: 100

Other configurations maintain the default configuration for each connection pool

2. Performance test:

Test points:

Under multithreaded multitasking, each connection pool gets the connection and then closes the connection immediately, comparing the time spent.

Read a lot on the internet about the database connection pool test,

Most test procedures include executing the SQL statement section, that is, creating the connection, executing the SQL statement, closing the connection,

That's what I did at first.

During the testing process, the data was found to be unstable, and the connection pools were both fast and slow,

After thinking, analysis, personally feel that this is inaccurate, the execution of SQL statements, the test is not the performance of the database connection pool,

Is entirely a database driver (such as Mysql_driver) and the performance of the database itself,

The database connection pool is solely responsible for establishing DataSource, acquiring (getting from the connection pool) Connection, closing (putting back into the connection pool) Connection,

So

When I was testing, I did not compute the time to initialize the connection pool (establish datasource), but rather the connection pool "get the connection and then close the connection immediately".

Test results:

DB POOL Thread
Quantity Single Thread
Execution times consume time
(ms) Start time
(ms) End time
(ms) Average consumption
Time (ms) average
Time (ms) DBCP 10 1000 251 1328863445815.00 1328863446066.00 251 0.0251 252 1328863466569.00 1328863466821.00 251 13288634771 74.00 1328863477425.00 254 1328863487555.00 1328863487809.00 247 1328863499474.00 1328863499721.00 C3P0 10 1000 781 132886 3372064.00 1328863372845.00 802.8 0.08028 789 1328863385489.00 1328863386278.00 879 1328863401335.00 1328863402214.00 773 1328863413608.00 1328863414381.00 792 1328863424693.00 1328863425485.00 tomcatjdbc 10 1000 191 1328863272642.00 13288 63272833.00 191.8 0.01918 197 1328863303126.00 1328863303323.00 187 1328863313262.00 1328863313449.00 195 1328863324253.00 1328863324448.00 189 1328863334700.00 1328863334889.00

DB POOL Thread
Quantity Single Thread
Execution times consume time
(ms) Start time
(ms) End time
(ms) Average consumption
Time (ms) average
Time (ms) DBCP 100 1000 786 1328862922748.00 1328862923534.00 810.4 0.008104 853 1328862939832.00 1328862940685.00 810 132886 2955354.00 1328862956164.00 807 1328862981344.00 1328862982151.00 796 1328862994825.00 1328862995621.00 C3P0 100 1000 251 7 1328863021884.00 1328863024401.00 2248.8 0.022488 2340 1328863040949.00 1328863043289.00 1968 1328863075044.00 1328863077012.00 2256 1328863092216.00 1328863094472.00 2163 1328863114138.00 1328863116301.00 TomcatJDBC 100 1000 752 13 28863155803.00 1328863156555.00 726 0.00726 725 1328863171617.00 1328863172342.00 694 1328863183983.00 1328863184677.00 703 1328863195628.00 1328863196331.00 756 1328863209798.00 1328863210554.00

DB POOL Thread
Quantity Single Thread
Execution times consume time
(ms) Start time
(ms) End time
(ms) Average consumption
Time (ms) average
Time (ms) DBCP 150 1000 1919 1328861533609.00 1328861535528.00 1854.4 0.012363 1957 1328861551638.00 1328861553595.00 1869 13 28861746964.00 1328861748833.00 1916 1328861791533.00 1328861793449.00 1611 1328861832003.00 1328861833614.00 C3P0 150 1000 2726 1328861869415.00 1328861872141.00 2990.8 0.019939 2570 1328861895349.00 1328861897919.00 3342 1328861912351.00 1328861915693.00 3218 1328861929664.00 1328861932882.00 3098 1328861950163.00 1328861953261.00 TomcatJDBC 150 1000 877 13 28861974599.00 1328861975476.00 861 0.00574 821 1328861990969.00 1328861991790.00 890 1328862016507.00 1328862017397.00 857 1328862037077.00 1328862037934.00 860 1328862052490.00 1328862053350.00

DB POOL Thread
Quantity Single Thread
Execution times consume time
(ms) Start time
(ms) End time
(ms) Average consumption
Time (ms) average
Time (ms) DBCP 300 1000 3908 1328862516139.00 1328862520047.00 3851.8 0.012839 3850 1328862408362.00 1328862412212.00 3939 13 28862440877.00 1328862444816.00 3806 1328862469116.00 1328862472922.00 3756 1328862495883.00 1328862499639.00 C3P0 300 1000 6111 1328862711585.00 1328862717696.00 6233.2 0.020777 5162 1328862618669.00 1328862623831.00 6261 1328862638870.00 1328862645131.00 6832 1328862659598.00 1328862666430.00 6800 1328862681808.00 1328862688608.00 TomcatJDBC 300 1000 3458 1 328862152316.00 1328862155774.00 3403.8 0.011346 3376 1328862308211.00 1328862311587.00 3397 1328862227685.00 1328862231082.00 3342 1328862261681.00 1328862265023.00 3446 1328862358400.00 1328862361846.00

Conclusion:

Overall performance: Tomcatjdbc > DBCP > C3p0

A lot of information on the Internet that C3P0 performance is better than DBCP, from my test results that is not the case, perhaps my configuration has not the right place,

When the maximum active thread is configured to 100 and concurrent to 150 threads, the TOMCATJDBC has obvious advantages.

That is, when concurrency exceeds the maximum number of active threads in the connection pool, but no more than too many cases, the advantages of TOMCATJDBC are obvious,

The results of my tests are all milliseconds,

For a small system, the concurrent pressure is small, choose which connection pool is not much different, consider more should be the stability of the connection pool.

3. Stability Test:

Test points:

1. When the database is closed for unknown reasons, the connection pool can be automatically reconnected after restarting, without restarting the application service.

2. The application service is normal, the database service is normal, but the network environment is abnormal, causes the connection to interrupt, at this time the connection pool is in the "Half connection" state,

Phenomenon:

Without restarting the application service, the MySQL database is restarted, and MySQL is fully restarted, executing the program,

Tomcatjdbc and DBCP are not automatically attached, repeat the query statement, will always report an exception, restart the application service and return to normal

C3P0 is automatically attached, repeated execution of the query statement, performing normally.

Conclusion:

Under the default configuration, TOMCATJDBC and dbcp do not have an automatic connection mechanism to view the official documentation, which can be resolved by modifying the configuration.

Other:

Connection pooling mechanism, there are 2 kinds:

1. Synchronous Authentication Mode:

Executes a predefined validation statement (SQL statement) each time a connection is fetched or a connection is closed.

Verify that the connection in the connection pool is valid and, if validation fails, completely shut down the connection.

This approach results in additional overhead for each database operation and a greater performance impact.

2. Heartbeat Verification Mode:

Perform one validation at a specific time, executing a predefined validation statement (SQL statement),

Verify that the connection in the connection pool is valid and, if validation fails, completely shut down the connection.

The verification interval can be adjusted according to the specific situation.

This approach maintains system stability at the expense of small performance overhead.

4. Experience

Since the release of TOMCAT7, a new shadow of the connection pool has begun to appear on the network, Tomcat JDBC,
After testing, we found that the performance of Tomcat JDBC is really good, is the choice of connection pool,
I have a limited level of e-wen, there is no way to translate the E-text so elegant,
Other benefits of Tomcat JDBC, please see its website introduction
Https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

This article describes in detail some of the deficiencies of DBCP and C3P0:
Why Another Connection pool project