Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ


Choose your language

InfoQ Homepage News Read/Write Splitting with MySQL-Proxy

Read/Write Splitting with MySQL-Proxy

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

    -- we found a slave which has a idling connection
    if max_conns_ndx > 0 then
      proxy.connection.backend_ndx = max_conns_ndx
    -- send to master

  return proxy.PROXY_SEND_QUERY
Jan notes that the technique can also be used to implement other data distribution strategies, such as sharding.

Rate this Article