概述

在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)")
 end

end

-- 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 0
end

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

-- 初始化默认值
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
 end

end

这个脚本主要功能是用来准备测试的表、测试数据、初始化测试中需要的一些默认值、清除数据。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"
 end

end

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

end

这个脚本就是默认的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 TRANSACTION
update 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"
 end
end

function 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方面的内容，感兴趣的朋友可以关注下~