Read/Write Splitting with MySQL-Proxy

Posted by Gavin Terrill on Oct 17, 2007

Sections

Development,

Architecture & Design

Topics

Architecture ,

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:

• load balancing and fail over handling
• query analysis and logging
• SQL macros
• query rewriting
• executing shell commands

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.

• Older >