在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脚本,不过万变不离其宗,凑合看下~ 1、common.lua脚本 这个脚本主要功能是用来准备测试的表、测试数据、初始化测试中需要的一些默认值、清除数据。function prepare()会在全局command为perpare时调用,function cleanup()会在全局command为cleanup时调用,而function set_vars()会被引用至自己本身以及其他lua脚本中。 这个脚本就是默认的oltp测试时使用的测试脚本。结合common.lua中初始化的参数,此脚本每个事务中执行了10次基于主键的简单查询,1次范围查询,一次求和计算,一次排序查询,一次去重加排序查询,一次小字段更新,一次长字段更新,一次插入。而且在这个脚本中根据oltp_skip_trx oltp_read_only 这两个参数的限制不同需要执行的语句块也不同。 了解了这两个脚本之后我们就可以根据业务来定制自己的lua脚本了。 场景:在一个业务中有几个简单的主键查询,一个转账扣款的事务,一个插入语句语句分别是: 那么脚本就可以这么定制: 然后另存为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方面的内容,感兴趣的朋友可以关注下~概述
一、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
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
二、自定义lua脚本进行oltp性能测试
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