作者：じ☆ve宝贝
发布时间：2017-11-24T17:49:25
my_common.lua
-- Copyright (C) 2006-2017 Alexey Kopytov <akopytov@gmail.com>
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
-- -----------------------------------------------------------------------------
-- Common code for OLTP benchmarks.
-- -----------------------------------------------------------------------------
function init()
assert(event ~= nil,
"this script is meant to be included by other OLTP scripts and " ..
"should not be called directly.")
end
if sysbench.cmdline.command == nil then
error("Command is required. Supported commands: prepare, prewarm, run, " ..
"cleanup, help")
end
-- Command line options
sysbench.cmdline.options = {
table_size =
{"Number of rows per table", 10000},
range_size =
{"Range size for range SELECT queries", 100},
tables =
{"Number of tables", 1},
point_selects =
{"Number of point SELECT queries per transaction", 10},
simple_ranges =
{"Number of simple range SELECT queries per transaction", 1},
sum_ranges =
{"Number of SELECT SUM() queries per transaction", 1},
order_ranges =
{"Number of SELECT ORDER BY queries per transaction", 1},
distinct_ranges =
{"Number of SELECT DISTINCT queries per transaction", 1},
index_updates =
{"Number of UPDATE index queries per transaction", 1},
non_index_updates =
{"Number of UPDATE non-index queries per transaction", 1},
delete_inserts =
{"Number of DELETE/INSERT combination per transaction", 1},
range_selects =
{"Enable/disable all range SELECT queries", true},
auto_inc =
{"Use AUTO_INCREMENT column as Primary Key (for MySQL), " ..
"or its alternatives in other DBMS. When disabled, use " ..
"client-generated IDs", true},
skip_trx =
{"Don't start explicit transactions and execute all queries " ..
"in the AUTOCOMMIT mode", false},
secondary =
{"Use a secondary index in place of the PRIMARY KEY", false},
create_secondary =
{"Create a secondary index in addition to the PRIMARY KEY", true},
mysql_storage_engine =
{"Storage engine, if MySQL is used", "myisam"},
pgsql_variant =
{"Use this PostgreSQL variant when running with the " ..
"PostgreSQL driver. The only currently supported " ..
"variant is 'redshift'. When enabled, " ..
"create_secondary is automatically disabled, and " ..
"delete_inserts is set to 0"}
}
-- Prepare the dataset. This command supports parallel execution, i.e. will
-- benefit from executing with --threads > 1 as long as --tables > 1
function cmd_prepare()
local drv = sysbench.sql.driver()
local con = drv:connect()
for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables,
sysbench.opt.threads do
create_table(drv, con, i)
end
end
-- Preload the dataset into the server cache. This command supports parallel
-- execution, i.e. will benefit from executing with --threads > 1 as long as
-- --tables > 1
--
-- PS. Currently, this command is only meaningful for MySQL/InnoDB benchmarks
function cmd_prewarm()
local drv = sysbench.sql.driver()
local con = drv:connect()
assert(drv:name() == "mysql", "prewarm is currently MySQL only")
-- Do not create on disk tables for subsequent queries
con:query("SET tmp_table_size=2*1024*1024*1024")
con:query("SET max_heap_table_size=2*1024*1024*1024")
for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables,
sysbench.opt.threads do
local t = "sbtest" .. i
print("Prewarming table " .. t)
con:query("ANALYZE TABLE sbtest" .. i)
con:query(string.format(
"SELECT AVG(id) FROM " ..
"(SELECT * FROM %s FORCE KEY (PRIMARY) " ..
"LIMIT %u) t",
t, sysbench.opt.table_size))
con:query(string.format(
"SELECT COUNT(*) FROM " ..
"(SELECT * FROM %s WHERE cn_name LIKE '%%0%%' LIMIT %u) t",
t, sysbench.opt.table_size))
end
end
-- Implement parallel prepare and prewarm commands
sysbench.cmdline.commands = {
prepare = {cmd_prepare, sysbench.cmdline.PARALLEL_COMMAND},
prewarm = {cmd_prewarm, sysbench.cmdline.PARALLEL_COMMAND}
}
-- Template strings of random digits with 11-digit groups separated by dashes
-- 10 groups, 119 characters
local c_value_template = "###########-###########-###########-" ..
"###########-###########-###########-" ..
"###########-###########-###########-" ..
"###########"
-- 5 groups, 59 characters
local pad_value_template = "###########-###########-###########-" ..
"###########-###########"
function get_c_value()
return sysbench.rand.string(c_value_template)
end
function get_pad_value()
return sysbench.rand.string(pad_value_template)
end
function create_table(drv, con, table_num)
local id_index_def, id_def
local engine_def = ""
local extra_table_options = ""
local query
if sysbench.opt.secondary then
id_index_def = "KEY xid"
else
id_index_def = "PRIMARY KEY"
end
if drv:name() == "mysql" or drv:name() == "attachsql" or
drv:name() == "drizzle"
then
if sysbench.opt.auto_inc then
id_def = "INTEGER NOT NULL AUTO_INCREMENT"
else
id_def = "INTEGER NOT NULL"
end
engine_def = "/*! ENGINE = " .. sysbench.opt.mysql_storage_engine .. " */"
extra_table_options = mysql_table_options or ""
elseif drv:name() == "pgsql"
then
if not sysbench.opt.auto_inc then
id_def = "INTEGER NOT NULL"
elseif pgsql_variant == 'redshift' then
id_def = "INTEGER IDENTITY(1,1)"
else
id_def = "SERIAL"
end
else
error("Unsupported database driver:" .. drv:name())
end
print(string.format("Creating table 'sbtest%d'...", table_num))
query = string.format([[
CREATE TABLE sbtest%d(
`id` char(100) DEFAULT NULL,
`cn_name` varchar(200) DEFAULT NULL COMMENT '中文名',
`cn_short` varchar(200) DEFAULT NULL COMMENT '中文简称',
`en_name` varchar(200) DEFAULT NULL COMMENT '英文名',
`en_short` varchar(200) DEFAULT NULL COMMENT '英文简称',
`cn_once` varchar(200) DEFAULT NULL COMMENT '曾用名',
`credit_code` varchar(200) DEFAULT NULL COMMENT '统一信用代码',
`reg_institute` varchar(200) DEFAULT NULL COMMENT '登记机关',
`scale` varchar(200) DEFAULT NULL COMMENT '公司规模',
`organize_code` varchar(200) DEFAULT NULL COMMENT '企业注册码',
`registration_number` varchar(200) DEFAULT NULL COMMENT '工商注册号',
`legal_person` varchar(200) DEFAULT NULL COMMENT '法人',
`company_type` varchar(200) DEFAULT NULL COMMENT '企业控股: 1：',
`register_type` varchar(200) DEFAULT NULL COMMENT '企业注册类型 ',
`estiblish_time` varchar(200) DEFAULT NULL COMMENT '营业期限开始',
`to_time` varchar(200) DEFAULT NULL COMMENT '营业期限截止',
`check_date` varchar(200) DEFAULT NULL COMMENT '发照日期',
`area_type` varchar(200) DEFAULT NULL COMMENT '地区',
`reg_location` varchar(200) DEFAULT NULL COMMENT '企业地址 ',
`business_scope` varchar(200) DEFAULT NULL COMMENT '经营范围',
`web_url` varchar(200) DEFAULT NULL COMMENT '网址',
`company_logo` varchar(200) DEFAULT NULL COMMENT 'logo',
`cn_desc` varchar(200) DEFAULT NULL COMMENT '中文简介',
`en_desc` varchar(200) DEFAULT NULL COMMENT '中文简介',
`begin_time` varchar(200) DEFAULT NULL COMMENT '成立时间',
`stop_time` varchar(200) DEFAULT NULL COMMENT '停业时间',
`registered_capital` varchar(200) DEFAULT NULL COMMENT '注册资本',
`currency_type` varchar(200) DEFAULT NULL COMMENT '注册资本货币类型 ',
`commerce_status` varchar(200) DEFAULT NULL COMMENT '商业状态',
`remark` varchar(510) DEFAULT NULL,
`creater_id` varchar(200) DEFAULT NULL COMMENT '修改人',
`create_time` varchar(200) DEFAULT NULL COMMENT '创建时间',
`status` varchar(200) DEFAULT NULL COMMENT '1：正常 2删除 3...',
`modifier_id` varchar(200) DEFAULT NULL COMMENT '修改人',
`modify_time` varchar(200) DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (id)
) %s %s]],
table_num, engine_def, extra_table_options)
con:query(query)
if (sysbench.opt.table_size > 0) then
print(string.format("Inserting %d records into 'sbtest%d'",
sysbench.opt.table_size, table_num))
end
if sysbench.opt.auto_inc then
query = "INSERT INTO sbtest" .. table_num .. "(id, cn_name, cn_short, en_name, en_short, cn_once, credit_code, reg_institute, scale, organize_code, registration_number, legal_person, company_type, register_type, estiblish_time, to_time, check_date, area_type, reg_location, business_scope, web_url, company_logo, cn_desc, en_desc, begin_time, stop_time, registered_capital, currency_type, commerce_status, remark, creater_id, create_time, status, modifier_id, modify_time) VALUES"
else
query = "INSERT INTO sbtest" .. table_num .. "(id, cn_name, cn_short, en_name, en_short, cn_once, credit_code, reg_institute, scale, organize_code, registration_number, legal_person, company_type, register_type, estiblish_time, to_time, check_date, area_type, reg_location, business_scope, web_url, company_logo, cn_desc, en_desc, begin_time, stop_time, registered_capital, currency_type, commerce_status, remark, creater_id, create_time, status, modifier_id, modify_time) VALUES"
end
con:bulk_insert_init(query)
local c_val
local pad_val
for i = 1, sysbench.opt.table_size do
c_val = get_c_value()
pad_val = get_pad_value()
if (sysbench.opt.auto_inc) then
query = string.format("('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),get_pad_value(),get_pad_value(),getTimeStamp(),
sb_rand(1, sysbench.opt.table_size), c_val,get_pad_value(),getTimeStamp(),get_pad_value(),getTimeStamp(),get_pad_value(),getTimeStamp(),
pad_val,guid(),get_pad_value(),get_pad_value(),getTimeStamp(),
sb_rand(1, sysbench.opt.table_size),guid(),get_pad_value(),get_pad_value(),getTimeStamp(),
sb_rand(1, sysbench.opt.table_size),get_pad_value(),get_pad_value(),getTimeStamp())
else
query = string.format("('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),guid(),get_pad_value(),get_pad_value(),getTimeStamp(),
sb_rand(1, sysbench.opt.table_size), c_val,get_pad_value(),getTimeStamp(),get_pad_value(),getTimeStamp(),get_pad_value(),getTimeStamp(),
pad_val,guid(),get_pad_value(),get_pad_value(),getTimeStamp(),
sb_rand(1, sysbench.opt.table_size),guid(),get_pad_value(),get_pad_value(),getTimeStamp(),
sb_rand(1, sysbench.opt.table_size),get_pad_value(),get_pad_value(),getTimeStamp())
end
con:bulk_insert_next(query)
end
con:bulk_insert_done()
if sysbench.opt.create_secondary then
print(string.format("Creating a secondary index on 'sbtest%d'...",
table_num))
con:query(string.format("CREATE INDEX k_%d ON sbtest%d(cn_name)",
table_num, table_num))
end
end
local t = sysbench.sql.type
local stmt_defs = {
point_selects = {
"SELECT cn_name FROM sbtest%u WHERE estiblish_time=?",
{t.VARCHAR, 100}},
simple_ranges = {
"SELECT cn_name FROM sbtest%u WHERE estiblish_time BETWEEN ? AND ?",
{t.VARCHAR, 100}, {t.VARCHAR, 100}},
sum_ranges = {
"SELECT SUM(cn_short) FROM sbtest%u WHERE estiblish_time BETWEEN ? AND ?",
{t.VARCHAR, 100}, {t.VARCHAR, 100}},
order_ranges = {
"SELECT cn_name FROM sbtest%u WHERE estiblish_time BETWEEN ? AND ? ORDER BY cn_name",
{t.VARCHAR, 100}, {t.VARCHAR, 100}},
distinct_ranges = {
"SELECT DISTINCT cn_name FROM sbtest%u WHERE estiblish_time BETWEEN ? AND ? ORDER BY cn_name",
{t.VARCHAR, 100}, {t.VARCHAR, 100}},
index_updates = {
"UPDATE sbtest%u SET en_name='test' WHERE estiblish_time=?",
{t.VARCHAR, 100}},
non_index_updates = {
"UPDATE sbtest%u SET cn_name='non_index_update' WHERE estiblish_time=?",
{t.VARCHAR, 100}},
deletes = {
"DELETE FROM sbtest%u WHERE estiblish_time=?",
{t.VARCHAR, 100}},
inserts = {
"INSERT INTO sbtest%u (id, cn_name, cn_short, en_name, estiblish_time) VALUES (?, ?, ?, ?, ?)",
{t.VARCHAR, 100}, {t.VARCHAR, 200}, {t.VARCHAR, 200}, {t.VARCHAR, 200}, {t.VARCHAR, 200}},
}
function prepare_begin()
stmt.begin = con:prepare("BEGIN")
end
function prepare_commit()
stmt.commit = con:prepare("COMMIT")
end
function prepare_for_each_table(key)
for t = 1, sysbench.opt.tables do
stmt[t][key] = con:prepare(string.format(stmt_defs[key][1], t))
-- print(string.format(stmt_defs[key][1], t))
local nparam = #stmt_defs[key] - 1
if nparam > 0 then
param[t][key] = {}
end
-- print(string.format('----byte,typeby=%s,len=%s,key=%s',type(btype),len,key))
for p = 1, nparam do
local btype = stmt_defs[key][p+1]
local len
if type(btype) == "table" then
len = btype[2]
btype = btype[1]
end
-- print(string.format('%s,%s---', btype,sysbench.sql.type.VARCHAR))
if btype == sysbench.sql.type.VARCHAR or
btype == sysbench.sql.type.CHAR then
-- print(string.format('----cahr,typeby=%s,key=%s,btype=%s,len=%s',type(btype),key,btype,len))
param[t][key][p] = stmt[t][key]:bind_create(btype, len)
else
-- print(string.format('----nochar,typeby=%s,key=%s,btype=%s',type(btype),key,btype))
param[t][key][p] = stmt[t][key]:bind_create(btype)
end
end
if nparam > 0 then
stmt[t][key]:bind_param(unpack(param[t][key]))
end
end
end
function prepare_point_selects()
prepare_for_each_table("point_selects")
end
function prepare_simple_ranges()
prepare_for_each_table("simple_ranges")
end
function prepare_sum_ranges()
prepare_for_each_table("sum_ranges")
end
function prepare_order_ranges()
prepare_for_each_table("order_ranges")
end
function prepare_distinct_ranges()
prepare_for_each_table("distinct_ranges")
end
function prepare_index_updates()
prepare_for_each_table("index_updates")
end
function prepare_non_index_updates()
prepare_for_each_table("non_index_updates")
end
function prepare_delete_inserts()
prepare_for_each_table("deletes")
prepare_for_each_table("inserts")
end
function thread_init()
drv = sysbench.sql.driver()
con = drv:connect()
-- Create global nested tables for prepared statements and their
-- parameters. We need a statement and a parameter set for each combination
-- of connection/table/query
stmt = {}
param = {}
for t = 1, sysbench.opt.tables do
stmt[t] = {}
param[t] = {}
end
-- This function is a 'callback' defined by individual benchmark scripts
prepare_statements()
end
function thread_done()
-- Close prepared statements
for t = 1, sysbench.opt.tables do
for k, s in pairs(stmt[t]) do
stmt[t][k]:close()
end
end
if (stmt.begin ~= nil) then
stmt.begin:close()
end
if (stmt.commit ~= nil) then
stmt.commit:close()
end
con:disconnect()
end
function cleanup()
local drv = sysbench.sql.driver()
local con = drv:connect()
for i = 1, sysbench.opt.tables do
print(string.format("Dropping table 'sbtest%d'...", i))
con:query("DROP TABLE IF EXISTS sbtest" .. i )
end
end
local function get_table_num()
return sysbench.rand.uniform(1, sysbench.opt.tables)
end
local function get_id()
return string.format(sysbench.rand.default(1, sysbench.opt.table_size))
end
function begin()
for t = 1, sysbench.opt.tables do
for k, s in pairs(stmt[t]) do
-- print(k,s)
end
end
stmt.begin:execute()
end
function commit()
stmt.commit:execute()
end
function execute_point_selects()
local tnum = get_table_num()
local i
for i = 1, sysbench.opt.point_selects do
param[tnum].point_selects[1]:set(get_id())
stmt[tnum].point_selects:execute()
end
end
local function execute_range(key)
local tnum = get_table_num()
for i = 1, sysbench.opt[key] do
local id = get_id()
param[tnum][key][1]:set(id)
param[tnum][key][2]:set(guid())
stmt[tnum][key]:execute()
end
end
function execute_simple_ranges()
execute_range("simple_ranges")
end
function execute_sum_ranges()
execute_range("sum_ranges")
end
function execute_order_ranges()
execute_range("order_ranges")
end
function execute_distinct_ranges()
execute_range("distinct_ranges")
end
function execute_index_updates()
local tnum = get_table_num()
for i = 1, sysbench.opt.index_updates do
param[tnum].index_updates[1]:set(get_id())
stmt[tnum].index_updates:execute()
end
end
function execute_non_index_updates()
local tnum = get_table_num()
for i = 1, sysbench.opt.non_index_updates do
-- param[tnum].non_index_updates[1]:set_rand_str(c_value_template)
-- param[tnum].non_index_updates[2]:set(guid())
param[tnum].non_index_updates[1]:set(get_id())
stmt[tnum].non_index_updates:execute()
end
end
function execute_delete_inserts()
local tnum = get_table_num()
for i = 1, sysbench.opt.delete_inserts do
-- print(i)
local id = get_id()
local k = get_id()
local uid = get_id();
param[tnum].deletes[1]:set(uid)
param[tnum].inserts[1]:set(uid)
param[tnum].inserts[2]:set(k)
param[tnum].inserts[3]:set_rand_str(c_value_template)
param[tnum].inserts[4]:set_rand_str(pad_value_template)
param[tnum].inserts[5]:set(uid)
stmt[tnum].deletes:execute()
stmt[tnum].inserts:execute()
end
end
function guid()
local seed = {
'0','1','2','3','4','5','6','7','8','9',
'a','b','c','d','e','f','g','h','i','j',
'k','l','m','n','o','p','q','r','s','t',
'u','v','w','x','y','z'
}
local sid = ""
for i=1, 32 do
-- math.randomseed(tostring(os.time()):reverse():sub(1, 6))
sid = sid .. seed[random()]
end
return string.format('%s%s%s%s%s',
string.sub(sid, 1, 8),
string.sub(sid, 9, 12),
string.sub(sid, 13, 16),
string.sub(sid, 17, 20),
string.sub(sid, 21, 32)
)
end
function random()
local ret=0
math.randomseed(os.time())
for i=1,3 do
n = math.random(1,36)
ret=n
end
return ret
end
function getTimeStamp()
local t = os.time();
return os.date("%Y-%m-%d %H:%M:%S",t/1000)
end
#!/usr/bin/env sysbench
-- Copyright (C) 2006-2017 Alexey Kopytov <akopytov@gmail.com>
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
-- ----------------------------------------------------------------------
-- Read/Write OLTP benchmark
-- ----------------------------------------------------------------------
require("my_common")
function prepare_statements()
if not sysbench.opt.skip_trx then
prepare_begin()
prepare_commit()
end
prepare_point_selects()
if sysbench.opt.range_selects then
prepare_simple_ranges()
prepare_sum_ranges()
prepare_order_ranges()
prepare_distinct_ranges()
end
prepare_index_updates()
prepare_non_index_updates()
prepare_delete_inserts()
end
function event()
if not sysbench.opt.skip_trx then
begin()
end
execute_point_selects()
if sysbench.opt.range_selects then
execute_simple_ranges()
execute_sum_ranges()
execute_order_ranges()
execute_distinct_ranges()
end
execute_index_updates()
execute_non_index_updates()
execute_delete_inserts()
if not sysbench.opt.skip_trx then
commit()
end
end
#!/bin/bash
read -t 30 -p "请输入运行方式(prepare/run/cleanup)": status
sysbench /data/lua/my_read_write.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password='V5kNP47u28450Gpt' \
--mysql-db=sbtest \
--db-driver=mysql \
--tables=1 \
--table-size=1000000 \
--report-interval=10 \
--threads=128 \
--time=120 \
$status