lua-resty-mysql and the cost of a blocking database call in nginx

The whole point is that the query does not stop the worker

lua-resty-mysql is a Lua MySQL client driver for the ngx_lua nginx module, and the README states the design constraint in one sentence: it takes advantage of ngx_lua's cosocket API, which ensures 100 percent nonblocking behavior.

That is the reason to use this driver rather than a general purpose Lua MySQL client. Inside nginx, the standard MySQL libraries rely on blocking sockets and blocking DNS, which stalls the worker and, in a busy server, eats through the available workers. A cosocket is nginx's own socket abstraction, and the driver is written against it, so a slow query yields to the event loop instead of parking a worker.

The README describes the library as production ready, and the API is shaped around that event model. Methods return immediately with a result or an error, and the pattern throughout the documentation is to check the return value, report the error, and close the connection.

new, connect and set_timeout are the whole handshake

The first three calls are the setup, and the README's synopsis shows them together. A connection object is created with `db, err = mysql:new()`, which returns `nil` and a string describing the error on failure. Then `db:set_timeout(1000)` sets the operation timeout, one second in that example.

The connect step needs the package on the Lua path. The synopsis opens with the line you only need outside the OpenResty bundle:

    # you do not need the following line if you are using
    # the ngx_openresty bundle:
    lua_package_path "/path/to/lua-resty-mysql/lib/?.lua;;";

and then, inside the nginx location handler, the object is created and the timeout set:

                local mysql = require "resty.mysql"
                local db, err = mysql:new()
                if not db then
                    ngx.say("failed to instantiate mysql: ", err)
                    return
                end

                db:set_timeout(1000) -- 1 sec

The `syntax: ok, err, errcode, sqlstate = db:connect(options)` line in the methods section tells you what a successful call looks like. Four return values, of which the last two are the MySQL error code and the SQL state, and both are worth logging because the error string alone rarely identifies the problem.

connect takes a unix socket path as well as a host and port

The `options` table for `connect` has a short, documented set of keys. `host` is the host name of the MySQL server, `port` is the port it listens on and defaults to 3306, `path` is the path of the unix socket file listened by the MySQL server, `database` is the database name, `user` and `password` are the account credentials, with the password passed in clear text, and `charset` sets the character set for the connection, which can differ from the server default.

The interesting option is `path`. The README's synopsis shows the socket form commented out beside the TCP form, and for a deployment where MySQL and nginx sit on the same host, connecting over the unix socket avoids the TCP stack entirely. The two forms are alternatives, not a fallback chain: the driver uses whichever you give it.

The connect call as documented:

                local ok, err, errcode, sqlstate = db:connect{
                    host = "127.0.0.1",
                    port = 3306,
                    database = "ngx_test",
                    user = "ngx_test",
                    password = "ngx_test",
                    charset = "utf8",
                    max_packet_size = 1024 * 1024,
                }

The charset list in the README is explicit and finite: `big5`, `dec8`, `cp850`, `hp8`, `koi8r`, `latin1`, `latin2`, `swe7`, `ascii`, `ujis`, `sjis`, `hebrew`, `tis620`, `euckr`, `koi8u`, `gb2312`, `greek`, `cp1250`, `gbk`, `latin5`, `armscii8`, `utf8`, `ucs2`. `utf8mb4` is not on that list, which matters for any schema storing emoji or four byte characters.

query returns affected_rows and insert_id, not just a result set

The `query` method covers the common case, and the method list in the table of contents shows that the finer grained operations are available too: `send_query` and `read_result` are separate methods, and there is also `server_ver`, `set_compact_arrays` and `get_reused_times`.

The synopsis shows what a query returns. For a statement that changes data, the result carries `affected_rows` and `insert_id`, and the README prints them together after an insert:

                ngx.say(res.affected_rows, " rows inserted into table cats ",
                        "(last insert id: ", res.insert_id, ")")

For a select, the result is a table of rows and the README encodes it with cjson to inspect it. The select call in the example passes a second argument of 10, which is the number of rows expected in the result set, and the comment above it says the result is expected to be about ten rows. The `set_compact_arrays` method in the method list is there for callers who want the array representation of rows tuned differently.

Every query in the synopsis follows the same three line error check, and the pattern is worth copying into your own code rather than shortening, because the error string alone will not tell you which statement failed.

set_keepalive returns the connection to a pool instead of closing it

After the work is done, the connection has to go back somewhere. The synopsis shows the pooling call with a size and an idle timeout:

                local ok, err = db:set_keepalive(10000, 100)
                if not ok then
                    ngx.say("failed to set keepalive: ", err)
                    db:close()
                    return
                end

The README describes this as putting the connection into a pool of size 100 with a 10 seconds max idle timeout. The alternative, shown commented out in the same example, is `db:close()` to drop the connection immediately.

This is the piece that decides whether the driver is fast in practice. A new TCP connection and a MySQL handshake per request is expensive, and the connection pool amortises it across requests handled by the same worker. The `get_reused_times` method in the method list exists to observe that, returning how many times a connection has been reused.

The pool is per worker process, which is also why `set_timeout` matters so much. A worker holding a pooled connection that has gone stale will find out at the next query, and the timeout you set is the bound on how long that worker sits there.

Version floors and two dependencies that are easy to miss

The README sets a hard floor: at least ngx_lua 0.9.11 or ngx_openresty 1.7.4.1 is required. That is not a soft recommendation, since the driver is built on the cosocket API and older versions do not provide what it needs.

The second dependency is the bit library, which the README also marks as required. If you are using LuaJIT 2 with ngx_lua, the `bit` library is already available by default, which covers most OpenResty installations.

The method list and the table of contents also hint at what else the library covers. There are sections for SQL literal quoting, multi-resultset support, debugging, automatic error logging and more authentication method support, so the library handles multiple statements per connection and can log errors on its own.

The README is an older style document, with the full method reference inline rather than on a separate documentation site, and the repository is small: `lib/` holds the Lua modules, `t/` holds the tests, and the top level has a `Makefile`, a `dist.ini`, `.travis.yml`, a `valgrind.suppress` file, and `server.crt` and `server.key`.

Installing from the Makefile and running the Test::Nginx suite

The Makefile in the repository root installs the Lua modules into the standard library path. The target runs `install` for `lib/resty/*.lua` and `lib/resty/mysql/*.lua` under `$(LUA_LIB_DIR)/resty` and `$(LUA_LIB_DIR)/resty/mysql`, where `LUA_LIB_DIR` defaults to `$(PREFIX)/lib/lua/$(LUA_VERSION)` under `/usr/local`. The `DESTDIR` variable is honoured, so a staged install is possible.

The test target runs the suite with prove against the `t/` directory:

	PATH=$(OPENRESTY_PREFIX)/nginx/sbin:$$PATH prove -I../test-nginx/lib -r t

The `OPENRESTY_PREFIX` variable defaults to `/usr/local/openresty-debug`, and the nginx binary directory is prepended to `PATH` so the tests can find the server they drive. The `test-nginx` dependency is expected as a sibling checkout at `../test-nginx`, which is the Test::Nginx framework. The presence of `server.crt` and `server.key` in the repository root suggests some of the tests exercise a TLS path, though the README text given here does not describe the TLS options in detail.

One licensing point deserves attention before adoption. The repository's licence is listed as unknown, and the README's own table of contents includes a Copyright and License section, but no licence identifier is recorded for the project in the metadata. For a component that many OpenResty deployments vendor into their own trees, that ambiguity is worth resolving with the maintainers rather than assuming.

Editorial conclusion

lua-resty-mysql is a sound choice for Lua code already running as an ngx_lua or OpenResty handler that needs database access without giving up the nonblocking event model of the worker. It is the wrong tool for a standalone Lua application, for a database other than MySQL, and for a deployment that cannot pin the ngx_lua version or the bit library. Verify first that your server satisfies the stated floor of ngx_lua 0.9.11 or ngx_openresty 1.7.4.1, and that the licence situation suits you, since the repository carries no licence that GitHub can classify.

Frequently asked questions

Official sources

1. Issues
2. openresty/lua-resty-mysql on GitHub
3. README