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.
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,
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:
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.
Currently, there is no way to determine the Connected states through the system tables. Connected, inactive and Disconnected sessions should have a TEMP_DB_RAM_USAGE of 0.0 in the current session list.
Inactive sessions can be moved from the main cluster to a worker cluster. For more information, see Move Sessions Between Clusters.