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:
- Performing query execution
- Has open transactions
- Has open prepared statement
- Has open resultset
- Has open sub-connection
Each active session occupies one active slot. The number of the active slots globally (not per node) 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. 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.
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, you will see the following warning message in EXAoperation:
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 an EXAoperation warning message is logged periodically until all requests have been processed:
Idle sessions in EXA_ALL_SESSIONS in most cases should not have any performance impact. An idle session can be in one of the following states:
- Connected, active: Active in this context implies that the session is using resources like 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 don't fulfill any of the above conditions. The database will change the status to Connected, inactive, and free these resources if at least 80% of the active session slots are in use or if the database has a high memory usage.
- 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.
In all the above states, a manual cleanup process is not necessary. However, if performed, it would interrupt operations for Idle/active sessions. Currently, there is no way to find out the Connected states through system tables. Connected, inactive and Disconnected sessions should have a TEMP_DB_RAM_USAGE of 0.0 in the current session list.