Database connection pool performance

Hi again after a long break! Today I would like to focus on the performance of the database connection pools in terms of database connection retrieval: Oracle Universal Connection Pool (UCP) and Tomcat Connection Pool (org.apache.tomcat:tomcat-jdbc). Let's go to the details!
My plan was simple - retrieve the connection from the pool and return it (everything in a single thread), then measure the time necessary to accomplish these operations. I used a local installation of Oracle 11g Express Edition database - no networking overhead was involved in testing. Firstly, I wanted to check UCP connection validation mechanism because I thought that could introduce the highest performance degradation. Here is the code which I used:

PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/xe");
pds.setUser("system");
pds.setPassword("root");
pds.setInitialPoolSize(1);
pds.setMinPoolSize(1);
pds.setMaxPoolSize(1);

Connection connection = pds.getConnection();
Statement statement = connection.createStatement();
statement.execute("select 1 from dual");
connection.close(); // returns the connection to the pool. It does not close that connection physically.

// here I killed the session in Oracle

Connection connection2 = pds.getConnection();
Statement statement2 = connection2.createStatement();
statement2.execute("select 1 from dual");
connection2.close();

I received a beautiful exception thrown from the following line:

statement2.execute("select 1 from dual");

Exception in thread "main" java.sql.SQLException: ORA-00028: your session has been killed
                at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
                at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
                at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
                at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
                at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
                at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
                at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
                at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
                at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
                at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
                at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1890)
                at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1855)
                at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:304)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:606)
                at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:294)
                at com.sun.proxy.$Proxy1.execute(Unknown Source)

Then I added:

pds.setValidateConnectionOnBorrow(true); // validation query is not needed for UCP
                                         // as it I can use a special ping mechanism to do the validation

No exception, new connection established behind the scenes. Same situation for:

pds.setValidateConnectionOnBorrow(true);
pds.setSQLForValidateConnection("select * from dual"); // if you specify validation query it will be
                                                       // used instead of UCP internal ping

Interesting point is that I had to enlarge the maximum pool size to 2 in order to retrieve the connection. Otherwise the thread was blocked.
Having known that the mechanism works as expected I executed my simplistic performance test:

long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
    pds.getConnection().close(); // borrow and return connection to trigger validation mechanism
}
System.out.print(System.currentTimeMillis() - start + " ms");

The results were as follows:

No validation	1526 ms
Ping based validation	2161 ms
Validation query based validation	3188 ms

Next, I wanted to test Tomcat Connection Pool with the following code:

Properties properties = new Properties();
properties.setProperty("url", "jdbc:oracle:thin:@//localhost:1521/xe");
properties.setProperty("username", "system");
properties.setProperty("password", "root");
properties.setProperty("driverClassName", "oracle.jdbc.OracleDriver");
properties.setProperty("testOnBorrow", "true");
properties.setProperty("testOnReturn", "false");
properties.setProperty("initialSize", "1");
properties.setProperty("minIdle", "1");
properties.setProperty("maxIdle", "1");
properties.setProperty("maxActive", "1");
properties.setProperty("validationQuery", "select 1 from dual");
properties.setProperty("validationInterval", "0"); // this line is really important because
                                                   // the default value is 30 seconds meaning that
                                                   // particular connection will not be tested more
                                                   // often than 30 seconds and it would obfuscate
                                                   // our results siginificantly making them
                                                   // incomparable with UCP results

DataSource dataSource = new DataSourceFactory().createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
    dataSource.getConnection().close();
}
System.out.print(System.currentTimeMillis() - start + " ms");

No validation	172 ms
Validation query based validation	1543 ms

The conclusion is that Tomcat Connection Pool performs better than Oracle UCP ouf of the box. I did not do any fine tuning of UCP - maybe there is some kind of a property which allows to improve the performance further. Someone may claim that testing a database connection pool in a single threaded environment is not right. I think that my approach gives you some picture. It is unlikely that in the multi threaded environment the connection retrieval times will be shorter - they may be slower due to resource contention when the connection pool is saturated and the upcoming requests are overwhelming.

Thanks and see you next time!