Read/Write Splitting with MySQL-Proxy
Posted by
Gavin Terrill
on
Oct 17, 2007
- Architecture
- Topics
-
Clustering & Caching
,
-
Performance & Scalability
- Tags
-
MySQL
,
-
LAMP
MySQL-Proxy,
announced in June, is a binary application that sits between your MySQL client and server, and supports the embedded scripting language
Lua. The proxy can be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:
One of the more powerful features of MySQL Proxy is the ability to do "
Read/Write Splitting". The basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. Replication is used to synchronize the changes due to transactional queries with the slaves in the cluster.

Jan Kneschke writing about the technique in "
MySQL Proxy learns R/W Splitting", discusses connection pooling:
For R/W Splitting we need a connection pooling. We only switch to another backend if we already have a authenticated connection open to that backend.The MySQL protocol first does a challenge-response handshake. When we enter the query/result stage it is too late to authenticate new connections. We have to make sure that we have enough open connections to operate nicely.
The LUA script to handle read/write splitting is straightforward:
-- read/write splitting
--
-- send all non-transactional SELECTs to a slave
if is_in_transaction == 0 and
packet:byte() == proxy.COM_QUERY and
packet:sub(2, 7) == "SELECT" then
local max_conns = -1
local max_conns_ndx = 0
for i = 1, #proxy.servers do
local s = proxy.servers[i]
-- pick a slave which has some idling connections
if s.type == proxy.BACKEND_TYPE_RO and
s.idling_connections > 0 then
if max_conns == -1 or
s.connected_clients < max_conns then
max_conns = s.connected_clients
max_conns_ndx = i
end
end
end
-- we found a slave which has a idling connection
if max_conns_ndx > 0 then
proxy.connection.backend_ndx = max_conns_ndx
end
else
-- send to master
end
return proxy.PROXY_SEND_QUERY
Jan notes that the technique can also be used to implement other data distribution strategies, such as sharding.
No comments
Watch Thread Reply