TDM Sleeping SQL Sessions

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are reviewing tool performance / optimization with our SQL DBAs and have been asked to check with you regarding sleeping sessions. What is the timeframe that the tool might reuse a sleeping SQL connection so that we don't kill it prior to that? Thanks

Environment

All supported TDM releases.

Cause

N/A

Resolution

SQL sessions are tightly coupled with connections in a connection pool (https://techcommunity.microsoft.com/t5/premier-field-engineering/connection-pooling-for-the-sql-server-dba/ba-p/370605). A connection pool managed by tomcat will keep connections ready to be used and therefore the underlying session will be kept running.

Tomcat is able to control these connections that are idle and evict them if required (https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html). When evicted, the underlying session will be terminated.

The following tomcat parameters are important to mange connection eviction:

minEvictableIdleTimeMillis

testWhileIdle

timeBetweenEvictionRunsMillis



testWhileIdle: The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool. The default value is false and this property has to be set in order for the pool cleaner/test thread is to run

minEvictableIdleTimeMillis : The minimum amount of time an object may sit idle in the pool before it is eligible for eviction. The default value is 60000 (60 seconds).

timeBetweenEvictionRunsMillis: The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread. This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often we validate idle connections. The default value is 5000 (5 seconds).



These entries can be controlled by adding in application.properties the following

tdmweb.TDMPublishService.db.spring.datasource.tomcat.minEvictableIdleTimeMillis

tdmweb.TDMPublishService.db.spring.datasource.tomcat.testWhileIdle

tdmweb.TDMPublishService.db.spring.datasource.tomcat.timeBetweenEvictionRunsMillis