MySQL Proxy RW Splitting

Skip to end of metadata
Go to start of metadata

Read/Write Splitting

The MySQL Replication allows to scale out read-load.

To use it nicely your application has to be aware of reading data from slaves and writing data to the master. Read/Write splitting wants to automate that process inside the MySQL Proxy.

In

a Proof of Concept of that idea is presented.

Limitations

There are a few problems left before that feature is in a acceptable state:

Connection State

Up to now the example script only knows about:

  • transactions go to the master
  • auto-increment INSERTs will wait for the next SELECT INSERT_ID()
  • SELECT SQL_CALC_FOUND_ROWS will wait for the next SELECT FOUND_ROWS()
  • LOCK TABLES

Currently most of the other commands that change the connection state are ignored.

  • SET NAMES utf8
  • CREATE TEMPORARY TABLE ...
  • SET @a = 1; SELECT @a;
  • Stored Procedures
  • SHOW SESSION STATUS

The rw-splitting.lua script has to be extended to track those statements and either:

  • replicate the state to the new connection (SET NAMES utf8)
  • don't switch the connection

Connection Pool

The MySQL Proxy maintains a connection pool if the scripting layer disconnects the backend from the client connection:

proxy.connection.backend_ndx = 0

The backend connection can be taken from the pool by other client connections afterwards.

For the R/W-Splitting you need 2 open connections for each client connection:

  • one to the master
  • one to the slave

As the authentication protocol never transmits the real password nor anything we can use to authenticate against another server, we need a internal storage of credentials to open connections to the backend-servers when we are short of connections in the pool.

That's currently missing.

Bugs

Yes, we know that RW-Splitting is not for production use yet, it is a Proof of Concept. To keep track of the known issues:

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Sign up or Log in to add a comment or watch this page.


The individuals who post here are part of the extended Oracle community and they might not be employed or in any way formally affiliated with Oracle. The opinions expressed here are their own, are not necessarily reviewed in advance by anyone but the individual authors, and neither Oracle nor any other party necessarily agrees with them.