Overview

The purpose of this article is to outline why all queries in ScaleArc are going to the Read/Write Servers, and none are going to the Read Servers within a Cluster, and how to configure ScaleArc for these situations to achieve better load balancing.

Possible common causes for ScaleArc only routing queries to Read/Write servers ignoring Read Databases within a Cluster include but are not limited to:

1. Read/Write Split feature is not Enabled
2. Query Level Load Balancing is not Enabled
3. Some queries are using USE and/or SET commands, which are considered Writes, and may require Write Ignore rules configured
4. All queries are using prepare-exec statements (prepare-exec statements force all queries to the Read/Write servers)
5. Sticky Connection for Write Queries affecting Reads
6. MARS (in MSSQL) is currently only sent to the Read/Write server
7. Unidentified stored procedures are sent to the Read/Write server

Solution

Below are suggested resolution steps to resolve the load balancing issue:

1. Check that the ScaleArc Read/Write Split feature is enabled by navigating to CLUSTERS > Cluster Settings > Load Balancing and Routing then use the toggle switch to turn on Read/Write Split to ON under the Load Balancing Policies tab.
   
   
   
   
2. On the same screen, ensure that Query Level Load Balancing Status (QLLB) is enabled:

What is Query Level Load Balancing (QLLB)?

Query Level Load Balancing (QLLB) within ScaleArc is used when you need to load balance queries on a single database connection. In ScaleArc, the default status for this feature is OFF.  If you need to load balance queries on a single connection, and the QLLB feature is NOT enabled within ScaleArc, if a persistent client connection is opened, then the corresponding server connection is allocated to this client connection until the client connection is closed. With QLLB enabled, this will allow ScaleArc to dissociate client & server connections after every query, allowing the server connection to be shared by multiple persistent clients.

What situations would benefit from QLLB?

If the QLLB feature is NOT enabled within ScaleArc, if ScaleArc reaches the maximum concurrent server connections and all the connections are persistent client connections, then any further client connection cannot be opened. With QLLB enabled, this situation can be avoided as the existing server connections can be used for further clients.

Things to know when using QLLB

When using QLLB, there is a chance that a Read may go to another Backend Database server before replication takes place.  Some applications could think a change they just executed was not committed initiating a retry.  In this situation, this would be undesired if, for example, you were ordering using an online shopping cart resulting in an unintended double order.

3. Review the LIVE MONITOR for the cluster (LIVE MONITOR > Cluster Stats > [Cluster Name]) and check if Query Traffic is being balanced between your Read/Write database server(s) and your Read server(s).
   
   
4. If you are still experiencing issues after following the above steps, you may need to check ScaleArc Analytics for that particular cluster to see if there are query commands being sent that are considered as Write commands.  The most common query commands that are considered write commands that can cause issues are the USE and SET commands.  If you do see either of these query commands being used, but you need them to go to your Read server(s), you may need to enable the Write Ignore feature within ScaleArc, and create Write Ignore Rules for those query commands.  

Write/Ignore Feature

Refer to this article on Write Ignore Rules which describes the "Write Ignore" feature and the step by step procedure to set up Write Ignore Rules.

5. Sticky Connection on Write Queries

Turn the status ON only if you need to load-balance queries received after a write query on a particular connection otherwise the Read/Write queries on that particular connection will not be load balanced and ScaleArc sticks to Write (RW Server) for the entire lifetime of the connection. The number of such read queries that should be forwarded to the same RW Server is defined by the parameter  "Query Level Sticky Query". Default is 1 query.

• Note: In case of Query Level Load Balancing (QLLB ON), ScaleArc tries to load balance the traffic on every query. While doing this, in order to maintain the integrity of the data post DML queries, ScaleArc sends few subsequent read queries to the same RW server.