There is no "read" going to the "read" replicas - all requests going only to "write" server.
This article has been replaced by this article:
- Read/Write Split affect requires more than one available database in the given cluster
- Database with Role in ScaleArc set with "No Traffic" will not be used for Read/Write Split
- Analytics shows a large number of "read" type queries
- Presence of "persistent" connections (see ScaleArc Query-Level Load Balancing)
- Very few "errors" indicated in charts (SQL errors)
- Logs appear to be clean
ScaleArc will load balance to all available servers, within the constraints of advanced settings. Under default settings, even infrequent "write" operations on a shared (reused) connection to the DB will result in that connection being migrated to a "write" DB server.
- Without a load-balancer (or other connection-pooling behavior) feeding [few] connections to ScaleArc, we would expect a very large number of client connections being created and destroyed.
- With each new connection, ScaleArc determines if the demand will be recognized as a "read" or a "write" and then where to send the demand - to which DB server (or cache). Whenever possible, ScaleArc will re-use connections to the back-end DBs to service the same inbound client demand, as long as the preceding demand has been fulfilled. As long as the result has returned from the previous query, the next query will re-use the same connection.
- Analytics shows that among the "write" demand, there is also a "SET NAMES" being used at a rate of 0.2% of the traffic (for sample hour) representing 25s of server time.
- Even a single "SET NAME 'utf8'" statement on a reused connection will cause ScaleArc to move that connection to a "write" server where it may stay until it becomes unused and is closed.
- Having a very few connections with even a few "write" statements, including "SET" commands will cause ScaleArc to move that traffic to a "write" server resulting in all connections being associated to the "write" server and no connection on the "read" server(s).
- "Write Ignore" options can exclude certain MySQL 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 peculiar application behavior (certain SET commands or non-standard commands) causes Read/Write split feature to favor master servers most of the time. (default on)
- "Ignore Rules" is used to exclude certain MySQL Queries from being detected as write queries. (two default rules enabled)
- "Replay Setting" defines SQL command or statement which will get executed on every new connection which ScaleArc use after switching the connection from one database server to other database server (no default commands)
- "Sticky Read" option (advanced cluster setting) is used to force reads to stay on the same "write" DB server when they are on the same connection following a "write" operation to the DB. (default on)
- "Ignore Replication Lag" option (advanced cluster setting) is used to forgive "read" replica servers for being slow/behind in replication. ScaleArc will continue to send "read" traffic to show servers. This can prevent overwhelming traffic being loaded onto the "write" servers, but can also cause other impacts to performance. (default off)
- "Query-Level Load Balancing" option (advanced cluster settings) allows ScaleArc to manage each query within each connection for load-balancing across available DB servers. (default off)
If you have any questions in regards to this article, please feel free to contact ScaleArc Support at email@example.com or call us at 1-855-800-7225 (US Toll Free) or +1-408-412-7315 (Outside of the US)