Session Management

This section explains how to manage sessions in Exasol.

Exasol can handle about 10,000 sessions per node. These 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. By default, the number of the active slots per cluster is limited to 100. When transitioning from an inactive to an active session, the session may wait for an active slot to become available. In such a case, the status QUEUED will be displayed for all the waiting sessions in the STATUS column of EXA_ALL_SESSIONS table. All sessions waiting for an active slot 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, 100 active but blocked session slots are 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. If the number of the blocked sessions exceeds the active but blocked session slots count, then the remaining free active slots are used for them.

If 80% of the maximum allowed active session slots are in use, the following warning messages are logged: 

Query queue limit of active sessions nearly reached, 
running: <<active_sessions>> of 100 (+ <<blocked_sessions>> WAIT FOR COMMIT) are in use. 
Total number of sessions: <<all_sessions>>.

If a session cannot start executing a query because no active session slots are available, then a warning message is logged periodically until all requests have been processed: 

Limit of active sessions has been reached.

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.

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

There is currently no way 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.

Moving sessions

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