Prepared statement API design

[ppolv]

Hi, first, thanks for all this work on openresty!, it is very useful :). And sorry for the large comment :)

I'm considering using it for replacing some parts of our REST API, and could implement it in short time, with straightforward code, and that was able to outperform by a significant margin our current implementation that do have a lot of quirks on the code to gain speed, so: great :)

It would be even better, for our use case, if we could use prepared statements instead of building the query string each time. We tend to have a small number of different queries, that runs thousands time per second in response to client requests. In that scenario server-side prepared statements are great:

• The mysql sever doesn't need to parse the query each time
• We don't need to generate the query, nor to escape the parameters each time
• To retrieve the results the binary protocol is used, that is lighter to both us and mysql

I implemented support for prepare/execute/close prepared statements, that implementation itself was not difficult, mainly a new function to parse rows, and set it or the text-based one as callback depending if the response is for a textual query or for a prepared_statement execute() one.

1. Would you be interesting in adding this to lua-resty-mysql ? I can work on this and prepare a patch

2. How the API for this should looks like?. For testing I used: st = db:prepare("select . from .. where col1 = ? and col2 = ?") result = st:call("Value1", "Value2") st:close()

3. And this is the important one: I don't want to prepare/execute/close the statement each time. That is likely more resource intensive than just sending a plain text query. Instead, want to somehow keep with each mysql connection the set of prepared statements on it, so we could do things like: local st = db:prepared("st_1") if not st then st = db:prepare("st_1", "some query....") end

Mysql assign an id to each prepared statement, that you use for calling it. And that id is per-connection, so statement "st_1" could be 7 on one connection, and 3 on another one.

I failed to find an easy way to "attach" info to a mysql connection. I'm right that probably need to be actually attached to the cosocket object?. I couldn’t find any obvious way to do that neither, as new tables are created and returned each time, no matter the socket is comming back from the pool. I tried to cheat and use the c-data in the cosocket as key in a weak table, but that didn't work , although not really sure I was using it right.

Without 3) , adding prepared statement is not that useful (might be for security, as you can't forget to escape a parameter, or might be preferable too if the result set is big enough than the cost of prepare/execute/close is less than the speed gained by using binary rather than textual protocol for results) Cheating, just for testing, and using only 1 prepared statement, and assuming it will be assigned id "1" on all sessions by mysql, I was able to test how well this behave, and by reusing the prepared statement for a simple query that returns 1 row, the throughput increased significantly (>50% ), while mysql server used less CPU.

So, what's your opinion?, would be interested?, if so, how you think the API should looks like?. It is point 3) possible with current cosocket/mysql design?

[agentzh]

@ppolv Yes, prepared stmt support is a high priority item on my TODO list. And I appreciate your comments here.

You are right that the statement handle need a way to attach to the underlying connection in ngx_lua's cosocket API. Maybe we can add something like a binddata() method and a getbounddata() to the cosocket object? We can limit the value type to an integer in our first implementation and extend that if future requirements arise. What do you think? Will you contribute a patch to ngx_lua? Thanks!

[ppolv]

Thanks, will check the ngx_lua code, I don't really know about the lua<>c interface, so will need to learn that first. Probably will find time to work on this on next week, will keep you posted if I make progress

[agentzh]

@ppolv Very much appreciated :)

[ppolv]

I started with the binddata() and getbounddata(), on https://github.com/ppolv/lua-nginx-module/commit/3d0bbed92f73571aa79474d4ea416b22a9bffbd8

Not really sure if I'm writing the tests correctly, specially to check if I'm freeing the data correctly when the sockets are closed.

I went to use a full lua table (if needed) to store the bind data, as thought that allowing only integers values was going to be too limiting and make developers tasks more complex. Do you think is ok?

[agentzh]

@ppolv Hmm, I don't like the extra complexity involved with supporting arbitrary Lua values here. I'd rather make binddata support numbers only at least for now.

The user can always map her values to a Lua number via something like this in her own Lua code anyway: https://github.com/openresty/lua-resty-lock/blob/master/lib/resty/lock.lua#L27

[ppolv]

ok, not sure how to do that actually :) like with a c-side tree | linked list?. Will not touch that for now, will try to continue work on mysql side.

With only integers I was worried there won't be a way to "free" the user-mapped value to it when the socket is closed by nginx (idle timeout, request crash before returning it to pool, whatever). But probably there is and I'm not thinking on it right. Or might be one doesn't really need that, don't know.

again, thanks for your time!

[agentzh]

@ppolv Yes, you're right about the cleanup part. I missed that.

Okay, I've given it a second thought. It does not look too bad for supporting arbitrary Lua values out of the box. Maybe you can relax the check for the value type? Right now it allows Lua strings only, it seems?

Also, will you create a pull request for it? In case you're not familiar with the process, please check out this document: https://help.github.com/articles/creating-a-pull-request/ Thanks!

[ppolv]

Yes, was forcing only string keys, but indeed that was just arbitrary. Will remove it and create the pull request this week, thanks!.