sysbench执行结果说明

﹏ヽ暗。殇╰゛Y 2023-03-05 03:24 123阅读 0赞

General statistics（综合统计）  
total time: 10.0004s  
total number of events: 25875

CPU测试，在10s范围内进行素数计算这个行为一共进行了25875次，实际执行时间为10.0004s;

--------------------

latency(ms)  
min 0.36  
avg 0.39  
max 16.12  
95th percentile 0.44  
sum: 9884  
这部分数据应该统计的是线程真正执行的时间，总共9884.06ms, 单次执行最少时间为0.36ms,  
最多时间为16.12ms, 平均时间为0.39ms, 95%次的执行时间在0.44ms左右;

--------------------

Threads fairness:  
events (avg/stddev): 43084.0000/0.00  
execution time (avg/stddev): 9.8626/0.00

归纳总结，线程执行时间为9.8626s, 执行平均次数为43084次；

--------------------

TPS：Transactions Per Second，意思是每秒事务数，具体事务的定义，都是人为的，可以一个接口、多个接口、一个业务流程等等。一个事务是指事务内第一个请求发送到接收到最后一个请求的响应的过程，以此来计算使用的时间和完成的事务个数。

以单接口定义为事务为例，每个事务包括了如下3个过程：

a.向服务器发请求

b.服务器自己的内部处理（包含应用服务器、数据库服务器等）

c.服务器返回结果给客户端

如果每秒能够完成N次这三个过程，tps就是N；

如果多个接口定义为一个事务，那么，会重复执行abc，完成一次这几个请求，算做一个tps。

--------------------

QPS：Queries Per Second，意思是每秒查询率，是一台服务器每秒能够响应的查询次数（数据库中的每秒执行查询sql的次数），显然，这个不够全面，不能描述增删改，所以，不建议用qps来作为系统性能指标。

--------------------

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5ODg0NDEw_size_16_color_FFFFFF_t_70]

--------------------

生成数据脚本  
– Copyright © 2006-2018 Alexey Kopytov [akopytov@gmail.com][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 combinations 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”, “innodb”\},  
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 k 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 %s,  
k INTEGER DEFAULT ‘0’ NOT NULL,  
c CHAR(120) DEFAULT ‘’ NOT NULL,  
pad CHAR(60) DEFAULT ‘’ NOT NULL,  
%s (id)  
) %s %s\]\],  
table\_num, id\_def, id\_index\_def, engine\_def, extra\_table\_options)

con:query(query)

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(k)”,  
table\_num, table\_num))  
end

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 … “(k, c, pad) VALUES”  
else  
query = “INSERT INTO sbtest” … table\_num … “(id, k, c, pad) 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("(%d, '%s', '%s')",
                               sb_rand(1, sysbench.opt.table_size), c_val,
                               pad_val)
      else
         query = string.format("(%d, %d, '%s', '%s')",
                               i, sb_rand(1, sysbench.opt.table_size), c_val,
                               pad_val)
      end
    
      con:bulk_insert_next(query)

end

con:bulk\_insert\_done()  
end

local t = sysbench.sql.type  
local stmt\_defs = \{  
point\_selects = \{  
“SELECT c FROM sbtest%u WHERE id=?”,  
t.INT\},  
simple\_ranges = \{  
“SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?”,  
t.INT, t.INT\},  
sum\_ranges = \{  
“SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?”,  
t.INT, t.INT\},  
order\_ranges = \{  
“SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c”,  
t.INT, t.INT\},  
distinct\_ranges = \{  
“SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c”,  
t.INT, t.INT\},  
index\_updates = \{  
“UPDATE sbtest%u SET k=k+1 WHERE id=?”,  
t.INT\},  
non\_index\_updates = \{  
“UPDATE sbtest%u SET c=? WHERE id=?”,  
\{t.CHAR, 120\}, t.INT\},  
deletes = \{  
“DELETE FROM sbtest%u WHERE id=?”,  
t.INT\},  
inserts = \{  
“INSERT INTO sbtest%u (id, k, c, pad) VALUES (?, ?, ?, ?)”,  
t.INT, t.INT, \{t.CHAR, 120\}, \{t.CHAR, 60\}\},  
\}

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

local nparam = #stmt_defs[key] - 1
    
      if nparam > 0 then
         param[t][key] = {}
      end
    
      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
         if btype == sysbench.sql.type.VARCHAR or
            btype == sysbench.sql.type.CHAR then
               param[t][key][p] = stmt[t][key]:bind_create(btype, len)
         else
            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

– Close prepared statements  
function close\_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  
end

function thread\_done()  
close\_statements()  
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 sysbench.rand.default(1, sysbench.opt.table\_size)  
end

function begin()  
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(id + sysbench.opt.range_size - 1)
    
      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(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  
local id = get\_id()  
local k = get\_id()

param[tnum].deletes[1]:set(id)
    
      param[tnum].inserts[1]:set(id)
      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)
    
      stmt[tnum].deletes:execute()
      stmt[tnum].inserts:execute()

end  
end

– Re-prepare statements if we have reconnected, which is possible when some of  
– the listed error codes are in the --mysql-ignore-errors list  
function sysbench.hooks.before\_restart\_event(errdesc)  
if errdesc.sql\_errno == 2013 or – CR\_SERVER\_LOST  
errdesc.sql\_errno == 2055 or – CR\_SERVER\_LOST\_EXTENDED  
errdesc.sql\_errno == 2006 or – CR\_SERVER\_GONE\_ERROR  
errdesc.sql\_errno == 2011 – CR\_TCP\_CONNECTION  
then  
close\_statements()  
prepare\_statements()  
end  
end

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5ODg0NDEw_size_16_color_FFFFFF_t_70]: /images/20230208/016bf334a1f74efdae3606d913868db3.png
[akopytov_gmail.com]: mailto:akopytov@gmail.com