Tungsten Clustering: Choosing the Best Read/Write Splitting Option

Forewords: Who Really Needs Read/Write Splitting?

Let’s spoil the myth right away: read/write splitting is not the holy grail to performance!

It’s a wrong shortcut to state that read/write splitting will improve performance. It definitely can, but there are several factors to consider and there is no simple recipe or formula to it, the right answer can only be found through testing and performance comparison.

Let’s take a few examples to illustrate the above:

• Application reads heavily, writes rarely or through batches overnight.
  ☑ Reading from replicas will spread the load over multiple MySQL instances. CPUs and network load will be balanced and performance will probably improve.
• Application has a high number of writes, few short reads, and primary CPU is not overloaded.
  ♦ Additional computation to determine whether the requests should be sent to a replica or primary might just overload the application or the proxy itself. Sending all the traffic directly to the primary will probably be more efficient.
• Heavy writes with complex and time-consuming reads.
  ☑ Asking replicas to handle the read-load could unload the primary, definitely worth trying.
• Low number of writes, many very small reads.
  ♦ This requires testing as well. The time to inspect the read statements might outweigh the benefit of sending them to a replica. If the application can do its own r/w splitting, then it will probably be worth trying to send the reads to replicas.

Try It With Tungsten Connector Proxy!

If you read this far, you probably want to set up a test in your own environment, with a load that would be as close as possible to your production load.

Among the various read/write splitting options that our database proxy offers, which one should you choose?

Here are a few questions you can use as a guideline:

Can Your Application Be Modified?

You have a hand on the application itself, and are able to:

• Change the code?
  When your application is about to do a read, you can add comments to the SQL statement like in the following example:

  /* TUNGSTEN USE qos=RO_RELAXED */ SELECT * FROM TABLENAME

  
  Documentation here
• Modify or use a specific host or port for reads?
  When using a framework or application that allows it, Tungsten Connector can be setup to listen on a different address or port and dedicate one to read from replicas.
  
  Host-based read/write splitting documentation
  Port-based read/write splitting documentation
• Modify or use a specific connection-URL for reads and connect to a different database-name?
  You just need to append a string to the name of the database schema you want to connect to:

  jdbc:mysql://connector-host1:3306/mydb@qos=RO_RELAXED&autoreconnect=true

  
  Documentation found here.

You Cannot Modify Your Application Code or Connection Strings?

Tungsten Connector can parse the SQL statements it receives and detect read requests automatically. However, it will only be able to split the read-only load outside transactions:

• Your application uses autocommit?
  We have 2 solutions that will allow r/w splitting. The following choice will balance performance against data “age”:
  • Can you afford to read data that is as old as the replication lag? (generally a few seconds).
    Direct-reads r/w splitting will send every request to the most up-to-date replica, which can still be a bit behind. So your application might not read what has just been written. This won’t affect applications that write rarely or only through periodic batches, but will confuse the ones that expect to see their own writes right away.
  • Need to read up-to-date data?
    SmartScale feature is powerful enough to select replicas that are up-to-date with your application writes. It requires a bit more CPU to check the progress of the replicas but will overcome the limitations of the direct-reads feature above.
• If your application uses transactions (begin/<statements>/commit) you cannot use automated read/write splitting.
  However, bridge mode is really fast, which is why we enable it by default. As stated in the foreword, even if you send all traffic to the primary, testing might actually prove it faster than any kind of read/write splitting, just because it’s simple, thus beautiful :)

Conclusion

Let’s follow the saying “a picture is worth a thousand words”: