Inter-Cluster Session Routing Migration

Details

Detail name Value
Changelog Number 13920
Type New Feature
Status Resolved
Fix Versions Exasol 8.16.0
Resolution Date 2023-04-28

Background

In situations where worker clusters can be dynamically started and stopped, it may be desirable for DBAs to be able to move sessions from one cluster to another. This flexibility can enable DBAs to better manage the loads on clusters. This new feature allows sessions to be moved from the MAIN cluster to a worker cluster using the CONTROL MOVE SESSION syntax.

Prerequisites

To move a session, the following prerequisites must be fulfilled:

  • The user must have DBA privileges in order to execute the SQL statements associated with this feature.
  • The session must have originally been established on the MAIN cluster. Sessions that are initially established on worker clusters cannot be moved.
  • The session is inactive (i.e. the session is not executing a statement and does not have an open transaction, result set or subconnection). Please see the session management documentation for details: https://docs.exasol.com/db/latest/database_concepts/session_management.htm .

SQL

See the below syntax to migrate a session to a worker cluster:

Move a list of sessions 

CONTROL MOVE SESSION SESSION_ID [,SESSION_ID ...] TO 'new-cluster-(name|uuid)' [WAIT TIMEOUT timeout_in_seconds] [FORCE]

Move all sessions 

CONTROL MOVE ALL SESSIONS FROM 'old-cluster-name|uuid' TO 'new-cluster-(name|uuid)' [WAIT TIMEOUT timeout_in_seconds] [FORCE]

Option descriptions

  1. None (WAIT TIMEOUT and FORCE not provided)
  2. WAIT TIMEOUT
  3. FORCE
  • The sessions will be moved immediately, if possible.
  • If the sessions cannot be moved, the user receives an exception message concerning the unmovable sessions. The other sessions which could be moved, were moved.
  • The client/statement will wait up to timeout_in_seconds for the sessions to be moved.
  • If the sessions cannot be moved after timeout_in_seconds, the user receives an exception message concerning the unmovable sessions. The other sessions which could be moved, were moved.
  • All sessions which cannot be moved will have their currently running statements killed and the clients will receive an exception with text: "Client requested execution abort. Message: Your session was moved to another cluster. Transaction was rolled back."

Examples

Move all sessions 

CONTROL MOVE ALL SESSIONS FROM 'WORKER1' TO 'WORKER2';

Move a list of sessions 

CONTROL MOVE SESSION 1234567890123456788, 1234567890123456789 TO 'WORKER1' WAIT TIMEOUT 60 FORCE;

Determining a session’s cluster name

A session’s current cluster name can be determined using the following.

  • For the current session: SELECT CURRENT_CLUSTER;
  • For all other sessions: SELECT CLUSTER_NAME, SESSION_ID FROM EXA_DBA_SESSIONS;

Determining a session’s cluster UID

A session’s current cluster UID can be determined using the following.

SELECT CURRENT_CLUSTER_UID;

Determining cluster names

The cluster names can be determined using the following.

SELECT CLUSTER_NAME FROM EXA_CLUSTERS;

Additional Notes

  • Only one MOVE SESSION statement at a time is currently supported. Multiple, parallel statements running at the same time may produce undefined behavior.
  • Sessions can be moved between worker clusters only if the initial client connection for the session was to the MAIN cluster. For example, a session which was created on the MAIN cluster and subsequently was moved to worker1 can also be moved again to worker2. However, a session which initially connected to worker1 cannot be moved to worker2.
  • The current session (i.e. the session running the MOVE SESSION statement) cannot be moved and will result in an exception.
  • If a session which has been routed to a worker cluster reconnects after a connection loss, the session will be on MAIN and not on the previous worker cluster after the reconnect.
  • If a session is already on the specified target cluster it will be ignored (i.e. it will not generate an exception).
  • Small WAIT TIMEOUT values may not be honored. Per default, each MOVE SESSION statement may wait up to a maximum of 4s (per default) for the sessions to be moved. If a WAIT TIMEOUT is not specified or the value if less than this time, it may be ignored.