LuaSQL fetches results about 15% faster than MySQL Proxy?
Sunday, September 7th, 2008While evaluating LuaSQL as backend connection replacement I came across this. I did a quick performance test using mysqlslap and it showed that just reading and copying the result can be significantly faster with LuaSQL.
Benchmark details
What I did was just sending the query to the backend and building up a new result-set in LUA.
This is the code for LuaSQL:
local _sqlEnv = assert(luasql.mysql())
local _con = nil
function read_auth(auth)
local host, port = string.match(proxy.backends[1].address, "(.*):(.*)")
– We explicitly connect to db "test" since mysqlslap drops the database
– and LuaSQL needs the db to exists beforehand. Anyway this is just a
– quick tests, so don’t bother.
_con = assert(_sqlEnv:connect("test", auth.username, auth.password, host, port))
end
function disconnect_client()
assert(_con:close())
end
function read_query(packet)
if (packet:byte() == proxy.COM_QUERY) then
local query = packet:sub(2)
local result = nil
local cur = assert(_con:execute(query))
if (type(cur) == "number") then
proxy.response.type = proxy.MYSQLD_PACKET_RAW;
proxy.response.packets = {
"\000" .. – fields
string.char(cur) ..
"\000" – insert_id
}
result = proxy.PROXY_SEND_RESULT
else
– Build up the result set.
local fields = {}
local colNames = cur:getcolnames()
local colTypes = cur:getcoltypes()
for a = 1, #colNames, 1 do
table.insert(fields, {name = colNames[a], type=proxy.MYSQL_TYPE_STRING})
end
local curRow = {}
local rows = {}
while (cur:fetch(curRow)) do
table.insert(rows, curRow)
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
result = proxy.PROXY_SEND_RESULT
end
if (result ~= nil) then
return result
end
end
end
And this is the code for using MySQL Proxy only:
if (packet:byte() == proxy.COM_QUERY) then
local query = packet:sub(2)
– We append the query so read_query_result gets triggered.
proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query)
return proxy.PROXY_SEND_QUERY
end
end
function _read_query_result(inj)
local resultSet = assert(inj.resultset)
local newFields = nil
local fieldCount = 1
local fields = resultSet.fields
if (fields) then
newFields = {}
while fields[fieldCount] do
table.insert(
newFields,
{
type = fields[fieldCount].type,
name = fields[fieldCount].name
}
)
fieldCount = fieldCount + 1
end
end
local newRows = nil
if (resultSet.rows) then
newRows = {}
for row in resultSet.rows do
table.insert(newRows, row)
end
end
if (newFields) then
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = newFields,
rows = newRows
}
}
return proxy.PROXY_SEND_RESULT
end
end
As you can see we do nothing but copy the result-set in LUA. This mimics the result-set aggregation HSCALE does if a full partition scan is necessary.
Results
Using LuaSQL:
Benchmark
Average number of seconds to run all queries: 65.731 seconds
Minimum number of seconds to run all queries: 65.731 seconds
Maximum number of seconds to run all queries: 65.731 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
Using MySQL Proxy only:
Benchmark
Average number of seconds to run all queries: 74.607 seconds
Minimum number of seconds to run all queries: 74.607 seconds
Maximum number of seconds to run all queries: 74.607 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
For comparison: Using empty read_query and read_query_result functions:
Benchmark
Average number of seconds to run all queries: 39.657 seconds
Minimum number of seconds to run all queries: 39.657 seconds
Maximum number of seconds to run all queries: 39.657 seconds
Number of clients running queries: 1
Average number of queries per client: 10000
Versions used: MySQL Proxy 0.7.0 (svn-rev 511), LuaSQL 2.1.1, MySQL server 5.0.51a, mysqlslap 5.1.26rc
Of course I repeated the tests several times to verify the results.
Without digging too deep into the source of both MySQL Proxy and LuaSQL the biggest difference is that LuaSQL pushes the result-set row-by-row onto the LUA-stack whereas MySQL Proxy puts the whole result.
Update: As Jan points out below this is not true. MySQL Proxy puts the result row by row onto th LUA stack, too.
Update #2: The tests above ran on my Notebook (MacBookPro 2.4GHz, 4GB RAM running Ubuntu 8.04 64 bit). They are reproducible. Running the same tests on an 8-core-server putting the MySQL database on another server results in the MySQL Proxy version running slightly faster (about 2-5%) than the LuaSQL version.
Conclusions
Even though this tiny benchmark showed that the speed of LuaSQL seems to be feasible, there are still drawbacks. First of all: Depending on your workload only a fraction of your queries need result-set altering. Namely it’s only full partition scans that need this. Most of the time you just need to change the table name or the backend. And then LuaSQL is 100% slower than MySQL Proxy alone.
Another downside of LuaSQL is that it does not return the mysql field types but only the LUA types. This makes it impossible to build up a correct result-set that can be sent back to the client.
So still we need suitable (for HSCALE) backend connection handling in MySQL Proxy if we want higher performance.
Built-in result-set merging would be a big win, too. Then we could even have streaming combined result-sets taking the memory pressure from the proxy (since every result-set has to be fully loaded into memory).
That said I think about using LuaSQL for configuration handling since it is a lot easier than doing it via proxy.queries:append -> read_query_result -> proxy.queries:append -> ....