Overview

This article describes the "Write Ignore" feature and the procedure to set up Write Ignore Rules.

Environment

• ScaleArc v3.6.1 and later

Information

Write Ignore is a feature by which a user can instruct ScaleArc not to treat specified queries as write queries. There are certain decisions that are taken when ScaleArc encounters a write query. For example, once ScaleArc receives a write query, all subsequent queries up to a certain number of queries will be routed to the primary server only. In order for Write Ignore to take effect, it needs to be turned ON and thereafter rules need to be added for each database server.

Write Ignore allows ScaleArc to exclude certain MySQL and MSSQL protocol commands and queries, which do not change the data, by specifying these using Set Commands and Set Rules. This helps load balance queries to slaves when some connection pooled application behavior (certain SET commands or non-standard commands) causes the 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 effective Read/Write split. Adding a set option like "SET names utf8" would let ScaleArc know that this set rule is okay to be sent to a read server.  The default status is ON, however, there are no Write Ignore rules defined as these have to be added as described below.

Follow these steps to enable and configure Write Ignore Rules:

1. Enable the Write Ignore feature by navigating to CLUSTER > Cluster Settings > ScaleArc tab > Write Ignore. Use the toggle switch to turn the feature ON.
2. Additional options for configuring Write Ignore are:
   • Set Rules – Click on this link to exclude the specified 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 ScaleArc uses after switching the connection from one database server to another. The switch can happen due to any of the following reasons; query routing, read/write split, and query level load-balancing.
3. Create a Write Ignore Rule by clicking on the Set Rules hyperlink 
   
4. In the Write Ignore Rules window, click on the gear icon next to the Database for which a rule needs to be added. 
   
5. Click Add Rule
   
   
   
   
6. Enter the Rule/Query you want to allow ScaleArc to route to a Read server
7. Under the Enabled column, tick the checkbox next to the Rule you just entered.  Repeat the above steps to add more rules as needed.
8. Once you have entered all of the rules you want to exclude from being detected as write queries, click the Save button.

   IMPORTANT:  The rules are case sensitive. For stored procedures, the exact name of every instance of the stored procedure needs to be added as a separate Write Ignore rule for it to work on all variations through which the stored procedure is invoked. Example:  For a stored procedure invoked using the following syntax “USP_FILINGSNAPSHOT_DETAILS”, “CORP.USP_FILINGSNAPSHOT_DETAILS” or “[CORP].[USP]_FILINGSNAPSHOT_DETAILS”, all three variations must be added as separate Write Ignore Rules.

9. Click the Close button to close the Window