Move sessions between clusters

Learn about how sessions can be moved between clusters in Exasol SaaS to distribute the loads.

Overview

In multi-cluster deployments of Exasol SaaS where worker clusters can be dynamically started and stopped, you may want to move sessions between clusters to better manage the loads on the clusters.

Sessions can be moved automatically based on a scaling policy if session offloading is enabled for the cluster, or manually by using the CONTROL MOVE SESSION statement.

  • Only sessions that are initiated on the main cluster can be moved to other clusters. Sessions that are initiated on a worker cluster cannot be moved.

  • If a session was initiated on the main cluster and then moved to a worker cluster, it can be moved to another worker cluster.

  • If a worker cluster is stopped, any sessions on that cluster that were initiated on the main cluster are automatically migrated back to the main cluster. Sessions that were initiated on the worker cluster itself are terminated.

  • The network connection for a moved session is still with the main cluster but forwarded internally to the worker cluster. There is no need to explicitly whitelist the worker cluster(s) on the client side.

Auto scaling

The auto scaling mechanism in Exasol SaaS allows sessions to be automatically distributed across clusters in a cluster resource group based on a scaling policy that determines on which cluster to run a session.

Create a scaling policy

The scaling policy is defined in JSON format by using the ALTER SYSTEM command with the AUTO_SCALING_POLICY parameter.

The auto-scaling mechanism will not create/destroy or start/stop worker clusters. The clusters in a cluster resource group must exist and be in the running state.

JSON definition

  • version (number)

    The version number of the JSON syntax format. Currently, only version 1 is supported.

  • clusterResourceGroups (array)

    An array of cluster resource groups. Currently, only one group is supported.

    • groupId (string)

      A user-defined ID for the cluster resource group. The group ID can be set freely.

    • clusters (array)

      An array of clusters in the group. For each cluster, set the following parameters:

      • clusterId (string)

        The name or UID of a cluster. Either is accepted.

      • weight (number)

        The preference for a given session to run on a certain cluster relative to the other clusters in the group.

        The higher the weight of a cluster, the more likely it is that a session will be routed to that cluster.

        Weights are normalized over the clusters, and the absolute values of the weights are not directly relevant. For example, the effect of clusters A and B having weights 1 and 2, respectively, is exactly the same as if the weights were 50 and 100.

  • scalingPolicy (string)

    Specifies the scaling policy that should be used in order to distribute the sessions among the clusters. Select one of the following policies:

    • session-weighted

      Balances sessions based on a weighted probability distribution specified by the weight set for each cluster (see above).

    • session-autobalance

      Balances sessions based the total number of connected sessions to a cluster (not the number of active sessions) and cluster weight.

    • session-round-robin

      Sessions are distributed among clusters sequentially and uniformly in a round-robin fashion. Cluster weight is ignored.

Example:
Copy 
ALTER SYSTEM SET AUTO_SCALING_POLICY =
'{
    "version": 1,
    "clusterResourceGroups": [
        {
            "groupId": "default",
            "clusters": [
                { "clusterId": "cluster1", "weight": 10 },
                { "clusterId": "cluster2", "weight": 20 }
            ],
            "scalingPolicy": "session-autobalance"
        }
    ]
}'

Move sessions manually

You can manually move a single session, a comma-separated list of sessions, or all inactive sessions to a worker cluster using the CONTROL MOVE SESSION command.

  • Only sessions that are initiated on the main cluster can be moved.

  • If a session was initiated on the main cluster and then moved to a worker cluster, it can be moved to another worker cluster.

  • The session must be inactive (not executing a statement or having an open write transaction, result set, or sub-connection).

  • You must have the DBA role in the database.

Clusters can be specified either by cluster name or by cluster UID. To determine the cluster name and/or cluster UID, use SELECT.

Examples:
Copy 
-- Get the current cluster name for the current session:
SELECT CURRENT_CLUSTER;

-- Get the current cluster name for all sessions (identified by session ID):
SELECT CLUSTER_NAME, SESSION_ID FROM EXA_DBA_SESSIONS;

-- Get cluster UID for the current session:
SELECT CURRENT_CLUSTER_UID;

-- Get all cluster names:
SELECT CLUSTER_NAME FROM EXA_CLUSTERS;

Timeout for session move

You can specify a timeout value in seconds for the session move by using the WAIT TIMEOUT clause. If the session is not moved within the timeout, an exception is thrown. If the FORCE clause is added, all currently running statements are forcibly killed on sessions that cannot be moved within the timeout.

Example:

To move two sessions from the main cluster to a worker cluster, setting a timeout of 60 seconds:

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

For more details and examples, see CONTROL MOVE SESSION.