The purpose of this article is to discuss 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.
All of the queries in ScaleArc are only routing to Read/Write servers, and no queries to any Read Databases within a Cluster.
NOTE: The below are common reasons which could cause this issue, but are not limited to the items listed below
- Read/Write Split feature is not Enabled
- Query Level Load Balancing is not Enabled
- Some queries are using USE and/or Set commands, which are considered Writes, and may need Write Ignore rules configured
- All queires are using prepare-exec statements (prepare-exec statements force all queries to the Read/Write servers)
- Sticky Connection for Write Queries affecting reads.
- MARS (in MSSQL) is currently only sent to Read/Write server
- Unidentified stored procedures are sent to the Read/Write server
Below are a few things to try to resolve this issue.
Query Level Load Balancing
1. Check that the ScaleArc Read/Write Split feature is enabled in the following location:
Clusters > Cluster Settings > Read/Write Split - Ensure this is ON
2. Check Query Level Load Balancing Status (QLLB) is enabled:
NOTE: Before Enabling Query Level Load Balancing, please see below to understand what QLLB is, and why you would use this feature:
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 queires 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 can not be opened. With QLLB enabled, we can avoid this situation, and 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 issued didn’t stick and that it has to retry. In this situation, for example, this would be bad if you’re ordering using an online shopping cart, as you may get a double order when you didn’t want it.
You can enable QLLB with the the ScaleArc UI in the following location:
Cluster > Cluster Settings > ScaleArc tab > Query level Load Balancing Status (Ensure is ON)
3. Go back to Live Monitoring for that Cluster, and see if Query Traffic is being balanced between your Read/Write database server(s) and your Read server(s).
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 fo those query commands.
You can exclude certain MySQL and MSSQL protocol commands and queries, which do not change the data, by specifying these using the Set Commands and Set Rules sections. This helps load balance queries to slaves when some connection pooled application behavior (certain SET commands or non-standard commands) causes read/write split feature to favor master servers most of the time.
For connection pooled apps like Java/Ruby on rails, setting Write Ignore and Set Ignore Rules along with turning Query Level Load Balancing (QLLB) ON is required to achieve Read/Write split. Adding a set option like "SET names utf8" would let iDB know that this set rules is okay to be sent to a read server. The default status is ON, however there are no Write Ignore rules defined.
IMPORTANT NOTE: In ScaleArc, we typically use Write Ignore rules when we see that Read/Write split is NOT happening and you want the queries to split. In some cases, it is safe to use the Write Ignore feature, such as when Apps or App Frameworks issue “SET names utf8” BEFORE EVERY QUERY AND the Database Server’s default character set is utf8.
In a similar case, where the “SET names utf8” causes ScaleArc NOT to perform Read/Write split AND the database default character set is NOT utf8, you would then need to use “Set Replay” rules to make sure ScaleArc does not corrupt the data.
1. Enable the Write Ignore feature within ScaleArc in the following location:
Cluster > Cluster Settings > ScaleArc tab > Write Ignore (ON)
The Options for the Write Ignore feature are below:
- Set Commands- Click on this link to select the MySQL or MSSQL commands that you would like to enable as Write Ignore.
- Set Rules – Click on this link to exclude certain MySQL or MSSQL Queries from being detected as write queries.
- Set Replay - Specify any SQL command or statement which will get executed on every new connection which iDB uses after switching the connection from one database server to another. The switch can happen due to following reasons, query routing, read/write split, and query level loadbalancing.
2. Go to the following location within the ScaleArc UI to create a Write Ignore Rule:
Cluster > Cluster Settings (for the production cluster) > iDB Settings > Write Ignore > Set Rules
3. In the Write Ignore Rules window:
1. Click Add Rule
2. Enter the Rule/Query you want to let ScaleArc know is okay to be sent to a Read Server
3. Under the Status column, put a check in the box next to the Rule you just entered. Repeate the above steps as needed.
4. Once you have entered all of the rules you want to exclude from being detected as write queires, click the Save button
IMPORTANT: When entering the above rule, the rules are CASE SENSITIVE
4. Click the Close button to close the Window
NOTE: Consider putting the same SET rules into SET replay as well to retain connection level fidelity. When ScaleArc switches a connection from the read server to a write server, it will not replay the SET commands, to achieve this one has to put all of the necessary commands in SET replay. If you have 2 applications one connection pooled and the other non-connection pooled one- use two different iDB clusters so that different advanced settings can be applied for optimal application compatibility and performance.
Sticky Connections for Write Queries
This parameter comes in picture once you enable the Query Level Load Balancing. In connection level load-balancing (QLLB OFF), ScaleArc sticks to Write (RW Server) for the entire lifetime of the connection. The no 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.
1. Disable the Sticky Connection for Write Queries feature within ScaleArc in the following location:
Cluster > Cluster Settings > ScaleArc tab > Sticky Connection for Write Queries (ON)
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.