I have a server side application for processing data from SQL Server database. The frequency of the hitting the database is 100s of transactions per second in batch. I am using Spring Boot 2.4.1 which includes <hibernate.version>5.4.25.Final</hibernate.version>, <hikaricp.version>3.4.5</hikaricp.version>
Also along with Spring Data JPA, I am using SQLServerPreparedStatement for bulk batch transactions calling Stored Procedures and also INSERT,SELECT queries, and uses the same DB Connection Pool via EntityManager.
My problem is, even if I increase the Max Connection Pool to like 500-600 it cannot handle and connection timeout/connection cannot be acquired kind of error occurs frequently. Once the max connection pool reaches, I think the connection is acquired but not released for pending transactions.
My configuration in application.properties is :
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.pool-name = SpringBootHikariCP
spring.datasource.hikari.maximum-pool-size = 600
spring.datasource.hikari.minimum-idle = 3
spring.datasource.hikari.max-lifetime = 2000000
spring.datasource.hikari.connection-timeout = 30000
spring.datasource.hikari.idle-timeout = 30000
#spring.datasource.hikari.auto-commit=true
#spring.datasource.hikari.data-source-class-name=com.microsoft.sqlserver.jdbc.SQLServerDataSource
spring.datasource.hikari.data-source-properties.cachePrepStmts = true
spring.datasource.hikari.data-source-properties.prepStmtCacheSize = 250
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit = 2048
spring.datasource.hikari.data-source-properties.useServerPrepStmts = true
spring.datasource.hikari.data-source-properties.useLocalSessionState = true
spring.datasource.hikari.data-source-properties.rewriteBatchedStatements = true
spring.datasource.hikari.data-source-properties.cacheResultSetMetadata = true
spring.datasource.hikari.data-source-properties.cacheServerConfiguration = true
spring.datasource.hikari.data-source-properties.elideSetAutoCommits = true
spring.datasource.hikari.data-source-properties.maintainTimeStats = false
spring.jpa.database-platform=org.hibernate.dialect.SQLServer2012Dialect
#spring.jpa.properties.hibernate.show_sql=true
#spring.jpa.properties.hibernate.format_sql=true
#spring.jpa.properties.hibernate.use_sql=true
spring.jpa.properties.hibernate.jdbc.batch_size = 20
#spring.jpa.properties.hibernate.id.new_generator_mappings=false
#spring.jpa.properties.hibernate.default_schema=dbschema
#spring.jpa.properties.hibernate.search.autoregister_listeners=false
#spring.jpa.properties.hibernate.bytecode.use_reflection_optimizer=false
spring.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions = true
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true
spring.jpa.properties.hibernate.order_inserts=true
Please suggest an appropriate configuration for such traffic. Main problems are : Connection time out and Connection pool being full.
There are multiple schedulers, normally 3, which extracts data from database and prepares data to save in batch. The schedulers run every second and extracts new unprocessed data(rows in that snap, can range from 1 or more) every second, processes and saves to database.
Please suggest efficient approaches for this kind of problem.