Alexey Kopytov has proposed merging lp:~alexey2k/sysbench/sb-prepared-stmt into
lp:sysbench.
Requested reviews:
Alexey Kopytov (akopytov)
For more details, see:
https://code.launchpad.net/~alexey2k/sysbench/sb-prepared-stmt/+merge/152934
Lua implementation of OLTP workload with prepared statement
Added help information
Various cleanups and minor fixes
--
https://code.launchpad.net/~alexey2k/sysbench/sb-prepared-stmt/+merge/152934
Your team sysbench-developers is subscribed to branch lp:sysbench.
=== 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
+
_______________________________________________
Mailing list: https://launchpad.net/~sysbench-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~sysbench-developers
More help : https://help.launchpad.net/ListHelp
