In http://jan.kneschke.de/2007/8/1/mysql-proxy-learns-r-w-splitting we did a first implementation on idea of sending non-transactional reads to the slaves and writes and transactions to the master.
While that was mostly a proof of concept it already worked pretty well. In this second round on R/W splitting several issues are taken care of:
SELECT SQL_CALC_FOUND_ROWS… +
default_dbnot in sync between client and backend
These changes should make Read/Write splitting more robust and make it a drop-in for more users.
Up to now we used the
tutorial-keepalive.lua to implement the Read/Write-splitting. As the name says, it is a tutorial for connection keepalive, while rw-splitting is far more complex. To address this the rw-splitting is now a separate script in th
lib/ folder which uses a set of base-classes we ship now.
$ cd trunk/ $ LUA_PATH="lib/?.lua" ./src/mysql-proxy \ --proxy-lua-script=./lib/rw-splitting.lua \ --proxy-read-only-backend-addresses=:3307 \ --proxy-read-only-backend-addresses=:3308 \ --proxy-read-only-backend-addresses=:3309
To start it we have to set
LUA_PATH for now, you tell where it find the standard-proxy libs.
By default the rw-splitting script will spit out a lot debug information about the different stages. You can either disable it in the script:
-- debug proxy.global.config.is_debug = true
or by sending:
set GLOBAL rwsplit.debug = 0
on a connection.
[connect_server] .connected_clients = 0 .idling_connections = 0 .type = 1 .state = 0  open new connection [read_query] current backend = 0 client default db = repl client username = root query = select * from foo sending to backend : 127.0.0.1:3306 is_slave : false server default db: repl server username : root in_trans : false in_calc_found : false COM_QUERY : true
One of the basic problems with rw-splitting is that each connection has a state, e.g. the
default_db. If you switch to another backend you have to make sure that before we issue a SQL query, that also set the new default-db, if they are not in sync.
[read_query] current backend = 0 client default db = mysql client username = root query = select * from user server default db: repl client default db: mysql syncronizing
The client-side did a
USE mysql against the master and wanted to
SELECT from the a slave afterwards. As the connection the slave was still using
repl from the previous query we have apply the DB-change now. For achieve this we insert a
USE mysql before sending the
SELECT to the slave.
In case the DB want to switch to doesn’t exist on the slave, you will get an error like:
ERROR 1000 (00S00): can't change DB 'norepl' to on slave ':3307'
for the SELECT statement.
Not all statements in MySQL are stateless and allow easy R/W splitting. Some of them need special support to make sure that still work:
SELECT SQL_CALC_FOUND_ROWS ...will lead to a
SELECT FOUND_ROWS()which has to be executed on the same connection
INSERTing into table with
auto_incrementfields might lead to a
SELECT LAST_INSERT_ID()which has to be execute on the same master-connection.
SHOW WARNINGSis similar
The solution is simple: don’t give away the connection in that cases and don’t send the
SELECT FOUND_ROWS() to a slave. For sure there are some more statements which might not harmonize with r/w splitting. For example prepared statements. But that’s another story.