mysql r w splitting_MySQL Proxy RW Splitting

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: