How to check is mysql connection using coscket?

roman.alexandr · 2016-03-11T07:34:53+00:00

> -----Oorspronkelijk bericht----- > Van: openresty-en@googlegroups.com [mailto:openresty-en@googlegroups.com] > Namens Yichun Zhang (agentzh) >...

How to check is mysql connection using coscket?

roman.alexandr

Hi folks!

local mysql

if a and b then
 mysql, err = sql:new()
  if not sql then
        ngxlog(ngxERR, "Failed to instantiate MySQL: "..err)
        return
  end


  ok, err, errno, sqlstate = mysql:connect{
            host     = mysql_production,
            port     = mysql_port,
            database = mysql_database,
            user     = mysql_user,
            password = mysql_password}


  if not ok then
        ngxlog(ngxERR, "Failed to connect to MySQL: ", err, ": ", errno, " ", sqlstate)
        return
 end


        -- SOME QUERIES HERE
end


local times, err = mysql:get_reused_times(60000, 100)

if times then 
    ngx.log(ngxERR, "SQL COSOCKET REUSED: " .. times)
end


--local ok, err = mysql:close()
local ok, err = mysql:set_keepalive()
if err then 
    ngxlog(ngxERR, "mysql:set_keepalive return error: " .. err)
end

Got "SQL COSOCKET REUSED: 0" and no errors...

What I'm missing?Enter code here...

guanglinlv

在 2016年3月11日星期五 UTC+8上午7:34:53，Alexandr Roman写道：

Hi folks!

local mysql if a and b then mysql, err = sql:new() if not sql then ngxlog(ngxERR, "Failed to instantiate MySQL: "..err) return end ok, err, errno, sqlstate = mysql:connect{ host = mysql_production, port = mysql_port, database = mysql_database, user = mysql_user, password = mysql_password} if not ok then ngxlog(ngxERR, "Failed to connect to MySQL: ", err, ": ", errno, " ", sqlstate) return end -- SOME QUERIES HERE end local times, err = mysql:get_reused_times(60000, 100) if times then ngx.log(ngxERR, "SQL COSOCKET REUSED: " .. times) end --local ok, err = mysql:close() local ok, err = mysql:set_keepalive() if err then ngxlog(ngxERR, "mysql:set_keepalive return error: " .. err) end

Got "SQL COSOCKET REUSED: 0" and no errors...

What I'm missing?Enter code here...

0 means that the connection does not come form built-in connection pool.

see https://github.com/openresty/lua-nginx-module#tcpsockgetreusedtimes

roman.alexandr

Thanks Guanglin!

Yeah, I saw this. But my question is how to check that connection really use cosockets? Is there some functions to show current state of pool? Or I'm too concerned and have to relax and believe? ;)

On Thursday, March 10, 2016 at 8:51:20 PM UTC-5, Guanglin Lv wrote:

在 2016年3月11日星期五 UTC+8上午7:34:53，Alexandr Roman写道：
Hi folks!

local mysql if a and b then mysql, err = sql:new() if not sql then ngxlog(ngxERR, "Failed to instantiate MySQL: "..err) return end ok, err, errno, sqlstate = mysql:connect{ host = mysql_production, port = mysql_port, database = mysql_database, user = mysql_user, password = mysql_password} if not ok then ngxlog(ngxERR, "Failed to connect to MySQL: ", err, ": ", errno, " ", sqlstate) return end -- SOME QUERIES HERE end local times, err = mysql:get_reused_times(60000, 100) if times then ngx.log(ngxERR, "SQL COSOCKET REUSED: " .. times) end --local ok, err = mysql:close() local ok, err = mysql:set_keepalive() if err then ngxlog(ngxERR, "mysql:set_keepalive return error: " .. err) end

Got "SQL COSOCKET REUSED: 0" and no errors...

What I'm missing?Enter code here...

0 means that the connection does not come form built-in connection pool.

see https://github.com/openresty/lua-nginx-module#tcpsockgetreusedtimes

agentzh

Hello!

On Thu, Mar 10, 2016 at 6:26 PM, Alexandr Roman wrote:
> Yeah, I saw this. But my question is how to check that connection really use
> cosockets? Is there some functions to show current state of pool? Or I'm too
> concerned and have to relax and believe? ;)
>

lua-resty-mysql is built upon cosockets. You can check its Lua source code.

If you still want to be sure that it's not using any blocking
operations, you can try the off-CPU flame graph tool to verify a busy
nginx worker in production or in development environments:

https://openresty.org/#Profiling

Regards,
-agentzh

roman.alexandr

Thank you very much!

On Wednesday, March 16, 2016 at 3:52:11 PM UTC-4, agentzh wrote:

Hello!

On Thu, Mar 10, 2016 at 6:26 PM, Alexandr Roman wrote:
> Yeah, I saw this. But my question is how to check that connection really use
> cosockets? Is there some functions to show current state of pool? Or I'm too
> concerned and have to relax and believe? ;)
>

lua-resty-mysql is built upon cosockets. You can check its Lua source code.

If you still want to be sure that it's not using any blocking
operations, you can try the off-CPU flame graph tool to verify a busy
nginx worker in production or in development environments:

https://openresty.org/#Profiling

Regards,
-agentzh