lua-resty-mysql aborted connections

[bjoe2k4]

Hello!

i have a problem using the lua-nginx module in connection with lua-resty-mysql. Although i close a connection properly using the db:close() command, mysql aborted connections count keeps increasing with every request on the follwing (reduced) location example:

location = /lua-mysql-test {
    content_by_lua '
        local cjson = require "cjson"
        local mysql = require "mysql"
        local db, err = mysql:new()
        if not db then
            ngx.log(ngx.ERR,"failed to instantiate mysql: ", err)
            return ngx.exit(500)
        end

        db:set_timeout(1000)
        local ok, err, errno, sqlstate = db:connect{
            path = "/var/run/mysqld/mysqld.sock",
            database = "db",
            user = "user",
            password = "password" }
           
        local data = {}    
       
        local ok, err = db:close()
        --local ok, err = db:set_keepalive(10000, 100)
        data.ok = ok
        data.err = err
        ngx.say(cjson.encode(data))
    ';
}

My nginx-lua/lua-resty-mysql/LuaJIT/nginx/mariadb versions are the latest ones, i.e. 0.9.13/0.15/2.1.0-git/1.7.7/10.0.14.

If i use the commented set_keepalive statement, the aborted connections count increases after the keep-alive timeout. The same is true if i used "127.0.0.1" instead of the unix socket.

Is this behavior normal or am i doing something wrong here?

The result of this example is always:

{"ok":1}

Best

[Yichun Zhang (agentzh)]

Hello!

On Sun, Nov 23, 2014 at 12:00 PM, bjoe2k4 wrote:
> i have a problem using the lua-nginx module in connection with
> lua-resty-mysql. Although i close a connection properly using the db:close()
> command, mysql aborted connections count keeps increasing with every request

Have you tried system tools like "netstat" to check the (TCP) state of
your MySQL connections?

Regards,
-agentzh

[bjoe2k4]

When connecting via TCP and setting keepalive, the state is ESTABLISHED.

$ lsof -n -i -U | sed -e '1p' -e '/mysql/!d'
COMMAND     PID    USER   FD   TYPE             DEVICE SIZE/OFF    NODE NAME
nginx     10142    http   35u  IPv4            3157278      0t0     TCP 127.0.0.1:54103->127.0.0.1:mysql (ESTABLISHED)
mysqld    14909   mysql   23u  IPv4            2703699      0t0     TCP 127.0.0.1:mysql (LISTEN)
mysqld    14909   mysql   24u  unix 0xffff880038bd8380      0t0 2703700 /run/mysqld/mysqld.sock type=STREAM
mysqld    14909   mysql   54u  IPv4            3157279      0t0     TCP 127.0.0.1:mysql->127.0.0.1:54103 (ESTABLISHED)

With PHP this doesn't happen, so i'm assuming this isn't a mysql issue. Also the mysql server is mostly idle. :-/

[Yichun Zhang (agentzh)]

Hello!

On Mon, Nov 24, 2014 at 1:27 PM, bjoe2k4 wrote:
> When connecting via TCP and setting keepalive, the state is ESTABLISHED.
>

Isn't this exactly what "set_keepalive" means? That is, putting
(alive) connections into connection pool for later use?

If you don't want too many idle connections, just specify a relatively
short max idle time in the set_keepalive call. See

https://github.com/openresty/lua-resty-redis#set_keepalive

Basically setting the first argument to set_keepalive as 1 sec (that
is, 1000), for example. So that the connection will be automatically
closed if it stays in the pool for too long.

Regards,
-agentzh

[bjoe2k4]

In production i'm setting keepalive, i got the principle of keepalive, but my real problem is different. Whenever a ngx-lua-->mysql connection is closed, the aborted connections counter of my mysql(mariadb) server is rising and i'm wondering why that is the case. Opening and closing a connection via PHP doesn't do that.

[Yichun Zhang (agentzh)]

Hello!

On Mon, Nov 24, 2014 at 2:16 PM, bjoe2k4 wrote:
> In production i'm setting keepalive, i got the principle of keepalive, but
> my real problem is different. Whenever a ngx-lua-->mysql connection is
> closed, the aborted connections counter of my mysql(mariadb) server is
> rising and i'm wondering why that is the case.

The reasons can be one or more of the follows:

1. you set your connection pool too large (the second argument to
set_keepalive).

2. you set your connection's max idle time too longer (the first
argument to set_keepalive).

3. you are not doing proper downstream connection concurrency level
control (via the standard ngx_limit_conn module or something
equivalent), leading to too many upstream connections to mysql.

I suggest you do local experiments with a minimal setup to avoid mistakes.

> Opening and closing a
> connection via PHP doesn't do that.
>

I doubt your php setup has a proper connection pool :)

Regards,
-agentzh

[bjoe2k4]

I think we are talking past each other. This happens even *without* any connection pool (excludes reason 1 + 2), just a simple connect+close increases the aborted connection counter in mysql.

My maximum connections to mysql is 3 as of right now.

--Regards

[bjoe2k4]

I think i partially figured it out. The mysql server expects a COM_QUIT (0x01) packet from the client before closing the connection. If i slightly modify the close command of lua-resty-mysql, connections aren't treated as aborted anymore. Yay!

function _M.close(self)
    local sock = self.sock
    if not sock then
        return nil, "not initialized"
    end

    self.state = nil

    _send_packet(self,0x01,4)

    return sock:close()
end

For connections in connection pool this doesn't work. As soon as a connection in pool times out, it doesn't send COM_CLOSE packet.

[Yichun Zhang (agentzh)]

Hello!

On Mon, Nov 24, 2014 at 2:57 PM, bjoe2k4 wrote:
> I think i partially figured it out. The mysql server expects a COM_QUIT
> (0x01) packet from the client before closing the connection.

Hmm, I don't think the COM_QUIT packet is mandatory for closing the
MySQL connection. I've never seen any issues from MySQL 5.1 to 5.5.

What exact version of MySQL server are you using?

Also, I still think your Lua code has bugs, because nothing can stop
the client from closing the connection (even when the server is not
ready for that).

I've tried your code example on my side, that is, connecting to mysql
and then closing immediately without sending any queries, and the
mysql connection enters the TIME_WAIT state when the request finishes,
as expected:

$ netstat -nt|grep 3306
tcp 0 0 127.0.0.1:55106 127.0.0.1:3306
TIME_WAIT

Regards,
-agentzh

[bjoe2k4]

Hmm, I don't think the COM_QUIT packet is mandatory for closing the
MySQL connection. I've never seen any issues from MySQL 5.1 to 5.5.

 

It is not really an issue, just something ugly. I am using MariaDB 10.0.14.

What exact version of MySQL server are you using?

Also, I still think your Lua code has bugs, because nothing can stop
the client from closing the connection (even when the server is not
ready for that).

Nothing is stopping the client from closing the connection. It's just that a closed connection from ngx-lua always appears as an aborted connection.

 $ echo "SHOW STATUS;" | mysql | grep Aborted_clients
Aborted_clients 214
 $ curl -s http://localhost/lua-mysql-test > /dev/null

 $ echo "SHOW STATUS;" | mysql | grep Aborted_clients
Aborted_clients 215


[Yichun Zhang (agentzh)]

Hello!

On Mon, Nov 24, 2014 at 3:39 PM, bjoe2k4 wrote:
> $ echo "SHOW STATUS;" | mysql | grep Aborted_clients
> Aborted_clients 215
>

Okay, it just affects this counter. I'd still save this (artificial)
COM_QUIT packet which can lead to a simpler implementation and better
performance (especially for many short-lived mysql connections).

Regards,
-agentzh