In this section configure ScaleArc itself for

• load balancing, query caching, and auto failover.

ScaleArc 

Click on the ScaleArc tab to modify or enhance the configuration of ScaleArc.

1. On the ScaleArc dashboard, locate the Status column and click Cluster Settings. 
   
   
2. Click ScaleArc in the Cluster Settings control panel to open the tab.
   
   
   
   

3. Configure ScaleArc using the following table:

   Field	Description	Default/User input
   Write Ignore	When ON, ScaleArc excludes certain MySQL Server protocol commands and queries, which do not change the data, by specifying these using the Set Rules section. This helps load balance queries to Read-only replicas when a connection-pooled application behavior (certain SET commands or non-standard commands) causes the Read/Write split feature to favor principal servers most of the time. See the KB article for more details. Connection pooled apps like Java/Ruby on Rails require you to set Write ignore and Set ignore rules to achieve a Read/Write split during query-level load balancing. Adding a set option like "SET names utf8" informs ScaleArc to send the Set rules to a Read server. Consider putting the same SET rules into SET replay as well to retain connection level fidelity. If you have two applications -- one connection pooled and the other non-connection pooled -- use two different ScaleArc clusters to optimize the different advanced settings for performance.	Turn ON/OFF.
   Set Commands	MySQL commands configured as write ignore commands.	See the KB article for more details.
   Set Rules	Excludes certain MySQL queries from being detected as write queries.	See the KB article for more details.
   Set Replay	Specifies a SQL command or statement which gets executed on every new connection that ScaleArc uses after switching the connection from one database server to another. The switch can occur for the following reasons: query routing, Read/Write split, query level load balancing, switchover, mark server offline, or a new client connection.	See the KB article for more details.
   Authentication Salt Reuse	Enables the capability to reuse authentication salt during the authentication offload to improve performance.  This setting can improve performance for a php like application where connection pooling is not done at the app layer and there are lot of SQL connections being created and destroyed every second.	Turn ON/OFF.
   Maximum Query Logging Length	Maximum numbers of characters of a query to be logged in the query log file.	Default value is 2000.
   Query Based Connection Unstick	Enable/ disable query based connection unstick. This only resets stickyness caused by SET query.	The default setting is OFF.
   Unstick Commands	Enabled commands are matched against API-generated commands in the connection. If matched, the connections unstick from the existing server connection.	See below.
   Unstick Query	Query patterns provided here are matched against queries sent by the application. If matched, connections unstick from the existing server connection.	See below.
   Counter Module Status	Control the counter infrastructure feature which is used to gather statistics and counters of various ScaleArc states and resources at run time.	The default setting is ON.
   Counter Update Interval (seconds)	The counter statistics is collected from core engine at a specified interval.	Default value is 5 seconds.

    

    

4. Click Apply All to submit your edits or click Load Defaults to reset to factory settings.

Unstick commands

This feature resets stickyness caused by non-ScaleArc supported SET queries. This KB article provides a list of supported sets.

1. Click Unstick Commands.
2. Select the commands that you wish to match against the commands in the connection. If matched, the connections unstick from the existing server connection. 
   
    

Unstick query

This feature resets stickyness caused by non-ScaleArc supported SET queries. This KB article provides a list of supported sets.

1. Click Unstick Query.
2. Add the query patterns that you wish to match against each query. If matched, connections unstick from the existing server connection. 
   
    

Cache 

Use the Cache tab in the Cluster Settings tab to configure this feature.

1. Click the Cache tab.
   
   
    

2. Follow the table below to complete the configuration.

   Field	Description	User input
   Maximum Query Size for Cache (KB)	Specifies the maximum value in kilobytes above which the data for a select query response is not cached. When query cache does not seem to work, increase this number to accommodate for larger query responses. When responses to large queries are stored within ScaleArc, the cache memory utilization also increases.	Default value is 1024 KB.
   Cache Cleanup Cycle Time (seconds)	Specifies how often to run the cache garbage collector. This is not related to the minimum time to live (TTL) of your cache. So don't set this to an unreasonably low value.	The default value is 60 seconds.
   Cache Empty Result Set	Turn the status ON only if you need to cache empty responses to queries.  Sometimes when there is an empty result set for queries having cache rules where pattern match happens but does not result in cache rule hits, this option needs to be turned ON to achieve cache hits.	Default setting is OFF.
   Dynamic Query Caching	Turn this setting ON if you would like to enable dynamic query caching.	Default setting is OFF.

    

    

3. Click Apply All to submit your edits or click Load Default to reset to factory settings.

Auto failover    

1. Click the AutoFailover tab in the Cluster Settings dashboard to configure this feature.
   
   
   
   

2. Then configure the fields as follows:

   Field	Description	Default/User input
   Auto Failover	When a principal server fails, Auto Failover aims to simplify and automate the failover process to a standby, or another principal in the same cluster.	Turn ON/OFF.
   Failover Timeout	The amount of time (in seconds) a principal should be detected as down before a failover is triggered. if the principal remains down past the specified time, the system triggers the failover and upgrades either a slave or another principal to take over the job of the downed principal server. Make sure the timeout entries are appropriate for your environment.	Default value is 2 seconds
   Flip Flop Timeout	The minimum time (in seconds) to wait between fail-over events. This can be used to reduce flip-flops and avoid frequent failovers. Although making this timeout too high can result in an increased downtime for the cluster, in which case the failover takes place after the specified flipflop timeout. Override the timeout by using the switchover functionality by which failover can be manually forced, regardless of the flip-flop timeout.	Default value is 600 seconds.
   Replication Type	Replication Type - Select the replication type. Synchronous - Synchronous replication (Galera, Schooner etc.) is more consistent, and easier to setup for auto-failover. Asynchronous - Asynchronous replication (MySQL, Tungsten) requires an extra consistency check and delays to be setup to ensure proper fail-over behavior.	Default is Asynchronous.
   Failover Type	ScaleArc supports ScaleArc based auto-failover. ScaleArc also supports custom scripts that the system can trigger as part of the Auto Failover.	Select ScaleArc Based OR External API

ScaleArc-based failover

ScaleArc based failover lets you change roles within the ScaleArc system in case your servers are already setup as Active and Secondary Read-Write servers. By using this functionality you can rely on ScaleArc to do the failover and upgrade the Secondary Read-Write servers as Primary when the master is down.

1. Select the ScaleArc Based button for failure type.
   
    
    

2. Configure as follows:

   Field	Description	Default/User input
   ScaleArc Based	ScaleArc performs the failover and upgrades the Secondary Read-Write server as Primary in case the master is down.	Select the ScaleArc based option.
   Switch Delay Time	The time (in seconds) to wait before the standby database is promoted to active read-write after demoting the current active read-write server within the given cluster. High value of the switch delay time will result in higher downtime for the particular cluster.	Default is 5 seconds.
   Wait For Sync	This functionality makes sure that in case of a failover event, the newly promoted active read-write server is in sync with original read-write server. The failover waits till this process is carried out and so this can cause increased failover time.	Default setting is ON.
   Retry Attempts	The number of attempts to check if the replication is in sync between original read-write server and the to be promoted active stand by server.	Default is 3 attempts.
   Retry Interval	The retry interval (in seconds) between consecutive retry attempts.	Default is 1 second.
   Force Failover	Performs failover in the case of unsuccessful wait for sync or SQL I/O errors or replication errors. This option can result in data loss and increase recovery times.	Default setting is OFF.

External API 

The External API lets you call out to an external script which in turn promotes one of your existing slaves to a principal and informs ScaleArc which server to promote to Active Read/Write role. If you chose this option, follow the table below to configure it.

1. Select External API as a failure type.
   
   
   
   

2. Configure as follows:

   Field	Description	Default/User input
   External API call	Makes a call to an external script to promote one of the existing slaves to a master.	Select the external script from the drop down list.
   External Script	Uploads/Downloads an external API script. This script is invoked when the system triggers a failover in place of ScaleArc's pre-configured failover logic.	See below.
   Retry attempts	indicates the number of retries the system executes.	Default is 3 attempts.
   API timeout (seconds)	The script executes within the API timeout specified.	Default is 60 seconds.
   Switchover to Other DC	This option will send a flag to the external failover API indicating switchover to the DataCenter(DC).
   Trigger When Standby or Read Server is Down	When enabled the failover module will get triggered in case of any server being detected as down.	Default setting is OFF.

       3. Click Apply All to submit your edits or click Load Defaults to reset to factory settings.

Initiate an External API script  

You need to locate and edit the Eternal API script to tailor it to your environment before you can upload it.

1. Click on the External API field to locate the script from the drop down menu.
   
    
    
2. Download the script on your local drive.
   
   
   
   
3. Edit it carefully with your text editor to show the following: your ScaleArc appliance's IP address, the username and password for ScaleArc, and the server key.
   
    
    
4. Rename the file once you have completed the edit.
   
    
    
5. Load the edited file into the field. A successful upload displays this message:
   
    
    
6. Complete the remaining fields.
7. Click Switchover.
8. Enter a timeout number manually.
   
   
   
   
9. ScaleArc posts this message when it initiates the switchover:
   
   
   
   
10. If the switchover fails, you'll receive an error notification in the Events tab on the ScaleArc dashboard:
    
    
    
    
11. Correct the error and re-initiate the switchover.