Connection Pool and thread pool setting in Java

Spring application using Hikari pool.

Now for a single request from the client I have to query 10 tables(business required), and then composite the result together. And querying for each table may cost 50ms to 200ms. To speed up the response time, I create a FixedThreadPool in my service to query each table in different thread(pseudocode):

class MyService{
    final int THREAD_POOL_SIZE = 20;
    final int CONNECTION_POOL_SIZE = 10;


    final ExecutorService pool = Executors.newFixedThreadPool(THREAD_POOL_SIZE);
    protected DataSource ds;


    MyClass(){
        Class.forName(getJdbcDriverName());
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(CONNECTION_POOL_SIZE);
        ds = new HikariDataSource(config);
    }



    public Items doQuery(){
        String[] tables=["a","b"......]; //10+ tables
        Items result=new Items();
        CompletionService<Items> executorService = new ExecutorCompletionService<Items>(pool);
        for (String tb : tables) {
            Callable<Item> c = () -> {
                Items items = ds.getConnection().query(tb); ......
                return Items;
            };
            executorService.submit(c);
        }


        for (String tb: tables) {
            final Future<Items> future = executorService.take();
            Items items = future.get();
            result.addAll(items);
        }
    }
}

Now for a single request, the average response time maybe 500ms.

But for concurrent requests, the average response time will increase rapidly, the more the requests, the long the response time will be.

I wonder how to set the proper connection pool size and thread pool size to make the app work effective?

BTW, the database use RDS in cloud with 4 cpu 16GB mem, 2000 max connections and 8000 max IOPS.

Answer

You might want to think about a few more parameters:
1. Max concurrent request parameter for the database. Cloud providers have different limits of concurrent requests for different tiers, you might want to check yours.

2. When you say 50-200 ms, although it is difficult to say, are there 8 requests of 50ms and 2 requests of 200ms on an average or all of them pretty much the same? Why? Your doQuery might be limited by the query taking maximum time (which is 200ms), but the threads taking 50 ms will get released after it’s task is done making them available for next set of requests.

3. What is the QPS you are expecting to receive?

Some calculations: If a single request takes 10 threads, and you have provisioned 100 connections with 100 concurrent query limit, assuming 200ms for each query, you can only handle 10 requests at a time. Maybe a little better than 10 if most queries take 50ms or so (but I wouldn’t be optimistic).

Of course, some of these calculations goes for a toss if any of your queries takes >200ms (network latency or anything else) , in which case I recommend you have a circuit breaker, either at the connection end (if you are allowed to abort the query after a timeout) or at the API end.

Note : max connection limit is not the same as max concurrent query limit.

Suggestion: Since you need response under 500ms, You can also have a connectionTimeout of about 100-150ms on the pool. Worst case: 150ms connection timeout + 200ms query execution + 100ms for application processing < 500ms for your response. Works.