search cancel

High number of sessions on repository Database

book

Article ID: 229740

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

People were having issue connection to the GTREP schema on the repository Database.

If you look at the screenshot, you can see that SCRAMBLE has over 1200 session opened and INACTIVE. Why?

We need implementation of better sessions/connections handling by the TDM portal because we cannot ask DBA to level up the sessions or processes limit on the Database each time we bump into this issue.

Environment

Release : 4.9.1

Component :

Resolution

 

I decided to put in the configuration that Service Engineer provided :

spring.datasource.tomcat.validationQuery=select 1 from dual

spring.datasource.tomcat.validationQueryTimeout=5

spring.datasource.tomcat.testOnBorrow=true

spring.datasource.tomcat.testWhileIdle=true

spring.datasource.tomcat.timeBetweenEvictionRunsMillis=10000

spring.datasource.tomcat.minEvictableIdleTimeMillis=60000

 

Result:

The entries provided by Marc are valid and can be used in your environment. As discussed on our call, every customer uses the product ( and tomcat) differently and therefore each customer should configure application.properties to fit their needs.

 

I noticed that in your entries you specified minEvictableIdleTimeMillis=60000 (1 minute)

I would increase the time to at least 10 or 30 minutes instead. What you want is to ensure that a connection has a chance to be reused rather than to quickly close it.

 

# The indication of whether objects will be validated by the idle object evictor.
# If an object fails to validate, it will be dropped from the pool.
spring.datasource.tomcat.testWhileIdle=true
# sleep time between runs of the idle connection validation/cleaner thread
# It dictates how often we check for idle, abandoned connections, and how often we validate idle connections.
spring.datasource.tomcat.timeBetweenEvictionRunsMillis=10000
# The minimum amount of time an object may sit idle in the pool before it is eligible for eviction.
spring.datasource.tomcat.minEvictableIdleTimeMillis=60000

 

spring.datasource.tomcat.validationQuery=select 1 from dual

spring.datasource.tomcat.validationQueryTimeout=5

spring.datasource.tomcat.testOnBorrow=true

 

Attachments