Hunter的大杂烩 技术学习笔记

November 13, 2010

可用的mysql-proxy配置

Filed under: 技术话题 — hunter @ 2:48 am

mysql-proxy自带的脚本有些问题,用mysqlslap稍微压一下就会崩溃,下面是经过简单修改的版本,在mysqlslap大并发情况下,依然能提供服务。后端使用连接池,避免过大的并发直接压到mysql server上。

mysql-proxy经过简单测试后,发现基本不能用于带事务的环境下,对于业务较为简单的网站,可以尝试。

ro-pooling.lua

— modify by hunter, 2010-10-25


— a flexible statement based load balancer with connection pooling

— * build a connection pool of min_idle_connections for each backend and
—   maintain its size
— * reusing a server-side connection when it is idling

— config

— connection pool
local min_idle_connections = 1
local max_idle_connections = 2
local max_connections_per_server = 3

— debug
local is_debug = false

— end of config


— read/write splitting sends all non-transactional SELECTs to the slaves

— is_in_transaction tracks the state of the transactions
local is_in_transaction = 0


— get a connection to a backend

— as long as we don’t have enough connections in the pool, create new connections

function connect_server()
 — make sure that we connect to each backend at least ones to
 — keep the connections to the servers alive
 —
 — on read_query we can switch the backends again to another backend

 if is_debug then
  print()
  print(“[connect_server] “)
 end

 local least_idle_conns_ndx = 0
 local least_idle_conns = 0

 — lua里面,一个table(类似php的array),是从1开始索引的
 — proxy.global.backends[N]应该是一个 mysql server
 for i = 1, #proxy.global.backends do
  local s        = proxy.global.backends[i]
  local pool     = s.pool — we don’t have a username yet, try to find a connections which is idling
  local cur_idle = pool.users[“”].cur_idle_connections  — 是一个user=xx 的queue, cur_idle_connections 是个整数

  if is_debug then
   print(”  [“.. i ..”].connected_clients = ” .. s.connected_clients)
   print(”  [“.. i ..”].idling_connections = ” .. cur_idle)
   print(”  [“.. i ..”].type = ” .. s.type)
   print(”  [“.. i ..”].state = ” .. s.state)
  end

  if s.state ~= proxy.BACKEND_STATE_DOWN then
   — try to connect to each backend once at least
   — 找到第一个backend server,若无空闲connection,则分配一个
   if cur_idle == 0 then
    proxy.connection.backend_ndx = i
    if is_debug then
     print(”  [“.. i ..”] open new connection”)
    end
    return
   end

   — try to open at least min_idle_connections
   — 遇到第一个backend,记录backend indx及本backend存在的idling_connection数
   — 若本backend 的空闲 idle connection比最小要求还少,或者
   —   本backend 的空闲 idle connection比其他backend connection要少,则记录backend indx及本backend存在的idling_connection数
   if least_idle_conns_ndx == 0 or
      ( cur_idle < min_idle_connections and         cur_idle < least_idle_conns ) then     least_idle_conns_ndx = i     least_idle_conns = s.idling_connections    end   end  end     -- 找到最少 idle connection的backend,赋值给proxy.connection.backend_ndx,准备在这个backend上创建新的connection  if least_idle_conns_ndx > 0 then
  proxy.connection.backend_ndx = least_idle_conns_ndx
 end

 if proxy.connection.backend_ndx > 0 then
  local s = proxy.global.backends[proxy.connection.backend_ndx]
  local pool     = s.pool — we don’t have a username yet, try to find a connections which is idling
  local cur_idle = pool.users[“”].cur_idle_connections

  if cur_idle >= min_idle_connections then
   — we have 4 idling connections in the pool, that’s good enough
   if is_debug then
    print(”  using pooled connection from: ” .. proxy.connection.backend_ndx)
   end
 
   return proxy.PROXY_IGNORE_RESULT
  end
 end

 if is_debug then
  print(”  opening new connection on: ” .. proxy.connection.backend_ndx)
 end

 — open a new connection
end

— 如果back_end connection过多,则直接关闭
function read_handshake()
 if is_debug then
  print(”  backend server index : ” .. proxy.connection.backend_ndx)
 end
    if proxy.connection.backend_ndx and proxy.connection.backend_ndx > 0 then
     local s = proxy.global.backends[proxy.connection.backend_ndx]
   if is_debug then
   print(”  backend server has connected client : ” .. s.connected_clients)
  end
  if s.connected_clients > max_connections_per_server then
       –if is_debug then
       print(”  backend server has too many connection:” .. s.connected_clients .. ” force close:” .. proxy.connection.client.src.name)
      –end
      proxy.response = {
       type = proxy.MYSQLD_PACKET_ERR,
       errmsg = “too many connection, force close”,
       errno = 2006,
       sqlstate= “HY000”
      }
            return proxy.PROXY_SEND_RESULT
  end
    end
end


— put the successfully authed connection into the connection pool

— @param auth the context information for the auth

— auth.packet is the packet
function read_auth_result( auth )
 if is_debug then
  print(“[read_auth_result] ” .. proxy.connection.client.src.name)
 end
 if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
  — auth was fine, disconnect from the server
  proxy.connection.backend_ndx = 0
 elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
  — we received either a
  —
  — * MYSQLD_PACKET_ERR and the auth failed or
  — * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
     if is_debug then
      print(“(read_auth_result) … not ok yet”);
     end
 elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
  — auth failed
 end
end

— read/write splitting
function read_query( packet )
 if is_debug then
  print(“[read_query]”)
  print(”  authed backend = ” .. proxy.connection.backend_ndx)
  print(”  used db = ” .. proxy.connection.client.default_db)
 end

 if packet:byte() == proxy.COM_QUIT then
  — don’t send COM_QUIT to the backend. We manage the connection
  — in all aspects.
  proxy.response = {
   type = proxy.MYSQLD_PACKET_OK,
  }

  return proxy.PROXY_SEND_RESULT
 end

 if is_debug then
  if string.byte(packet) == proxy.COM_QUERY then
   print(“we got a normal query: ” .. string.sub(packet, 2))
  end
 end

 if proxy.connection.backend_ndx == 0 then
  — we don’t have a backend right now
  —
  — let’s pick a master as a good default
  for i = 1, #proxy.global.backends do
   local s = proxy.global.backends[i]
   local pool     = s.pool — we don’t have a username yet, try to find a connections which is idling
   local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections
   
   if cur_idle > 0 and
      s.state ~= proxy.BACKEND_STATE_DOWN then
    proxy.connection.backend_ndx = i
    break
   end
  end
 end
 
 — 如果可用connection已用完,则断开链接
 if proxy.connection.backend_ndx == 0 then
  proxy.response = {
   type = proxy.MYSQLD_PACKET_EOF,
   errmsg = “hunter test”,
  }
 
  –if is_debug then
   print(”  (kill) current connection   = ” .. proxy.connection.client.src.name)
  –end

  return proxy.PROXY_SEND_RESULT
 end

 if true or proxy.connection.client.default_db and proxy.connection.client.default_db ~= proxy.connection.server.default_db then
  — sync the client-side default_db with the server-side default_db
  proxy.queries:append(2, string.char(proxy.COM_INIT_DB) .. proxy.connection.client.default_db, { resultset_is_needed = true })
  if is_debug then
   print(”  sync db  = ” .. proxy.connection.client.default_db)
  end
 end
 proxy.queries:append(1, packet)
 if is_debug then
  print(” end read_query ” .. proxy.connection.backend_ndx)
 end

 return proxy.PROXY_SEND_QUERY
end


— as long as we are in a transaction keep the connection
— otherwise release it so another client can use it
function read_query_result( inj )
    –[[ do nothing , 因为我们的client没有长链接
 local res      = assert(inj.resultset)
   local flags    = res.flags

 is_in_transaction = flags.in_trans

 if not is_in_transaction then
  — release the backend
  proxy.connection.backend_ndx = 0
 end
 –]]
 if is_debug then
  if inj.resultset.rows then
     rows_count = #inj.resultset.rows
  else
     rows_count = 0
  end
  print(“read_query_result ” .. rows_count)
  if inj.resultset.rows then
   for row in inj.resultset.rows do
    print(“injected query returned: ” .. row[1])
   end
  end 

 end
 if inj.id ~= 1 then
  — ignore the result of the USE
  return proxy.PROXY_IGNORE_RESULT
 end

end


— close the connections if we have enough connections in the pool

— @return nil – close connection
—         IGNORE_RESULT – store connection in the pool
function disconnect_client()
 if is_debug then
  print(“[disconnect_client]” .. proxy.connection.backend_ndx)
 end

    — 重置待关闭的backend_ndx
    proxy.connection.backend_ndx = 0;
 if proxy.connection.backend_ndx == 0 then
  — currently we don’t have a server backend assigned
  —
  — pick a server which has too many idling connections and close one
  for i = 1, #proxy.global.backends do
   local s = proxy.global.backends[i]
   local pool     = s.pool — we don’t have a username yet, try to find a connections which is idling
   local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections

   if s.state ~= proxy.BACKEND_STATE_DOWN and
      cur_idle > max_idle_connections then
    — try to disconnect a backend
    proxy.connection.backend_ndx = i
    if is_debug then
     print(”  [“.. i ..”] closing connection, idling: ” .. cur_idle)
    end
    return
   end
  end
 end
end

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress