Navigate
ScaleArc Customer Support
  • Login to Ignite Global Support
or
Contact Us
  • Get in touch

    Send us an email

  • Submit Feedback

    Feedback and suggestions submitted by customers like you

  • Knowledgebase Read help articles
  • Feedback Custom suggestions
  • Downloads Browse our downloads
  • Contact Us We are here to help
  • Portal
  • Knowledgebase
  • ScaleArc Registered User
  • How To: MySQL Transaction Patterns
Subscribe Download PDF

How To: MySQL Transaction Patterns

Arun Sangameswaran
2017-09-08
0 Comments
in ScaleArc Registered User

Release Classification Level DB Platform Categories
3.12 GA How To MYSQL Transaction

 

TOPIC

What are ScaleArc MySQL transaction patterns and how to configure them?

DETAILS

As part of advanced configuration, ScaleArc provides the option to configure patterns/rules at varying levels of granularity. These patterns/rules determine how ScaleArc caches queries and uses read ignore statements within transaction to load balance traffic. Contact support@scalearc.com for additional details on configuring these features.

FIELD
 Description
 Default/User input
Read/Write Split Within Transaction

Enabling this option will allow ScaleArc to load balance SQL statement within a transaction. Note that load balancing happens only for the read part of a transaction. Additionally, users can provide transaction load balancing and transaction cache pattern to perform selective load balancing and caching of read queries in any part of transaction.

Enable for repeatable read - select this option to enable Read/Write split within transaction for server with repeatable read level of isolation.

Isolation level support in ScaleArc

1. Read committed - supported by default
2. Read uncommitted - supported by default
3. Repeatable read - supported if enabled (not default)
4. Serializable - not supported

Mixed isolation levels are not recommended in ScaleArc. All nodes in a cluster should be configured with the same isolation level. 

Default is OFF. Turn ON to enable the feature. If you need to configure advanced rules, contact support@scalearc.com. 

Transaction Pattern

Follow the steps below to configure a transaction pattern which will decide the behavior for caching, load balancing, and read ignore for transactions. You can also enable/disable a pattern.

  1. Click on the gear icon to add/configure patterns for the databases.

     
     
  2. Enter the patterns.

     

  3. Complete the entries as follows:

    Field/Button
    Description
    User input
    ADD Pattern button Opens the related screen. Create a pattern and ensure that it's entered in the order in which you want it executed.  
    Order Specifies the order in which the query should be executed. Enter the order.
    Rules Enter the pattern's regular expression. Enter the rule.
    Time To Live Defines when the cache expires.  Enter a value when you create a pattern.
    Read Ignore This option allows ScaleArc to treat matching SQL statements as write instead of read. This applies only in transaction. Select enabled/ disabled
    Cache This option allows ScaleArc to perform caching of matching SQL statements in tranasaction. Select enabled/ disabled
    LB This option allows ScaleArc to perform load-balancing of matching SQL statements in transaction. Select enabled/ disabled
    Enabled Disables or enables a transaction rule from executing, depending on its state. You can enable or disable this option during or after you create the pattern. The default is enabled. 
    Delete icon You can also click on the delete icon to delete the cache rule.  Click icon to delete

    It is important to include only those read statements in transaction that are not expected to fetch frequently-changing data. Frequently changing SQL statements will get load balanced in a manner where ScaleArc will fetch data from read server as 'READ-COMMITTED' only. For such statements, use pattern management to keep it out of load balancing.

Write Ignore Rules

Follow the steps below to configure stored procedure which will exclude certain MySQL SQL statements from being detected as write statements. 

  1. Click on the Write Ignore Rules tab.

     
     
  2. Click on Add Rule to add Write Ignore rules for the database. Click on Save when done. 

     
     
  3.  Click on List Stored Procedures to view the list of Write Ignore rules created. 

Unstick Query

Query patterns created here are matched against each SQL statement. If matched, connections will unstick from the existing server connection.

  1. Click on the Unstick Query tab.

     
     
  2. Click on Add Rule to create a query pattern for an unstick query. Click on Save.

Permalink: https://support.scalearc.com/kb/articles/4405
  • Tags
  • patterns
  • Transaction
  • Transaction Patterns

Rate the quality of this page

This page was helpful :) :( This page was not helpful

6 of 12 people found this page helpful


Quick Jump
  • ScaleArc Customer Support
  • Knowledgebase
  • Downloads
  • Feedback
  • Contact Us
Top
Helpdesk software provided by DeskPRO