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