1、在上一篇中查看了common.lua脚本的相关内容，下面会介绍oltp.lua脚本
2、oltp.lua

function thread_init(thread_id)
set_vars()
if (((db_driver == "mysql") or (db_driver == "attachsql")) 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

2.1、在线程初始化的时候，如果数据库是mysql且表的存储引擎为myisam的时候，需要先锁表，在commit之后unlock

for i=1, oltp_point_selects do
rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
end
for i=1, oltp_simple_ranges do
range_start = sb_rand(1, oltp_table_size)
rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
end
for i=1, oltp_sum_ranges do
range_start = sb_rand(1, oltp_table_size)
rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
end
for i=1, oltp_order_ranges do
range_start = sb_rand(1, oltp_table_size)
rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
end

   for i=1, oltp_distinct_ranges do
      range_start = sb_rand(1, oltp_table_size)
      rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
   end

2.2、第1个测试语句select，是主键等值查询；
2.3、第2个测试语句select，是主键范围查询；
2.4、第3个测试语句select，是主键范围查询，并为列k计算求和；
2.5、第4个测试语句select，是主键范围查询，且为列c排序；
2.6、第5个测试语句select，是主键范围查询，且找出c列的distinct值，并排序
2.7、下面代码显示的是，如果oltp应用不是只读的，就会有DML操作，这里就不一个个介绍了，代码比较简单。

if not oltp_read_only then
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
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 -- oltp_read_only
if not oltp_skip_trx then
db_query(commit_query)
end
end

ITPub博客

测试软件：sysbench-oltp（2）

最新文章