lua table insert_超详细的sysbench oltp-数据库性能测试中lua脚本解剖

概述

在sysbench0.4的后期版本中sysbench已经取消了test中的oltp模式，换而代之的是oltp的lua脚本。这一改变大大的提升了sysbench的灵活性。用户可以结合业务来定制lua脚本，这样能更精确的测试出适用于此业务的数据库性能指标。

今天主要使用sysbench-1.0.18来看看默认的lua脚本做了哪些工作，以及我们怎么来定制lua脚本。

我这边的oltp的测试脚本存放在/usr/local/sysbench/share/sysbench/tests/include/oltp_legacy下，这个目录下有很多脚本，在oltp基准测试中我们用到比较多的是common.lua和oltp.lua。虽然不会lua脚本，不过万变不离其宗，凑合看下~

---

一、lua脚本分析

1、common.lua脚本

-- Input parameters-- oltp-tables-count - number of tables to create-- oltp-secondary - use secondary key instead PRIMARY key for id column---- 创建表，插入测试数据function create_insert(table_id) local index_name local i local j local query if (oltp_secondary) then index_name = "KEY xid" else index_name = "PRIMARY KEY" end if (pgsql_variant == 'redshift') then auto_inc_type = "INTEGER IDENTITY(1,1)" else auto_inc_type = "SERIAL" end i = table_id print("Creating table 'sbtest" .. i .. "'...") if ((db_driver == "mysql") or (db_driver == "attachsql")) then query = [[CREATE TABLE sbtest]] .. i .. [[ (id INTEGER UNSIGNED NOT NULL ]] ..((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,k INTEGER UNSIGNED DEFAULT '0' NOT NULL,c CHAR(120) DEFAULT '' NOT NULL,pad CHAR(60) DEFAULT '' NOT NULL,]] .. index_name .. [[ (id)) /*! ENGINE = ]] .. mysql_table_engine .." MAX_ROWS = " .. myisam_max_rows .. " */ " .. (mysql_table_options or "") elseif (db_driver == "pgsql") then query = [[CREATE TABLE sbtest]] .. i .. [[ (id ]] .. auto_inc_type .. [[ NOT NULL,k INTEGER DEFAULT '0' NOT NULL,c CHAR(120) DEFAULT '' NOT NULL,pad CHAR(60) DEFAULT '' NOT NULL,]] .. index_name .. [[ (id)) ]] elseif (db_driver == "drizzle") then query = [[CREATE TABLE sbtest (id INTEGER NOT NULL ]] .. ((oltp_auto_inc and "AUTO_INCREMENT") or "") .. [[,k INTEGER DEFAULT '0' NOT NULL,c CHAR(120) DEFAULT '' NOT NULL,pad CHAR(60) DEFAULT '' NOT NULL,]] .. index_name .. [[ (id)) ]] else print("Unknown database driver: " .. db_driver) return 1 end db_query(query) print("Inserting " .. oltp_table_size .. " records into 'sbtest" .. i .. "'") if (oltp_auto_inc) then db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(k, c, pad) VALUES") else db_bulk_insert_init("INSERT INTO sbtest" .. i .. "(id, k, c, pad) VALUES") end local c_val local pad_val for j = 1,oltp_table_size do c_val = sb_rand_str([[###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]]) pad_val = sb_rand_str([[###########-###########-###########-###########-###########]]) if (oltp_auto_inc) then db_bulk_insert_next("(" .. sb_rand(1, oltp_table_size) .. ", '".. c_val .."', '" .. pad_val .. "')") else db_bulk_insert_next("("..j.."," .. sb_rand(1, oltp_table_size) .. ",'".. c_val .."', '" .. pad_val .. "' )") end end db_bulk_insert_done() if oltp_create_secondary then print("Creating secondary indexes on 'sbtest" .. i .. "'...") db_query("CREATE INDEX k_" .. i .. " on sbtest" .. i .. "(k)") endend-- prepare阶段执行的操作function prepare() local query local i local j set_vars() db_connect() for i = 1,oltp_tables_count do create_insert(i) end return 0end-- cleanup阶段执行的操作function cleanup() local i set_vars() for i = 1,oltp_tables_count do print("Dropping table 'sbtest" .. i .. "'...") db_query("DROP TABLE IF EXISTS sbtest".. i ) endend-- 初始化默认值function set_vars() oltp_table_size = tonumber(oltp_table_size) or 10000 oltp_range_size = tonumber(oltp_range_size) or 100 oltp_tables_count = tonumber(oltp_tables_count) or 1 oltp_point_selects = tonumber(oltp_point_selects) or 10 oltp_simple_ranges = tonumber(oltp_simple_ranges) or 1 oltp_sum_ranges = tonumber(oltp_sum_ranges) or 1 oltp_order_ranges = tonumber(oltp_order_ranges) or 1 oltp_distinct_ranges = tonumber(oltp_distinct_ranges) or 1 oltp_index_updates = tonumber(oltp_index_updates) or 1 oltp_non_index_updates = tonumber(oltp_non_index_updates) or 1 oltp_delete_inserts = tonumber(oltp_delete_inserts) or 1 if (oltp_range_selects == 'off') then oltp_range_selects = false else oltp_range_selects = true end if (oltp_auto_inc == 'off') then oltp_auto_inc = false else oltp_auto_inc = true end if (oltp_read_only == 'on') then oltp_read_only = true else oltp_read_only = false end if (oltp_write_only == 'on') then oltp_write_only = true else oltp_write_only = false end if (oltp_read_only and oltp_write_only) then error("--oltp-read-only and --oltp-write-only are mutually exclusive") end if (oltp_skip_trx == 'on') then oltp_skip_trx = true else oltp_skip_trx = false end if (oltp_create_secondary == 'off') then oltp_create_secondary = false else oltp_create_secondary = true end if (pgsql_variant == 'redshift') then oltp_create_secondary = false oltp_delete_inserts = 0 endend

这个脚本主要功能是用来准备测试的表、测试数据、初始化测试中需要的一些默认值、清除数据。function prepare()会在全局command为perpare时调用，function cleanup()会在全局command为cleanup时调用，而function set_vars()会被引用至自己本身以及其他lua脚本中。

2、oltp.lua脚本

pathtest = string.match(test, "(.*/)")-- 引入common.lua脚本if pathtest then dofile(pathtest .. "common.lua")else require("common")end--申明在满足db_driver=="mysql"/"attachsql"和mysql_table_engine == "myisam"条件时测试使用锁表开头，解除锁表结尾。--其他全部使用BEGIN开始，commit结束(事务)function thread_init() set_vars() if (((db_driver == "mysql") or (db_driver == "attachsql")) and mysql_table_engine == "myisam") then local i local tables = {} for i=1, oltp_tables_count do tables[i] = string.format("sbtest%i WRITE", i) end begin_query = "LOCK TABLES " .. table.concat(tables, " ,") commit_query = "UNLOCK TABLES" else begin_query = "BEGIN" commit_query = "COMMIT" endendfunction get_range_str() local start = sb_rand(1, oltp_table_size) return string.format(" WHERE id BETWEEN %u AND %u", start, start + oltp_range_size - 1)end-- 开始测试function event() local rs local i local table_name local c_val local pad_val local query -- 随机获取表名后缀 table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count) --如果指定跳过事务参数为ON 则不调用事务开始标示 if not oltp_skip_trx then db_query(begin_query) end if not oltp_write_only then -- 开始执行执行查询语句 for i=1, oltp_point_selects do rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size)) end if oltp_range_selects then for i=1, oltp_simple_ranges do rs = db_query("SELECT c FROM ".. table_name .. get_range_str()) end for i=1, oltp_sum_ranges do rs = db_query("SELECT SUM(K) FROM ".. table_name .. get_range_str()) end for i=1, oltp_order_ranges do rs = db_query("SELECT c FROM ".. table_name .. get_range_str() .. " ORDER BY c") end for i=1, oltp_distinct_ranges do rs = db_query("SELECT DISTINCT c FROM ".. table_name .. get_range_str() .. " ORDER BY c") end end end -- 如果oltp_read_only=on 则跳过DML语句 if not oltp_read_only then -- 开始执行DML语句 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 for i=1, oltp_delete_inserts do 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 end -- oltp_read_only -- 如果指定跳过事务参数为ON，测不执行commit if not oltp_skip_trx then db_query(commit_query) endend

这个脚本就是默认的oltp测试时使用的测试脚本。结合common.lua中初始化的参数，此脚本每个事务中执行了10次基于主键的简单查询，1次范围查询，一次求和计算，一次排序查询，一次去重加排序查询，一次小字段更新，一次长字段更新，一次插入。而且在这个脚本中根据oltp_skip_trx oltp_read_only 这两个参数的限制不同需要执行的语句块也不同。

---

二、自定义lua脚本进行oltp性能测试

了解了这两个脚本之后我们就可以根据业务来定制自己的lua脚本了。

场景：在一个业务中有几个简单的主键查询，一个转账扣款的事务，一个插入语句语句分别是：

select id from test1 where id=:vid;select id from test2 where id=:vid;select id from test3 where id=:vid;start TRANSACTIONupdate test4 set k=k-1 where id=:vid;update test5 set k=k+1 where id=:vid;commit;insert into test6 (k,xv) values (:vk,:vxv);

那么脚本就可以这么定制：

pathtest = string.match(test, "(.*/)") or ""dofile(pathtest .. "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" else begin_query = "BEGIN" commit_query = "COMMIT" endendfunction event(thread_id) local vid1 local vid2 local vid3 local vid4 local vid5 local vk local vxv vid1 = sb_rand_uniform(1,10000) vid2 = sb_rand_uniform(1,10000) vid3 = sb_rand_uniform(1,10000) vid4 = sb_rand_uniform(1,10000) vid5 = sb_rand_uniform(1,10000) vk = sb_rand_uniform(10,10000) vxv = sb_rand_str([[###########-###########-###########-###########-###########]]) rs = db_query("SELECT pad FROM test1 WHERE id=" .. vid1) rs = db_query("SELECT pad FROM test2 WHERE id=" .. vid2) rs = db_query("SELECT pad FROM test3 WHERE id=" .. vid3) db_query(begin_query) rs = db_query("update test4 set k=k-1 where id=" .. vid4) rs = db_query("update test5 set k=k+1 where id=" .. vid5) db_query(commit_query) rs = db_query("insert into test6 (k,xv) values " .. string.format("(%d , '%s')",vk,vxv));end

然后另存为mytest.lua，接下来就可以使用如下命令来进行测试了：

./sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --test=mytest.lua --num-threads=128 --report-interval=10 --rand-type=uniform --max-time=600 --max-requests=0 run

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~