how can I impove the performance for batch insert into mysql ? about lua-resty-mysql HOT 5 OPEN

@gholly Concatenating multiple SQL statements in a single query string should save several roundtrips between your nginx server and the mysqld server, and thus faster. You do not really need the transaction thing here for the batch mode to work.

from lua-resty-mysql.

@agentzh First,Thank you for your answer.If the stitching multiple sql statement to insert into a string , and if one insert statement insert fails, three tables will not be able to keep pace . Missing rollback function .
How to avoid this situation ？

from lua-resty-mysql.

I try to write like this:

  local db,err = mysql:new()
  local ok,err,errno,sqlstate = db:connect(dbconf)    
  if not ok then
      ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
      return
  end


 res, err, errno, sqlstate = db:query("insert sql1;insert sql2;insert sql3;")
 if not res then
     ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
      return
 end
 local i=2
 while err == "again" do
     res, err, errno, sqlstate = db:read_result()
     if not res then
          ngx.log(ngx.ERR, "bad result #", i, ": ", err, ": ", errno, ": ", sqlstate, ".")
          return  
     end
   -- ngx.say("result #", i, ": ", cjson.encode(res))        
    i = i + 1
end

local ok, err = db:set_keepalive(10000, 1000)
if not ok then
   ngx.log(ngx.ERR,"failed to set keepalive: ",err)
   --ngx.exit(500)
 end

But the performance of this is stilll low:

What methods should be used to ensure that the table can be synchronized, and high performance？

from lua-resty-mysql.

@gholly Before optimizing anything, you should determine the bottleneck first. If you cannot max out your nginx worker processes's CPU usage to 100% then your mysqld server is the bottleneck. Otherwise, you can use the on-CPU flame graph tools (both on C land and Lua land) to determine the bottlenecks of the nginx server. See

http://openresty.org/en/profiling.html

BTW, it's recommended to move such general discussions to the openresty-en mailing list. This place is preserved for bug reports and development discussions. Thank you for your cooperation. Please see

http://openresty.org/en/community.html

from lua-resty-mysql.

@agentzh Well, first of all thank you for your advice, I try to use the on-CPU flame graph tools to maxout the nginx worker processes's CPU usage to higher.
I'll move to the question later openresty-en mailing list.

from lua-resty-mysql.