Last active
October 23, 2023 12:24
-
-
Save ylacancellera/e4c2c97d2f746f91db4cd588e0d50c49 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env sysbench | |
-- replace oltp_common.lua | |
-- cp oltp_common.lua /usr/share/sysbench/oltp_common.lua | |
-- usage | |
-- oltp_rw_unoptimized.lua \ | |
-- --mysql-host=192.168.56.40 \ | |
-- --mysql-password=sbtest \ | |
-- --mysql-user=sbtest \ | |
-- --mysql-port=3306 \ | |
-- --table-size=1000000 \ | |
-- --tables=1 \ | |
-- --threads=8 \ | |
-- --time=0 \ | |
-- --report-interval=1 \ | |
-- --read_prct=0.84 \ | |
-- --delete_prct=0.06 \ | |
-- --update_prct=0.1 \ | |
-- --thresholds=100,500 \ | |
-- run | |
require('.oltp_common') | |
sysbench.cmdline.options.primary_key = {"Add primary keys", true} | |
sysbench.cmdline.options.read_prct = {"Part of reads", 0.95} | |
sysbench.cmdline.options.update_prct = {"Part of updates", 0.025} | |
sysbench.cmdline.options.delete_prct = {"Part of deletes and inserts", 0.025} | |
sysbench.cmdline.options.insert_large_text_prct = {"Part of large text insertion among inserts", 0} | |
sysbench.cmdline.options.large_text_size_min = {"Minimum size of large texts (Megabytes)", 1} | |
sysbench.cmdline.options.large_text_size_max = {"Maximum size of large texts (Megabytes)", 10} | |
sysbench.cmdline.options.fullscan_prct = {"Part of table full scan among selects", 0} | |
sysbench.cmdline.options.join_fullscan_prct = {"Part of joins not using indexes among selects (if tables >= 2)", 0} | |
sysbench.cmdline.options.double_fullscan_prct = {"Part of joins not using indexes along with a base table full scan among selects (if tables >= 2). Should be set very low (0.00001)", 0} | |
sysbench.cmdline.options.delete_range_prct = {"Part of range deletes", 0} | |
sysbench.cmdline.options.group_trx = {"Group every actions in a single transaction for each event per thread", false} | |
sysbench.cmdline.options.verbose_display = {"Enable report hook to display percents of DMLs and DQLs and remove some infos", true} | |
sysbench.cmdline.options.fulltext = {"Add fulltexts indexes on large_text columns", false} | |
sysbench.cmdline.options.disable_color = {"Disable colors for verbose display", false} | |
sysbench.cmdline.options.csv_output = {"Display final report in csv format(FORCED TO TRUE)\n Format: time_total,threads,tables,table_size,reads,writes,other,latency_min,latency_max,latency_avg,errors", false} | |
sysbench.cmdline.options.thresholds = {"Disable colors for verbose display", "1000,2000"} | |
stmt_defs.fullscans = { | |
"SELECT c FROM sbtest%u WHERE pad=? LIMIT 30", | |
{t.CHAR, 60} | |
} | |
stmt_defs.join_fullscans = { | |
"SELECT sb1.id FROM sbtest%u as sb1 JOIN sbtest1 as sb2 ON sb1.id = (sb2.id+3) where sb1.id = ?", | |
t.INT | |
} | |
stmt_defs.double_fullscans = { | |
"SELECT sb1.id FROM sbtest%u as sb1 JOIN sbtest2 as sb2 ON (sb1.id-3) = (sb2.id+3) ORDER BY sb2.id LIMIT 2000", | |
} | |
stmt_defs.insert_large_text_mysql = { | |
"INSERT INTO sbtest%u (id, k, c, pad, large_text) VALUES (?, ?, ?, ?, repeat('x', ?))", | |
t.INT, t.INT, {t.CHAR, 120}, {t.CHAR, 60}, t.INT | |
} | |
stmt_defs.insert_large_text_pgsql = { | |
"INSERT INTO sbtest%u (id, k, c, pad, large_text) VALUES (?, ?, ?, ?, repeat('x', ?::int))", | |
t.INT, t.INT, {t.CHAR, 120}, {t.CHAR, 60}, t.INT | |
} | |
stmt_defs.join_fullscans = { | |
"SELECT sb1.id FROM sbtest%u as sb1 JOIN sbtest1 as sb2 ON sb1.id = (sb2.id+3) where sb1.id = ?", | |
t.INT | |
} | |
function execute_fullscans() | |
local tnum = get_table_num() | |
local i | |
param[tnum].fullscans[1]:set_rand_str("%###") | |
stmt[tnum].fullscans:execute() | |
end | |
function execute_join_fullscans() | |
local tnum = get_table_num() | |
local i | |
param[tnum].join_fullscans[1]:set(get_id()) | |
stmt[tnum].fullscans:execute() | |
end | |
function execute_double_fullscans() | |
local tnum = get_table_num() | |
local i | |
stmt[tnum].double_fullscans:execute() | |
end | |
function execute_delete_insert_large_text() | |
local tnum = get_table_num() | |
local id = get_id() | |
local k = get_id() | |
param[tnum].deletes[1]:set(id) | |
local driver = "mysql" | |
if sysbench.opt.db_driver == "pgsql" then driver = "pgsql" end | |
local insert_large_text = param[tnum]["insert_large_text_"..driver] | |
insert_large_text[1]:set(id) | |
insert_large_text[2]:set(k) | |
insert_large_text[3]:set("##") | |
insert_large_text[4]:set("##") | |
insert_large_text[5]:set(sysbench.rand.uniform(sysbench.opt.large_text_size_min*1024*1024, sysbench.opt.large_text_size_max*1024*1024)) | |
stmt[tnum].deletes:execute() | |
stmt[tnum]["insert_large_text_"..driver]:execute() | |
end | |
function prepare() | |
cmd_prepare() | |
local con=sysbench.sql.driver():connect() | |
for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables, sysbench.opt.threads do | |
print("Adding large_text column on sbtest"..i) | |
local type='LONGTEXT' | |
if sysbench.opt.db_driver == "pgsql" then type='TEXT' end | |
con:query("ALTER TABLE sbtest"..i.." ADD COLUMN large_text "..type) | |
if sysbench.opt.db_driver == "mysql" and sysbench.opt.fulltext then | |
con:query("ALTER TABLE sbtest"..i.." ADD FULLTEXT INDEX sbtest"..i.."_ft_idx(large_text)") | |
end | |
end | |
end | |
sysbench.cmdline.commands = { | |
prepare = {prepare, sysbench.cmdline.PARALLEL_COMMAND}, | |
} | |
-- max_ref_from a list of tests, with "ref" defined | |
-- See event(), it is used to stop event execution earlier | |
function max_ref_from(ts) | |
mr = 0.0 | |
for _,v in pairs(ts) do | |
if (mr < v.ref) then | |
mr = v.ref | |
end | |
end | |
return mr | |
end | |
-- add_individual_transaction add begin/commit for individual actions if we did not specify to group actions in a single transaction | |
-- It expects and returns an array | |
function add_individual_transaction(fs) | |
if sysbench.opt.group_trx then return fs end | |
arr = { begin } | |
for _, f in pairs(fs) do arr[#arr+1] = f end | |
arr[#arr+1] = commit; | |
return arr | |
end | |
function reads(ref) | |
if ref < sysbench.opt.double_fullscan_prct then execute_double_fullscans(); return end | |
local ref_fullscans = sysbench.opt.double_fullscan_prct + sysbench.opt.fullscan_prct | |
if ref < ref_fullscans then execute_fullscans(); return end | |
local ref_join_fullscans = ref_fullscans + sysbench.opt.join_fullscan_prct | |
if ref < ref_join_fullscans then execute_join_fullscans(); return end | |
execute_point_selects() | |
end | |
function inserts(ref) | |
if ref < sysbench.opt.insert_large_text_prct then execute_delete_insert_large_text(); return end | |
execute_delete_inserts() | |
end | |
-- prepare_statements must be defined for sysbench to work | |
-- Everything can be setup here | |
function prepare_statements() | |
prepare_begin() | |
prepare_commit() | |
sysbench.opt.point_selects=1 | |
prepare_point_selects() | |
prepare_index_updates() | |
prepare_non_index_updates() | |
prepare_delete_inserts() | |
prepare_for_each_table("fullscans") | |
prepare_for_each_table("join_fullscans") | |
prepare_for_each_table("double_fullscans") | |
if sysbench.opt.db_driver == "pgsql" then | |
prepare_for_each_table("insert_large_text_pgsql") | |
else | |
prepare_for_each_table("insert_large_text_mysql") | |
end | |
if sysbench.opt.group_trx then sysbench.opt.skip_trx = false end | |
-- variables are global if not explicitely local | |
tests = { | |
{ref = sysbench.opt.read_prct, need_ref = true, funcs = {reads}}, | |
{ref = sysbench.opt.delete_prct/2, need_ref = true, funcs = {inserts}}, | |
{ref = sysbench.opt.update_prct/2, funcs = add_individual_transaction({execute_index_updates, execute_non_index_updates})}, | |
} | |
max_ref = max_ref_from(tests) | |
end | |
-- is_below compare two doubles | |
function is_below(ref, rand) | |
return ref >= rand | |
end | |
-- event is the main function executed during benchmark | |
function event() | |
local rand = sysbench.rand.uniform(1, 10000000)/10000000 | |
-- If returned random is above every tests "ref", none of our tests will be triggered, future iterations are useless. | |
if (not is_below(max_ref, rand)) then | |
return | |
end | |
if sysbench.opt.group_trx then begin() end | |
for _, v in pairs(tests) do | |
if (is_below(v.ref, rand)) then | |
if v.need_ref then for _, f in pairs(v.funcs) do f(rand) end | |
else for _, f in pairs(v.funcs) do f() end | |
end | |
end | |
end | |
if sysbench.opt.group_trx then commit() end | |
end | |
sysbench.hooks.report_intermediate = | |
function (stat) | |
if (not sysbench.opt.verbose_display) then | |
return sysbench.report_default(stat) | |
end | |
local seconds = stat.time_interval | |
-- This is derived from sysbench.report_default, formulas were copy-pasted | |
local total_reqs = (stat.reads + stat.writes + stat.other) / seconds | |
local total_reads = stat.reads / seconds | |
local total_writes = stat.writes / seconds | |
local total_rw = total_reads + total_writes | |
local total_others = stat.other / seconds | |
local errs = stat.errors / seconds | |
print(string.format( | |
"[ %.0fs ] thds: %u | " .. | |
"qps: " .. get_threshold_color(total_reqs) .. "%4.2f" .. get_color("reset").. " | " .. | |
"r: %4.2f(" .. get_color("blue") .."%4.2f %%" .. get_color("reset") .. ") | " .. | |
"w: %4.2f(" .. get_color("blue") .."%4.2f %%" .. get_color("reset") .. ") | " .. | |
"o: %4.2f | " .. | |
"err/s: " .. alert_if_above_zero(errs) .. "%4.2f" .. get_color("reset"), | |
stat.time_total, | |
stat.threads_running, | |
total_reqs, | |
total_reads, | |
total_reads / total_rw * 100, | |
total_writes, | |
total_writes / total_rw * 100, | |
total_others, | |
errs | |
)) | |
end | |
--[[ | |
sysbench.hooks.report_cumulative = function (stat) | |
-- function final_report_csv(stat) | |
-- One day | |
--[[ if not sysbench.opt.csv_output then | |
return sysbench.report_cumulative(stat) | |
end | |
--]] | |
--[[-- | |
print(string.format( | |
"%.0f,%u,%u,%u," .. | |
"%4.2f,%4.2f,%4.2f," .. | |
"%4.2f,%4.2f,%4.2f," .. | |
"%4.2f,%4.2f,%4.2f," .. | |
"%4.2f", | |
stat.time_total, sysbench.opt.threads, | |
sysbench.opt.tables, sysbench.opt.table_size, | |
sysbench.opt.read_prct, sysbench.opt.update_prct, sysbench.opt.delete_prct, | |
stat.reads, stat.writes, stat.other, | |
stat.latency_min, stat.latency_max, stat.latency_avg, | |
stat.errors | |
)) | |
end | |
--]] | |
-- We can find additional colors, http://lua-users.org/wiki/AnsiTerminalColors | |
local colors = { | |
reset = 0, | |
red = 31, | |
green = 32, | |
yellow = 33, | |
blue = 34, | |
magenta = 35, | |
cyan = 36, | |
white = 37, | |
} | |
function get_color(color) | |
if (sysbench.opt.disable_color) then | |
return "" | |
end | |
return "\27["..colors[color].."m" | |
end | |
local thresholds_colors = { | |
"red", | |
"yellow", | |
} | |
function alert_if_above_zero(v) | |
if (v > 0) then return get_color("red") else return "" end | |
end | |
function get_threshold_color(v) | |
local i = 1; | |
-- split by commas | |
for s in string.gmatch(sysbench.opt.thresholds, "([^,]+)") do | |
if (v <= tonumber(s)) then | |
return get_color(thresholds_colors[i]) | |
end | |
i = i + 1 | |
end | |
return get_color("reset") | |
end | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment