Need to add faster fetching row about lua-resty-mysql HOT 19 OPEN

@ryannining I'm guessing the slowness in your Lua code comes from your frequent calls ngx.print and ngx.say instead of lua-resty-mysql. Unlike PHP's echo, these two Lua output functions do not have buffering (by default), which is much more expensive. You can confirm that by using the C-land and/or Lua-land on-CPU flame graph:

https://github.com/openresty/nginx-systemtap-toolkit#sample-bt

https://github.com/openresty/stapxx#lj-lua-stacks

So it's always recommended to buffer your HTML output in your own Lua table, and then use a single ngx.print to emit all the pieces out in a single run if you don't want strict non-buffered output support.

BTW, better avoid using your own abstraction in the benchmark programs because it obscures the facts what underlying primitives (of both PHP and OpenResty) you're actually using under the hood.

from lua-resty-mysql.

Yea, i have check the read_result code, its already the best code. I have buffer all the result too, actually. My raw function is buffering all and do 1 time ngx.say in the finish!

I will check my code again, thank you.

from lua-resty-mysql.

@ryannining As I've suggested earlier, try profiling with the C-land and Lua-land on-CPU flame graph tools. We can easily see the bottlenecks on your flame graphs :)

from lua-resty-mysql.

@ryannining Also, it might be helpful by benchmarking the raw Lua code using lua-resty-mysql instead, without using MoonScript.

from lua-resty-mysql.

@ryannining BTW, I've noticed that the second argument to your queryi() function in the MoonScript version does not match the number in your limit clause of the SQL query. Is it the estimated row number? If yes, then you should use the same number. Otherwise I can imagine you're spending a lot of time on Lua table resizing (again, flame graphs can show the bottlenecks in a straightforward way so that we don't have to guess).

I can see a potential benefit in a single row fetching API via avoiding allocating and keeping many Lua tables in memory.

from lua-resty-mysql.

Yea, actually i have make php library that store qurey result in single array. And i love your resty-mysql, its doing the same, make me easy to migrate from my php code to lua code.

Actually php is very slow if we store all result in one array. Its far slower than default resty-mysql.

My project was combining all necessary library (mysql,cookies,session,upload,magick) so i can 100% migrate from php to lua. And i actually did, i have convert one my website. and its faster than PHP already by factor 2-3 on SIEGE benchmark, just convert the code, no optimization yet.

http://lampu.tokoled.net:81/ Openresty - Moonscript/Lua
http://lampu.tokoled.net:82/ XGINX -FastcgiPHP/ the original web

But my next website http://www.tokosenterled.com its very frustating, so many code.... still need much learn about LUA and OpenResty.

Oh this is my project page on github: https://github.com/ryannining/restyweb still working on it, so many bugs, currently i have bridge module so i can choose using resty-mysql or luasql.mysql. And indeed luasql.mysql is little faster (perhaps its C library).

from lua-resty-mysql.

@ryannining luasql is blocking. Always avoid it. You can get performance boost in lua-resty-mysql by enabling the experimental trace stitching feature in LuaJIT. Just put the following lines in your nginx.conf's http {} block:

init_by_lua '
    jit.opt.start("minstitch=10")
';

But be careful, this trace stitching feature is new and may have quirks.

And yeah, lua-resty-mysql still has a lot of room for optimizations. I just haven't found the tuits to do that.

from lua-resty-mysql.

@ryannining BTW, ensure you're running the latest OpenResty bundle. As I've repeated earlier, do profiling with flame graph tools and it'll be much easier to optimize things :)

from lua-resty-mysql.

@ryannining Your http://lampu.tokoled.net:81/ site looks interesting. Glad you're migrating from PHP to OpenResty :) Hopefully you can have more fun with it. BTW, you're encouraged to join the openresty-en mailing list to get connected with the community :)

from lua-resty-mysql.

You are right, the performance bottle-next is in the ngx.say, even though have buffer it. I measure buffer the output make it slower 0.6x . Do you have any idea how to increase performance of the ngx.say ?

i have test this code:

for r=1,100000
  raw "2500"," "

Versus

for ($i=0;$i<100000;$i++){
  echo "2500"," ";
}

PHP is 60x times faster. Thats why my tokosenterled.com website, which have more texts than ledhemat.com , open-resty and lua perform slower.

Sorry, the clock is wrong. The correct result is ngx.say is same or faster than PHP echo.

And buffering output and do one time ngx.say, is slower than directly call ngx.say

from lua-resty-mysql.

@ryannining How exactly do you implement the raw function? Your own abstraction in the sample code makes me clueless :)

from lua-resty-mysql.

@ryannining My hunch is that your raw function is implemented in an absolutely wrong way like using the following way to do buffering, which is terribly slow in Lua:

buffer = buffer .. new_string

Because Lua strings are immutable, such operations are inherently slow. The correct way is to make buffer a Lua table, and then upon every output:

index = index + 1
buffer[index] = new_string

And eventually,

ngx.print(table.concat(buffer))

Regards,
-agentzh

from lua-resty-mysql.

raw

  raw:(...)->
    arg = {...}
    s=ngx.ctx.sbuffer
    for i = 1, #arg do
      v=arg[i]
      if type(v)=='function'
        v()
      else
        s[#s+1]=v 

finish!

    ngx.ctx.ses\save()
    ngx.print(ngx.ctx.sbuffer)
    ngx.ctx.sbuffer=nil

i call print directly without concat the sbuffer table content.

from lua-resty-mysql.

@ryannining Because we're talking about performance here, I'd list the following issues in your latest version of raw:

1. avoid calling ngx.ctx over and over again. It's a Lua metamethod magic. Better cache the ngx.ctx table yourself (like in your own Lua module's toplevel "local" variable) because it's (much) more efficient.
2. avoid calling #s over and over again (as in a tight loop or so) because # on Lua tables is not an O(1) operation. It's (much) more efficient to maintain the array length yourself, as demonstrated in my earlier code snippet.

I believe taking care of these can make a difference :)

from lua-resty-mysql.

later , i will check

index = index + 1
buffer[index] = new_string

compared to

buffer[#buffer+1] = new_string

but i think raw or ngx.print is not the problem, they are fast. Yes we need buffering, so server code can play with cookies and session without worry response body already sent, and because i need php function like ob_start, ob_get_content

And actually change to luasql is not increase benchmark on SIEGE too, and can harm because its blocking like you say. so i think the problem is in other code, since on my ledhemat.com we have already reach 2x boost performance from PHP, on my cheap VPS

I still learning this
https://github.com/openresty/stapxx#lj-lua-stacks

Also the uploads and posts problem already solved thx.

I want to post on the group but afraid someone already asking the question , hehehehehe. I am new in LUA, i mainly use PHP and Python.

from lua-resty-mysql.

for ngx.ctx i read somewhere we need to use this because other local variable in module are shared with other worker. What is the solution for this (please give me example). Is it using class ? i havent use class yet, still need to learn class in lua.

Thx for the direction !! i will test ..

from lua-resty-mysql.

@ryannining Just define a custom Lua module like resty.mysql you're using. Basically:

-- utils.lua
local _M = {}
function _M.raw() ... end
return _M

And then later in your content_by_lua code:

local utils = require "utils"
local raw = utils.raw
...
raw(...)  -- call it!

And yeah, my mistake, you cannot cache ngx.ctx in Lua module's toplevel scope because it'll be shared by all the concurrent requests. We can pass the ctx table around explicitly by the first argument to raw (and other similar functions):

local ctx = ngx.ctx
raw(ctx, "hello")
raw(ctx, "world")

Yeah, it's not really nice but it's the fastest solution. Accessing ngx.ctx everywhere accumulates significant performance overhead. We used to see it getting kinda hot in our flame graphs for our own Lua apps online :)

from lua-resty-mysql.

If, i set lua_code_cache to off, is all module still share same variable ?

from lua-resty-mysql.

@ryannining Nope. In case of disabling the Lua code cache, every request has its own (fresh) Lua VM instance. No Lua-level data sharing across requests is possible.

from lua-resty-mysql.