how can I impove the performance for batch insert into mysql ?

[gholly]

I want to insert 3 SQL statements are inserted into the 3 table, but the performance is too low, I was so written, how do I do to make the performance higher？ the code like this: example1:

     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("BEGIN;")
if not res then
        ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
        return
end

   res, err, errno, sqlstate = db:query("insert1;")
   if not res then
        ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
        return
    end 
  res, err, errno, sqlstate = db:query("insert2;")
   if not res then
       ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
       return
  end
 res, err, errno, sqlstate = db:query("insert3;")
 if not res then
       ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
       return
 end

 res, err, errno, sqlstate = db:query("COMMIT;")
 if not res then
        ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
        return
 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

the performance of examnple1 like that:

example2:

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("START TRANSACTION;insert sql1;insert sql2;insert   sql3;COMMIT;")
if not res then
       ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
       return
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

the performance of example2 like that: ./wrk -t10 -c 100 -d1m "url"

How can i apply the transaction to improve the performance? The way of appling the transcation is right?But lack the ROLLBACK ,what's the right way?

[agentzh]

@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.

[gholly]

@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 ？

[gholly]

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？

[agentzh]

@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

[gholly]

@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.