Consumer group: idle session timeout

Details

Detail name Value
Changelog Number 11515
Type New Feature
Status Resolved
Fix Versions Exasol 7.1.0, Exasol 7.1.beta1
Resolution Date 2021-07-29

Background

The Consumer Groups allow defining properties on a role basis.
They allow fine-grained control of the resource-utilization among users and roles.
This new feature allows specifying idle session timeout within the consumer group.

Long-running open sessions could impact other sessions by holding unnecessary object locks or occupying an active slot.

In the past, the idle session timeout could only be implemented in a watchdog script.

Description

This new feature allows better session management control by killing the session if the specified idle timeout is reached. 

Consumer Groups

The idle timeout is implemented into consumer groups (IDLE_TIMEOUT attribute) .

  • Defined as DECIMAL(6,0) for values in the interval <1 , 999999>, in seconds or 0 representing UNLIMITED
  • Consumer group attribute value can be set independent of system and session parameters.
  • Default value is 86400 seconds (24h)
  • Minimum value between session parameter IDLE_TIMEOUT and the user consumer group IDLE_TIMEOUT is considered, to kill the session if this value is reached.

Example:

CREATE CONSUMER GROUP Group1
    WITH CPU_WEIGHT = 150,
         PRECEDENCE = 300,
         MAX_GROUP_TEMP_DBRAM = '10G',
         MAX_SESSION_TEMP_DBRAM = '1G',
         MAX_USER_TEMP_DBRAM = '2G',
         IDLE_TIMEOUT = 3600;

ALTER CONSUMER GROUP Group1 set IDLE_TIMEOUT = 0; 

 

Session and system parameter

This feature is also integrated into new system and session parameters: IDLE_TIMEOUT, which allows fine-grained user control over their own sessions. 

Example:

alter system set idle_timeout = 0;
alter session set idle_timeout = 3600;