oltp_rw_percent_large_text_compressed

gistfile1.txt

#!/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 (?, ?, ?, ?, ?)",
	t.INT, t.INT, {t.CHAR, 120}, {t.CHAR, 60}, {t.CHAR, 4000} 
}

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()
	tnum = get_table_num()

	param[tnum].fullscans[1]:set_rand_str("%###")

	stmt[tnum].fullscans:execute()
end

function execute_join_fullscans()
	tnum = get_table_num()

	param[tnum].join_fullscans[1]:set(get_id())

	stmt[tnum].fullscans:execute()
end

function execute_double_fullscans()
	tnum = get_table_num()

	stmt[tnum].double_fullscans:execute()
end

function execute_delete_insert_large_text()
	tnum = get_table_num()
	id = get_id()
	k = get_id()
	param[tnum].deletes[1]:set(id)

	driver = "mysql"
	if sysbench.opt.db_driver == "pgsql" then driver = "pgsql" end

	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(string.rep("##", 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()
	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)
		
		type='LONGTEXT'
		if sysbench.opt.db_driver == "pgsql" then type='TEXT' end

		con:query("ALTER TABLE sbtest"..i.." ADD COLUMN large_text "..type)
		print("Compressing sbtest"..i)
		con:query("ALTER TABLE sbtest"..i.." ROW_FORMAT=compressed")
		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
	
	ref_fullscans =  sysbench.opt.double_fullscan_prct + sysbench.opt.fullscan_prct
	if ref < ref_fullscans then execute_fullscans(); return end

	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()
	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

		seconds = stat.time_interval
		-- This is derived from sysbench.report_default, formulas were copy-pasted 
		total_reqs = (stat.reads + stat.writes + stat.other) / seconds
		total_reads = stat.reads / seconds
		total_writes = stat.writes / seconds
		total_rw = total_reads + total_writes
		total_others = stat.other / seconds
		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
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

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)
	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