Session Management

This article explains how to manage sessions in Exasol.

Exasol can handle about 10,000 sessions per node. The sessions can be either active or inactive. A session is defined as active if it matches at least one of the following conditions:

  • Performs query execution
  • Has an open transaction with a write operation
  • Has an open result set
  • Has an open sub-connection

Each active session occupies one active slot. The number of active slots per cluster is limited to 100 by default. When transitioning from an inactive to an active session, the session may wait for an active slot to become available. The status QUEUED will in this case be displayed for all the waiting sessions in the STATUS column of EXA_ALL_SESSIONS table.

All sessions waiting for an active slot are inserted into a prioritization-based queue respecting the session’s CPU weight from the corresponding consumer group. Sessions having the same CPU weight from the corresponding consumer group are served in a FIFO manner.

Reaching the active session limit is not necessarily a cause for concern. Additional sessions are queued until an active slot is available and are then executed.

In addition to the 100 active slots available globally, there are active but blocked session slots available for sessions that are waiting for another session. Typically these are sessions waiting for commit and have the status Waiting for session in the ACTIVITY column of the EXA_ALL_SESSIONS table. The active but blocked session slots are limited by the heap memory and the limit of about 1000 slots for the sum of active slots and active but blocked session slots.

Idle sessions

Idle sessions in EXA_ALL_SESSIONS usually do not have any impact on performance. An idle session can be in one of the following states: 

Connected, active

Active in this context implies that the session is using resources, such as a prepared statement, an open result set, or an open transaction. Although the impact is minimal, the session can not be disconnected silently. Open transactions will cause an increase in storage usage on the back-end when other transactions repeatedly modify tables.

Sessions may be still using some resources even if they do not fulfill any of the above conditions. If at least 80% of the active session slots are in use, or if the database has a high memory usage, the database will change the status of these sessions to Connected, inactive.

Connected, inactive

In this state, the session will not consume any resources. The database will remove these sessions by default after 24 hours of inactivity.

Disconnected

Sessions may be disconnected in some cases. For example, this can happen if the connection to the driver is lost due to network problems, or because of query termination due to high memory usage. By default, the database retains session information for about 2 hours. During this time, the drivers can automatically reconnect to the session. Disconnected sessions have no impact on performance.

Manual cleanup is not necessary in any of the above states. If a manual cleanup is performed, it would interrupt operations for idle/active sessions.

It is not possible to absolutely determine connection states through the system tables. Sessions that are in either the Connected, inactive or Disconnected states should have a TEMP_DB_RAM_USAGE of 0.0 in the current session list.

Move sessions

In native cloud deployments on AWS, active sessions can be moved between clusters. For more information, see Move Sessions Between Clusters.