MySQL Proxy RW Splitting
Contents |
[edit] 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
- http://jan.kneschke.de/projects/mysql/mysql-proxy-learns-r-w-splitting
- http://jan.kneschke.de/projects/mysql/mysql-proxy-more-r-w-splitting
a Proof of Concept of that idea is presented.
[edit] Limitations
There are a few problems left before that feature is in a acceptable state:
[edit] 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
[edit] 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.
[edit] 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: