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 its feature for load balancing queries within transactions. When this option is turned on, ScaleArc;
- Frees up the primary server for more write workloads and application stacking.
- Offloads read queries to secondary slaves that occur with a begin/end transaction block.
Use the ScaleArc Value Meter to assess if ScaleArc is load balancing SQL statements for optimal performance.
Set up a load balancing policy
- Click CLUSTERS > Settings > Load Balancing and Routing.
- Click on the Load Balancing Policies tab.
- Configure as follows:
Field/Button Description Read/Write Split Enabled when multiple database servers are defined in the cluster. 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 primary 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 master server.
Default is ON. Query Level Load Balancing Status Load balances SQL statements on a single database connection. This is an optional setting.
Default is OFF.
See below for configuration options when this setting is ON.
Read/Write Split Within Transaction Enabling this option will allow ScaleArc to load balance SQL statements within a transaction. Note that load balancing happens only for the read part of a transaction.
Default is OFF.
Query-Level Load Balancing
The query-level load balancing option balances SQL statements on a single database connection.
-
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.
Set 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.
- On the ScaleArc dashboard, click CLUSTERS > Settings > Load Balancing and Routing.
- Click on the Query Routing Policies tab.
- Turn ON the Query Routing option.
- 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.
- Click Add.
- Click the Gear icon against the policy group 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 UI 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:
- Click the Gear icon against the database of choice to edit the rules.
- Turn ON Rule Status.
- Click Add.
-
Complete the entries as follows:
Field/Button Description User input Order Specifies the order in which the SQL statement 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.
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 SQL statement 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 the next database node in the list.
Choose a rule type. -
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 than 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
|
Order |
Database |
User |
Source |
|---|---|---|---|
| 2 | Test | All users | All IPs |
To ensure the rule(s) in Policy group 2 are evaluated/tested after Policy group 1, enter an order value higher than 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:
- Click the Gear icon in the Add Rule screen to open the screen.
- Click on GENERATE SHARDS to open the screen to set up the sharding logic.
- The following screen will open to specify the sharding logic:
-
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 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 data 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 SQL statement 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's healthy; otherwise, it's sent to the 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. - Click Generate Rules and Save.