Load Balancing and Routing

ScaleArc's load balancing decisions depend on how you configure settings such as Read/Write split and Query Level Load Balancing.

Query routing allows ScaleArc to override the load balancing decisions by using policies that route a specific SQL statement to a specific database server or a set/type of database servers. A simple use case is where you can run the reporting workload from a specific database node by specifying a user-based rule on ScaleArc.

Additionally, the query routing feature enables data segmentation at the database level without requiring you to make changes to the application. You can create query routing rules on ScaleArc to specify the data segment to which the incoming SQL statement should be forwarded.

ScaleArc further enhances performance through the ability to load balance queries within transactions by using the Rules for Read/Write Split Within Transactions feature. When this feature is turned on, ScaleArc;

  • Frees up the primary server for more write workloads and application stacking.
  • Offloads read queries that occur with a begin/end transaction block to secondary servers/slaves.
Tip: Use the ScaleArc Value Meter to assess if ScaleArc is load balancing SQL statements for optimal performance.

Set up a load balancing policy

Follow these steps to configure a load balancing policy:

  1. Navigate to CLUSTERS > Settings column > Load Balancing and Routing.

    Load_balancing___Routing_main.png
     
  2. Click on the Load Balancing Policies tab. 

    Load_balancing_policies.png 

      3. Configure as follows:

Field/Button Description User input/Default
Read/Write Split

Usable when multiple database servers are defined in the cluster and ScaleArc Authentication Offload is ON. ScaleArc distinguishes Read from Write statements and routes traffic accordingly. Read traffic is load balanced across multiple servers, while Write traffic is processed by the master server, defined in ScaleArc as Read/Write. For typical principal replica environments, Read/Write split should be turned ON so that ScaleArc can send Reads to replicas and everything else (+ some Read traffic) to the principal server.

Default is OFF. Default is ON when ScaleArc is in Always ON mode or connected to the Azure SQL Database server.
Query Level Load Balancing Status

Load balances SQL statements on a single database connection. This is an optional setting.

Turn ON/OFF.
Sticky Connection on Write Queries Turn the status OFF only if you need to load-balance queries received after a write query on a particular connection. Or else, the Read/Write queries on that particular connection will not be load balanced. Default is OFF.
Read/Write Split Within Transaction

Enabling this option allows ScaleArc to load balance SQL statements within a transaction. Note that load balancing happens only for the read statements before the first write in a transaction.

Default is OFF.

 

 

Turn on Read/Write split

If you attempt to configure Read/Write split and ScaleArc Authentication Offload is not ON, you will see the following errors:

  1. Click the Read/Write Split setting to ON.

    QueryLoadBalPolErrAuth.png 
     
  2. Attempting to turn on Authentication Offload while the cluster is running will result in this error. Click OK.

    QueryLoadBalPolErrStopClus.png
     

  3. Stop the cluster on the Cluster control panel page. 
  4. Turn ON ScaleArc Authentication Offload setting.
  5. Return to the Load balancing screen and turn ON the setting.  
  6. Start the cluster.

Query Level Load Balancing  

The query-level load balancing option load balances SQL statements on a single database connection. 

  1. When this option is enabled configure it as follows:

    QueryLoadBalPolSticky.png

    Field/Button Description User input/Default
    Sticky Connection on Write Queries

    Turn the status OFF only if you need to load balance SQL statements received after a write query on a particular connection. 

    Default is ON.
    Query Level Sticky Query

    Enter the number of read SQL statements after a write SQL statements to send to the same DB server after a write statement is executed. Connection will un-stick from the DB server after those many read statements are executed.

    Default is 1.

Setting up a query routing policy group

You can leverage the query routing feature to route to a database server or to a data segment of a database; alternatively, you can choose to do both. Note that in the event of a conflict, the Read/Write split decision takes precedence over a query routing decision. For example, if a stored procedure matches a routing rule which has only read database node(s) but if that stored procedure is not part of the Write Ignore rule it does not get sent to the read database node; instead the system generates a custom error message for the client. If all the database nodes matching the query routing rule are marked unhealthy then the normal queuing logic kicks in.

Follow these steps to set up a query routing policy:

  1. Navigate to CLUSTERS > Settings column > Load Balancing and Routing.

    Load_balancing___Routing_main.png
     
  2. Click on the Query Routing Policies tab.

    Query_routing_policy.png

  3. Turn ON the Query Routing option. Select yes when prompted to confirm changing the query routing status.
  4. Add a query-routing rule by selecting the database(s), user(s), and one or more IP addresses. This creates a group to which you can add more granular rules as per your requirement by providing the exact query pattern.  Add_rule.png
  5. Click the gear icon against the database of choice to edit the rules.

Create specific query-routing policy rules

You can create specific rules manually to direct SQL statements to databases or data segments of databases if they are very complex and specific to your requirements. Alternatively,  you can use the ScaleArc GUI to generate a query-routing rule for a specific data segment. 

Set the rule manually

Follow these steps to manually create a rule for query-routing to databases or to a data segment:

  1. Click the gear icon against the database of choice to edit the rules.

    Edit_rules.png

  2. Turn ON Rule Status.
  3. Click ADD.

    Enable_rule.png
     
  4. Complete the entries as follows:

    Field/Button Description User input
    Order Specifies the order in which the SQL statements should be executed. Note that you cannot edit the order number when you define a policy group. You can edit the order number for the rule associated with a policy. Enter the order.
    Source Pattern The query routing pattern. Enter the query routing pattern.
    Route To

    The target destination for the pattern. You have a choice to send the SQL statements to Read-Only or to a Read/Write server. Once the SQL statement gets matched with the rule it gets diverted to the database server, based on the type of server added in the cluster.

    Route the query to an IP address or a type of server.
    Check box Indicates whether the rule is enabled or disabled. Select the checkbox to enable.
    AND/OR rule

    The AND rule uses the default load-balancing logic that has been defined in the cluster. The database servers do not have any priority, with respect to the route. The SQL statements are load-balanced among all the selected database nodes.

    The OR rule is a prioritized list of servers. The specified order indicates the priority in which a database server is chosen to route the SQL statement. The SQL statements are always sent to first database node in the list if it's healthy; otherwise it's sent to next database node in the list.

    Note: And/Or field cannot be edited when the Route To field has role-based (Read + Write, Read-Only) values.
    Choose a rule type.

     

  5. Click SAVE.

Set policy precedence   

Rules from all policy groups in the query-routing module are put in a single linear list, sorted in the ascending order of the order value, irrespective of the policy group. Use the rule-editing function to set policy precedence for executing an order when you have overlapping policies (for example, when you have multiple policies with rules that have the same order numbers). 

Consider the following two policy groups (Policy group 1 and Policy group 2):

Policy group 1

Order

Database

User

Source

1 Test  ssluser All IPs

If you want the rule(s) in this group to be evaluated first, before checking in the next policy group(s), for example Policy group 2, assign the order value of the routing rule(s) lower then the values assigned to the rules in the other policy group(s). For example, assign the order values for rules in this group from 1 to 100.

PolicyGrp1a.png

PolicyGrp1b.png

Policy group 2

Order

Database

User

Source

2 All  All All IPs

To ensure the rule(s) in Policy group 2 are evaluated/tested after Policy group 1, enter an order value higher then the other group(s). For example, assign order values for rules in this group from 101 to 200.

PolicyGrp2a.png

PolicyGrp2b.png


Creating data segmentation (shard) rules

Follow these steps to create shard (data segmentation) rules:

  1. Click on the gear icon in the Add Rule screen to open the screen.

    Edit_rules.png 
     
  2. Click on GENERATE SHARDS button to open the corresponding interface.

    Generate_shards.png

  3. The Generate Shards screen. 

    Generate_shards_screen.png

  4. Use the following table to configure both screens. 

     Item Description User input
    Database column Select the database. Select a database from the drop-down.
    Order Specifies the order in which the SQL statement should be executed. Enter the order.
    Table column The database table to which the SQL statement is routed. Select from a drop-down menu.
    Key column The table column key for the query routing rule. Select from a drop-down menu.
    Number of shards The number of database segments you would like to create. Enter a number.
    From and To range Entries allow the key column to generate routing patterns. Specify the range.
    Route To

    The target destination for the pattern. You have a choice to send the queries SQL statement to Read-Only or to a Read/Write server. Once the query gets matched with the rule it gets diverted to the database server, based on the type of server added in the cluster.

    Tip: Refer to your cluster configuration to confirm which IP is Read-Only or Read/Write. A destination cannot have both role-based and IP-based values in a single rule.
    Route the query to an IP address or a type of server.
    Checkbox Indicates whether the rule is enabled or disabled. Select the checkbox to enable.
    AND/OR rule

    The AND rule uses the default load-balancing logic that has been defined in the cluster. The database servers do not have any priority, with respect to the route. The SQL statements are load-balanced among all the selected database nodes. 

    The OR rule is a prioritized list of servers. The specified order indicates the priority in which a database server is chosen to route the SQL statement. The SQL statements are always sent to the first database node in the list if it is healthy; otherwise, they are sent to the next database node in the list.

    And/Or field cannot be edited when the Route to field has role-based ( Read + Write, Read-Only) values.
    Choose a rule type.

     

  5. Click Generate Rules and then SAVE.         

Back to top