Understanding JdbcConnectionPool with H2 database

Question

I have an H2 server process running in my main server machine, allowing TCP connections.

Let's say I want to perform 100 SQL queries:

SELECT * FROM TEST

And, for my own purposes, I want to do one query per thread. Let's do this with one and only one Connection object shared among threads:

• Create one Connection object.
• Create 100 threads.
• In each thread, use the shared Connection object to call the SQL query.

The above will work, but it will be a bit slow. Of course, after all, if someone is using a Connection, then the others have to wait for it.

Well then, let's make one Connection per thread:

• Create 100 threads.
• In each thread, create a new Connection object and call the SQL query.

Much, much faster. But I feel like 100 connections is a bit of a waste. Maybe 50 connections would do fine. I heard that I can use JdbcConnectionPool for this sort of thing.

• Create a JdbcConnectionPool with a maximum of 50 connections.
• Create 100 threads.
• In each thread, use pool.getConnection() and then call the SQL query.

Huh. It is slow. If anything, it is as slow as the first approach. Maybe, out of curiousity, I should set the maximum of connections to 100...

... and it is still slow. Weird. It was my understanding that a pool with 100 connections would be equivalent to making one connection for each of my 100 threads.

What may be the problem? Here is the code for the last test:

import java.sql.Connection;
import java.sql.ResultSet;

import org.h2.jdbcx.JdbcConnectionPool;

public class App {

    public static void main(String[] args) throws Exception {
        Class.forName("org.h2.Driver");
        JdbcConnectionPool pool = JdbcConnectionPool.create("url", "user", "password");
        pool.setMaxConnections(100);
        for (int i = 0; i < 100; ++i) {
            Thread t = new Thread(new Client(i, pool));
            t.start();
        }
    }
}

class Client implements Runnable {
    int id;
    JdbcConnectionPool pool;
    public Client(int id, JdbcConnectionPool pool) {
        this.id = id;
        this.pool = pool;
    }
    public void run() {
        try {
            Connection conn = pool.getConnection();
            ResultSet set = conn.createStatement().executeQuery("SELECT * FROM TEST");
            if (set.next()) {
                System.out.println("Finished " + id);
            }
            set.close();
            conn.close();
        }catch (Exception e) {

        }
    }
}

---

I am using H2 1.4.182.

Answer 1

Source code of JdbcConnectionPool#getConnection():

  public Connection getConnection() throws SQLException {
        long max = System.currentTimeMillis() + timeout * 1000;
        do {
              synchronized (this){
                if (activeConnections < maxConnections) {
                    return getConnectionNow();
                }
                try {
                    wait(1000);
                } catch (InterruptedException e) {
                    // ignore
                }
            }
        } while (System.currentTimeMillis() <= max);
        throw new SQLException("Login timeout", "08001", 8001);
    }

One of the most expensive operations in your example is creating a connection. As you can see the method getConnection() has the lock synchronized (this) so only one thread can create connection at the same time and all other threads are in waiting.

I believe built-in h2 connection pool is pretty simple. If you want to have high-performance connection pool you can look at C3P0 or BoneCP.