Set up load balancing policy

Set up query routing policy

You can leverage the query routing feature to route to a database server or to a shard database; alternatively, you can choose to do both. Note that in the event of a conflict, 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.

1. On the ScaleArc dashboard, click Clusters > Settings > Load Balancing and Routing.
   
    
    
2. Click on the Query Routing Policies tab.
   
    
   
   
3. Turn ON the Query Routing option.
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 you 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 rules

You can create specific rules manually to direct queries to databases or shards 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 sharding requirement. 

Set the rule manually

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

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 query should be executed.	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 queries 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.
   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 queries 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 query. The queries 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. 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.

Creating Shard Rules

Follow these steps to create shard rules.

1. Click the Gear icon in the Add Rule screen to open the screen.
   
   
   
   
2. Select Generate Shards and configure appropriately. 
   
   
   
   
3. Click Generate Shards to open the screen. 
    
   
   
   

4. Then, complete the entries as follows: 

   Item	Description	User input
   Database column	Select the database.	Select a database from the drop down.
   Order	Specifies the order in which the query should be executed.	Enter the order.
   Table column	The database table to which the query 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 shards 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 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 queries 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 query. The queries 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.  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 save.