Merge lp:~alexey2k/sysbench/sb-prepared-stmt into lp:sysbench

[Alexey Kopytov]

Lua implementation of OLTP workload with prepared statement
Added help information
Various cleanups and minor fixes

Unmerged revisions

117. By Alexey Stroganov on 2013-03-12

Lua implementation of OLTP workload with prepared statement
Added help information
Various cleanups and minor fixes

=== modified file 'sysbench/tests/db/common.lua'
--- sysbench/tests/db/common.lua	2011-12-01 19:43:29 +0000
+++ sysbench/tests/db/common.lua	2013-03-12 15:16:22 +0000
@@ -19,7 +19,7 @@
 
    i = table_id
 
-   print("Creating table 'sbtest" .. i .. "'...")
+   print("Creating table '" .. oltp_table_name .. i .. "'...")
    if (db_driver == "mysql") then
       query = [[
 CREATE TABLE sbtest]] .. i .. [[ (
@@ -58,14 +58,14 @@
 
    db_query(query)
 
-   db_query("CREATE INDEX k_" .. i .. " on sbtest" .. i .. "(k)")
+   db_query("CREATE INDEX k_" .. i .. " on ".. oltp_table_name ..  i .. "(k)")
 
-   print("Inserting " .. oltp_table_size .. " records into 'sbtest" .. i .. "'")
+   print("Inserting " .. oltp_table_size .. " records into '" .. oltp_table_name .. i .. "'")
 
    if (oltp_auto_inc) then
-      db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(k, c, pad) VALUES")
+      db_bulk_insert_init("INSERT INTO " .. oltp_table_name .. i .. "(k, c, pad) VALUES")
    else
-      db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(id, k, c, pad) VALUES")
+      db_bulk_insert_init("INSERT INTO " .. oltp_table_name .. i .. "(id, k, c, pad) VALUES")
    end
 
    local c_val
@@ -101,7 +101,6 @@
 
    db_connect()
 
-
    for i = 1,oltp_tables_count do
      create_insert(i)
    end
@@ -115,7 +114,7 @@
    set_vars()
 
    for i = 1,oltp_tables_count do
-   print("Dropping table 'sbtest" .. i .. "'...")
+   print("Dropping table '" .. oltp_table_name .. i .. "'...")
    db_query("DROP TABLE sbtest".. i )
    end
 end
@@ -131,6 +130,18 @@
    oltp_distinct_ranges = oltp_distinct_ranges or 1
    oltp_index_updates = oltp_index_updates or 1
    oltp_non_index_updates = oltp_non_index_updates or 1
+   oltp_delete = 1
+   oltp_insert = 1
+
+   prepared_stmt_mode=true
+
+   if (oltp_ps_mode == 'disable') then
+     prepared_stmt_mode=false
+   end
+
+   if (oltp_table_name == nil or oltp_table_name == '' ) then 
+      oltp_table_name = 'sbtest'
+   end   
 
    if (oltp_auto_inc == 'off') then
       oltp_auto_inc = false
@@ -150,4 +161,63 @@
       oltp_skip_trx = false
    end
 
-end
+  if (oltp_secondary == 'on') then 
+    oltp_secondary = true
+  else
+    oltp_secondary = false
+  end
+
+end
+
+function help()
+
+  print ([[ 
+
+   --oltp_table_name        = <default:sbtest>
+   --oltp_table_size        = <default:10000>
+   --oltp_tables_count      = <default:1>
+
+   --oltp_range_size        = <default:100>
+   --oltp_point_selects     = <default:10>
+   --oltp_simple_ranges     = <default:1>
+   --oltp_sum_ranges        = <default:1>
+   --oltp_order_ranges      = <default:1>
+   --oltp_distinct_ranges   = <default:1>
+   --oltp_index_updates     = <default:1>
+   --oltp_non_index_updates = <default:1>
+   --oltp_delete            = <default:1>
+   --oltp_insert            = <default:1>
+
+   
+
+   --oltp_read_only         = <on|off>
+   --oltp_skip_trx          = <on|off>
+   --oltp_auto_inc          = <on|off>
+   --oltp_secondary         = <on|off> use secondary key instead PRIMARY key for id column
+
+   --oltp-ps-mode           = <enable|disable>
+   --mysql_table_engine     = <default:InnoDB>
+   --mysql-host	            = <default:localhost>  MySQL server host, you may specify a list of hosts separated by commas. In this case 
+                              SysBench will distribute connections between specified MySQL hosts on a round-robin basis. 
+                              Note that all connection ports and passwords must be the same on all hosts. 
+                              Also, databases and tables must be prepared explicitely on each host before 
+                              executing the benchmark.
+   --mysql-port	            = <default:3306>  MySQL server port (in case TCP/IP connection should be used)
+   --mysql-socket	    = <> Unix socket file to communicate with the MySQL server	 
+   --mysql-user	            = <> MySQL user
+   --mysql-password	    = <> MySQL password	 
+   --mysql-db	            = <sbtest> MySQL database name. Note SysBench will not automatically create this database. 
+                                 You should create it manually and grant the appropriate privileges to a user which will 
+                                 be used to access the test table
+   --mysql-table-engine	    = <innodb> Type of the test table. Possible values: myisam, innodb, heap, ndbcluster
+   --mysql-ssl	            = <no> Use SSL connections
+   --myisam-max-rows	    = <1000000> MAX_ROWS option for MyISAM tables (required for big tables)	1000000
+   --mysql-create-options   = <> Additional options passed to CREATE TABLE.
+          
+            ]])
+               
+end
+                  
+                     
+                        
+                        
\ No newline at end of file

=== modified file 'sysbench/tests/db/oltp.lua'
--- sysbench/tests/db/oltp.lua	2011-12-01 19:43:29 +0000
+++ sysbench/tests/db/oltp.lua	2013-03-12 15:16:22 +0000
@@ -1,89 +1,27 @@
 pathtest = string.match(test, "(.*/)") or ""
 
 dofile(pathtest .. "common.lua")
+dofile(pathtest .. "oltp_common.lua")
 
 function thread_init(thread_id)
    set_vars()
 
-   if (db_driver == "mysql" and mysql_table_engine == "myisam") then
-      begin_query = "LOCK TABLES sbtest WRITE"
-      commit_query = "UNLOCK TABLES"
+   if (prepared_stmt_mode) then 
+     init_prepared_stmt()
    else
-      begin_query = "BEGIN"
-      commit_query = "COMMIT"
+     init_plain_stmt()
    end
 
 end
 
 function event(thread_id)
-   local rs
-   local i
-   local table_name
-   local range_start
-   local c_val
-   local pad_val
-   local query
-
-   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
-   if not oltp_skip_trx then
-      db_query(begin_query)
-   end
-
-   for i=1, oltp_point_selects do
-      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
-   end
-
-   for i=1, oltp_simple_ranges do
-      range_start = sb_rand(1, oltp_table_size)
-      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
-   end
-  
-   for i=1, oltp_sum_ranges do
-      range_start = sb_rand(1, oltp_table_size)
-      rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
-   end
-   
-   for i=1, oltp_order_ranges do
-      range_start = sb_rand(1, oltp_table_size)
-      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
-   end
-
-   for i=1, oltp_distinct_ranges do
-      range_start = sb_rand(1, oltp_table_size)
-      rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
-   end
-
-   if not oltp_read_only then
-
-   for i=1, oltp_index_updates do
-      rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
-   end
-
-   for i=1, oltp_non_index_updates do
-      c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
-      query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
-      rs = db_query(query)
-      if rs then
-        print(query)
-      end
-   end
-
-   i = sb_rand(1, oltp_table_size)
-
-   rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
-   
-   c_val = sb_rand_str([[
-###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
-   pad_val = sb_rand_str([[
-###########-###########-###########-###########-###########]])
-
-   rs = db_query("INSERT INTO " .. table_name ..  " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))
-
-   end -- oltp_read_only
-
-   if not oltp_skip_trx then
-      db_query(commit_query)
+
+   if (prepared_stmt_mode) then
+     event_prepared_stmt(thread_id)
+   else
+     event_plain_stmt(thread_id)
    end
 
 end
 
+

=== added file 'sysbench/tests/db/oltp_common.lua'
--- sysbench/tests/db/oltp_common.lua	1970-01-01 00:00:00 +0000
+++ sysbench/tests/db/oltp_common.lua	2013-03-12 15:16:22 +0000
@@ -0,0 +1,213 @@
+function init_prepared_stmt()
+
+  stmt= { 
+          point=          { query = "SELECT c FROM sb_table_name WHERE id=?", params_template = {0}, params={}, sth = {} },
+          range=          { query = "SELECT c FROM sb_table_name WHERE id BETWEEN ? AND ?", params_template = {0, 0}, params={}, sth = {} },
+          sum=            { query = "SELECT SUM(K) FROM sb_table_name WHERE id BETWEEN ? AND ?", params_template = {0, 0}, params={}, sth = {} },
+          order=          { query = "SELECT c FROM sb_table_name WHERE id BETWEEN ? AND ? ORDER BY c", params_template = { 0, 0}, params={}, sth = {} },
+          distinct=       { query = "SELECT DISTINCT c FROM sb_table_name WHERE id BETWEEN ? AND ? ORDER BY c", params_template = {0, 0}, params={}, sth = {} },
+          update_idx=     { query = "UPDATE sb_table_name SET k=k+1 WHERE id=?", params_template = {0}, params={}, sth = {} },
+          update_non_idx= { query = "UPDATE sb_table_name SET c=? WHERE id=?", params_template = {"", 0}, params={}, sth = {} },
+          delete=         { query = "DELETE FROM sb_table_name WHERE id=?", params_template = {0}, params={}, sth = {} },
+          insert=         { query = "INSERT INTO sb_table_name (id, k, c, pad) VALUES  (?, ?, ? , ?)", params_template = {0,0,"",""}, params={}, sth = {} },
+          begin=          { query = "BEGIN", params_template = {}, params={}, sth = {} },
+          commit=         { query = "COMMIT", params_template = {}, params={}, sth = {} }
+        }
+ 
+   if (db_driver == "mysql" and mysql_table_engine == "myisam") then
+      stmt["begin"]["query"]="LOCK TABLES sb_table_name WRITE"
+      stmt["commit"]["query"]="UNLOCK TABLES"
+   end
+
+   for t1,t2 in pairs (stmt) do
+       for i=1, oltp_tables_count do 
+         local query=t2["query"]
+         query=query:gsub("sb_table_name", oltp_table_name..i)
+         t2["sth"][i]=db_prepare(query)
+         if ( t2["params_template"][1] ~= nil ) then 
+            t2["params"][i]={}
+            for j,x in ipairs(t2["params_template"]) do t2["params"][i][j] = x end
+            db_bind_param(t2["sth"][i], t2["params"][i])
+         end
+     end
+   end
+end
+
+
+function init_plain_stmt()
+
+  if (db_driver == "mysql" and mysql_table_engine == "myisam") then
+     begin_query = "LOCK TABLES sbtest WRITE"
+     commit_query = "UNLOCK TABLES"
+  else
+     begin_query = "BEGIN"
+     commit_query = "COMMIT"
+  end
+end
+
+
+function sth_execute(sth)
+   rs = db_execute(sth)
+   db_store_results(rs)
+   db_free_results(rs)
+end
+  
+function event_prepared_stmt(thread_id)
+  local rs
+  local i
+  local table_name
+  local range_start
+  local c_val
+  local pad_val
+  local query
+
+  table_id = sb_rand_uniform(1, oltp_tables_count)
+
+  if not oltp_skip_trx then
+    db_execute(stmt["begin"]["sth"][table_id])
+  end
+
+  for i=1, oltp_point_selects do
+     stmt["point"]["params"][table_id][1] = sb_rand(1, oltp_table_size)
+     sth_execute(stmt["point"]["sth"][table_id])
+  end
+
+  for i=1, oltp_simple_ranges do
+    stmt["range"]["params"][table_id][1] = sb_rand(1, oltp_table_size)
+    stmt["range"]["params"][table_id][2] = stmt["range"]["params"][table_id][1] + oltp_range_size - 1
+    sth_execute(stmt["range"]["sth"][table_id])
+  end
+
+  for i=1, oltp_sum_ranges do
+    stmt["sum"]["params"][table_id][1] = sb_rand(1, oltp_table_size)
+    stmt["sum"]["params"][table_id][2] = stmt["sum"]["params"][table_id][1] + oltp_range_size - 1
+    sth_execute(stmt["sum"]["sth"][table_id])
+  end
+
+  for i=1, oltp_order_ranges do
+    stmt["order"]["params"][table_id][1] = sb_rand(1, oltp_table_size)
+    stmt["order"]["params"][table_id][2] = stmt["order"]["params"][table_id][1] + oltp_range_size - 1
+    sth_execute(stmt["order"]["sth"][table_id])
+  end
+
+  for i=1, oltp_distinct_ranges do
+    stmt["distinct"]["params"][table_id][1] = sb_rand(1, oltp_table_size)
+    stmt["distinct"]["params"][table_id][2] = stmt["distinct"]["params"][table_id][1] + oltp_range_size - 1
+    sth_execute(stmt["distinct"]["sth"][table_id])
+  end
+
+  if not oltp_read_only then 
+
+    for i=1, oltp_index_updates do
+      stmt["update_idx"]["params"][table_id][1] = sb_rand(1, oltp_table_size)
+      db_execute(stmt["update_idx"]["sth"][table_id])
+    end
+
+    for i=1, oltp_non_index_updates do 
+      c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
+      stmt["update_non_idx"]["params"][table_id][1] = c_val
+      stmt["update_non_idx"]["params"][table_id][2] = sb_rand(1, oltp_table_size)
+      db_execute(stmt["update_non_idx"]["sth"][table_id])
+    end
+
+    -- DELETE and INSERT on the same id
+    local id = sb_rand(1, oltp_table_size)
+
+    for i=1, oltp_delete do
+      stmt["delete"]["params"][table_id][1] = id
+      db_execute(stmt["delete"]["sth"][table_id])
+    end
+
+    for i=1, oltp_insert do
+      c_val = sb_rand_str([[###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
+      pad_val = sb_rand_str([[###########-###########-###########-###########-###########]])
+
+      stmt["insert"]["params"][table_id][1] = id
+      stmt["insert"]["params"][table_id][2] = sb_rand(1, oltp_table_size)
+      stmt["insert"]["params"][table_id][3] = c_val
+      stmt["insert"]["params"][table_id][4] = pad_val
+      db_execute(stmt["insert"]["sth"][table_id])
+    end
+  end -- oltp_read_only
+
+  if not oltp_skip_trx then
+    db_execute(stmt["commit"]["sth"][table_id])
+  end
+
+end
+
+function event_plain_stmt(thread_id)
+  local rs
+  local i
+  local table_name
+  local range_start
+  local c_val
+  local pad_val
+  local query
+
+  table_name = oltp_table_name .. sb_rand_uniform(1, oltp_tables_count)
+  if not oltp_skip_trx then
+    db_query(begin_query)
+  end
+
+  for i=1, oltp_point_selects do
+    rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
+  end
+
+  for i=1, oltp_simple_ranges do
+    range_start = sb_rand(1, oltp_table_size)
+    rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
+  end
+  
+  for i=1, oltp_sum_ranges do
+    range_start = sb_rand(1, oltp_table_size)
+    rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
+  end
+   
+  for i=1, oltp_order_ranges do
+    range_start = sb_rand(1, oltp_table_size)
+    rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
+  end
+
+  for i=1, oltp_distinct_ranges do
+    range_start = sb_rand(1, oltp_table_size)
+    rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
+  end
+
+  if not oltp_read_only then
+
+    for i=1, oltp_index_updates do
+      rs = db_query("UPDATE " .. table_name .. " SET k=k+1 WHERE id=" .. sb_rand(1, oltp_table_size))
+    end
+
+    for i=1, oltp_non_index_updates do
+      c_val = sb_rand_str("###########-###########-###########-###########-###########-###########-###########-###########-###########-###########")
+      query = "UPDATE " .. table_name .. " SET c='" .. c_val .. "' WHERE id=" .. sb_rand(1, oltp_table_size)
+      rs = db_query(query)
+      if rs then
+        print(query)
+      end
+    end
+ 
+    i = sb_rand(1, oltp_table_size)
+    for i=1, oltp_delete do
+      rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)
+    end
+   
+    for i=1, oltp_insert do
+      c_val = sb_rand_str([[
+###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])
+      pad_val = sb_rand_str([[
+###########-###########-###########-###########-###########]])
+
+      rs = db_query("INSERT INTO " .. table_name ..  " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, sb_rand(1, oltp_table_size) , c_val, pad_val))
+    end
+   
+  end -- oltp_read_only
+
+  if not oltp_skip_trx then
+     db_query(commit_query)
+  end
+
+end
+