pero on anything

LuaSQL fetches results about 15% faster than MySQL Proxy?

While 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:

require("luasql.mysql")
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:


function read_query(packet)
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:


> $ mysqlslap -h 127.0.0.1 -P 4040 --auto-generate-sql --number-of-queries=10000
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:


> $ mysqlslap -h 127.0.0.1 -P 4040 --auto-generate-sql --number-of-queries=10000
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:

> $ mysqlslap -h 127.0.0.1 -P 4040 --auto-generate-sql --number-of-queries=10000
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 -> ....

3 Responses to “LuaSQL fetches results about 15% faster than MySQL Proxy?”

  • Jan Kneschke says:

    How many boxes are involved in this benchmark ? Is it all on one box ?

    The proxy does only provide iterator to the result-set and the rows are converted one by one to lua if the iterator is called. What did gave you the impression it is covered to LUA completely ?

  • Kay Roepke says:

    I’ve tried to reproduce your findings, but wasn’t able to.
    In fact, for me the “plain” MySQL Proxy version was consistently about 1.6x faster (I have not profiled the code yet, so I can’t currently say where most of the time was spent in each case).
    For my simple test I’ve run everything locally on an 8-core box with 5.1.23rc and our internal proxy code (which for all intents and purposes is the 0.7.0 code in SVN).

    As Jan already commented, only iterating the entire resultset will copy it onto the Lua stack, but either way the time necessary to copy it all in one piece or row by row should roughly be the same (of course there would be less function call overhead when copying it all at once, so that approach should actually be faster). It’s all academic, though, because Proxy doesn’t do it :)

    My first suspicion was that LuaSQL would not buffer the resultset like Proxy currently does, but it does: It uses mysql_store_result() from the C client lib which buffers the entire response from the server. So that would have the same memory overhead as Proxy has. Without digging even deeper I’m really at a loss as to what causes the performance discrepancy you see.

    Another comment on the use of LuaSQL here: In the face of higher concurrency this approach would severely limit the throughput, because the retrieval in read_query would block the connection. As such the Proxy couldn’t serve other clients while it’s waiting on the server response (because it doesn’t go through the libevent handling, but opens a socket of its own).

  • pero says:

    Jan, Kay, thanks for your comments.

    First of all, my assumptions MySQL Proxy is putting the whole result on LUA stack at once is false, indeed. I corrected that.

    I reproduced the tests above on an 8-core-server with 8GB RAM putting the database on another server and using HEAP tables. Then the performance of both MySQL Proxy and LuaSQL was nearly equal, MySQL Proxy being about 2-5% faster. I was not able to see a performance gap of factor 1.6 – perhaps your “internal” 0.7 version has some other improvements? BTW: When is it going to be in SVN?

    Kay, thanks for the pointer to LuaSQL + MySQL Proxy issues in high concurrency. I quickly tried to verify that by using “-c 10″ switch in mysqlslap. But I could not see a drop in the LuaSQL performance.

    As said above, I am planning to use LuaSQL only for configuration purposes.

    Greetings

    Peter

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">