dded by Keith Larson, last edited by Keith Larson on Jul 25, 2012 (view change)

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

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

ITPub博客

MySQL Proxy RW Splitting

Read/Write Splitting

Limitations

Connection State

Connection Pool

Bugs

最新文章