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:
Jan Kneschke writing about the technique in "MySQL Proxy learns R/W Splitting", discusses connection pooling:
The LUA script to handle read/write splitting is straightforward:
- load balancing and fail over handling
- query analysis and logging
- SQL macros
- query rewriting
- executing shell commands
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 splittingJan notes that the technique can also be used to implement other data distribution strategies, such as sharding.
--
-- 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