Spring Batch connection pooling and multithreading

1
votes

I have a Spring Boot 1.5 application with Spring Batch 3.0.7 and Java 8. I recently received some connection timeouts when a scheduled job tried to start with 5 threads, while another long-running batch job was running. It appears there is contention for the connections by the combined 15 threads. I haven't found any documentation, blogs or questions that seem to address the correlation with threads and pools in Spring Batch.

I have 3 connections using HikariCP, each datasource configured as default (10 connections):

  • batcdb (postgres)
  • readdb (oracle)
  • writedb (postgres)

I have 2 jobs:

  • Job 1 (long-running) - max-pool-size: 10
  • Job 2 (short-running) - max-pool-size: 5

I have been able to recreate this error numerous times.

I wanted to try something different, so I upped my AWS EC2 instance type to a C4.xlarge, and set max-pool-size: 20. This is where things get really confusing, instead of the HikariCP pools showing 20 connections active, it consistently stays at 10 connections active, 10 connections idle. Which tells me there may be a different setting around the number 10 somewhere? 

DEBUG 3054 --- [cdb housekeeper] com.zaxxer.hikari.pool.HikariPool : writedb - Pool stats (total=20, active=10, idle=10, waiting=0)

Note: I have also observed the chunk logging output ~20 threads in the logs, which would tell me that the threading/taskExecutor is behaving as intended.

[taskExecutor-1]
...
[taskExecutor-20]

I would prefer not to keep throwing more connections at my application to allow concurrent jobs to run. Any suggestions and ideas will be greatly appreciated.

1
multithreadingspring-bootspring-batchconnection-poolinghikaricp
Are you sure that there are more number of concurrent requests to use those 10 idle threads from the thread pool? - hagrawal
I reviewed my settings and realized that I had set throttle-limit: 10, which seems to be the reason for what I had expected to be a continued correlation between threads and connections. With throttle-limit increased to 20, all 20 connections are showing as active. - shawnjohnson
Super. May be put that as answer and accept it so that it will not remain in unanswered pool. - hagrawal
Ultimately what I was hoping to get out of this question is a better understanding of the correlation between the TaskExecutor for a Job and the connections in the connection pool. I expected the source connection to mostly be used for an initial "Read" - page-size: 5000; chunk-size: 250; core-pool-size: 20 - Then the processing goes on using a different datasource - the processing is slow, so the 'readdb' connection should in-theory be idle. Why does each thread need to hold on to it's own 'readdb' datasource connection? - shawnjohnson
Your current question is only about why there is gap of 10 threads, if you have any other question about pool size of executor service or anything else then please open a new question. - hagrawal

1 Answers

1
votes

The issue was due to a difference in the number of core/max pool-size and the throttle-limit.

My config:

core-pool-size: 20
max-pool-size: 20
throttle-limit: 10

When I increased the throttle-limit to 20, the connection-pool logging showed all 20 connections were 'active'.