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.

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.
   
   
    
2. Click on the Load Balancing Policies tab. 
   
    

      3. Configure as follows:

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.
   
    
    

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

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:
   
   
   
   

   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 statement to send to the same DB server after a write statement is executed. The 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.
   
   
    
2. Click on the Query Routing Policies tab.
   
   
   
   
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. 
   
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.
   
   
   
   
2. Turn ON Rule Status.
3. Click ADD.
   
   
    

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 the first database node in the list if it's healthy; otherwise, it is 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

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.

Policy group 2

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.

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.
   
    
    
2. Click on GENERATE SHARDS button to open the corresponding interface.
   
   
   
   
3. The Generate Shards screen. 
   
   
   
   

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.