MySQL 存储过程性能测试(Sysbench自定义lua脚本)

入职新公司，领导给的第一个任务就是说：我们打算在业务里面上存储过程，但是据说存储过程性能不好，所以你来出一个测试方案测试一下，额，刚拿到需求的时候懵懵哒，第一，我还真不知道存储过程性能不好呢，理论上来说存储过程会减少服务器与应用程序之间的数据传输，如果数据传输量比较大的话，用存储过程应该会好一些，然后跟老师请教一下，说是MySQL的存储过程的实现确实不如oracle做的好，我以前是用PG写存储过程的，觉得还不错。然后在经过两天胡乱摸索之后，又跟老师请教，然后大致定了一下方案，就是sysbench0.5可以自定义lua脚本来测试了嘛，所以先做一遍基准测试，然后自定义lua脚本来测试，大致是这么个思路。

安装sysbench

yum install m4  autoconf  automake libtool    #安装依赖包解压进入路径./autogen.sh./configuremake    make install

sysbench的测试有很多项：

• cpu测试
• 线程测试
• mutex测试
• 磁盘IO性能测试
• 内存测试
• OLTP测试
  我们这里重点看一下OLTP测试

sysbench的OLTP测试

sysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-user=root --mysql-password=root --mysql-host=localhost --mysql-db=sbtest --mysql-socket=/data/mysqldata/3306/mysql.sock preparesysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-user=root --mysql-password=root --mysql-host=localhost --mysql-db=sbtest --mysql-socket=/data/mysqldata/3306/mysql.sock runsysbench --test=./tests/db/oltp.lua --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-user=root --mysql-password=root --mysql-host=localhost --mysql-db=sbtest --mysql-socket=/data/mysqldata/3306/mysql.sock cleanup

几个重要参数解读

- -test
sysbench-0.4是使用–oltp-test-mode用以指定测试模式，取值有(simeple,complex,nontrx)，默认是complex。

• simple只执行简单的查询，0.5版本中就是–test=./tests/db/oltp_simple.lua
• complex是事务型查询，每个线程都在数据表上执行事务。0.5版本中就是–test=./tests/db/oltp.lua
• nontrx是非事务模式，它执行insert或update的语句。0.5版本中有insert.lua等

- -oltp-sp-name=STRING 存储过程的名称。默认为空
- -oltp-read-only=[on|off] 只读模式。Update，delete，insert语句不可执行。默认是off
- -oltp-skip-trx=[on|off] 省略begin/commit语句。默认是off
- -oltp-table-name=STRING 测试时使用到的表名。默认是sbtest
- -oltp-table-size=N 测试表的记录数。默认是10000

我们来看看lua脚本及lua脚本做了什么。

[root@localhost db]# lltotal 84-rw-r--r--. 1 root root  1001 May 24  2016 bulk_insert.lua-rw-r--r--. 1 root root  3652 May 24  2016 common.lua-rw-r--r--. 1 root root   340 May 24  2016 delete.lua-rw-r--r--. 1 root root  1154 Dec  5 10:08 insert.lua-rw-r--r--. 1 root root 12293 Dec  2 10:05 Makefile-rw-r--r--. 1 root root  1085 May 24  2016 Makefile.am-rw-r--r--. 1 root root 11782 Dec  2 10:04 Makefile.in-rw-r--r--. 1 root root  2959 May 24  2016 oltp.lua-rw-r--r--. 1 root root   342 May 24  2016 oltp_simple.lua-rw-r--r--. 1 root root   501 May 24  2016 parallel_prepare.lua-rw-r--r--. 1 root root   343 May 24  2016 select.lua-rw-r--r--. 1 root root  3996 May 24  2016 select_random_points.lua-rw-r--r--. 1 root root  4098 May 24  2016 select_random_ranges.lua-rw-r--r--. 1 root root   343 May 24  2016 update_index.lua-rw-r--r--. 1 root root   552 May 24  2016 update_non_index.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(为id列使用辅助索引而非主键索引)----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   i = table_id   --创建sbtest1，sbtest2，sbtest3...这样的表，oltp-tables-count决定要创建的表的数目   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 SERIAL 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)   db_query("CREATE INDEX k_" .. i .. " on sbtest" .. i .. "(k)")   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()end--准备测试数据库function prepare()   local query   local i   local j   set_vars()   --连接数据库   db_connect()   --依次创建sbtest1，sbtest2，sbtest3..并插入数据   for i = 1,oltp_tables_count do     create_insert(i)   end   return 0end--清理测试数据库function cleanup()   local i   set_vars()   --for循环依次删除sbtest1，sbtest2，sbtest3...   for i = 1,oltp_tables_count do   print("Dropping table 'sbtest" .. i .. "'...")   db_query("DROP TABLE sbtest".. i )   endend--设置变量，oltp可用参数都在这里啦！！！function set_vars()   --这些参数如果设置了就会使用我们设置的值，否则的话取用后面的默认值   oltp_table_size = oltp_table_size or 10000   oltp_range_size = oltp_range_size or 100   oltp_tables_count = oltp_tables_count or 1   --看到点的查询10个，范围查询1个，取总为1个，排序1个，distinct一个，索引更新一个，   --所以我们自定义lua脚本时，根据我们的业务比例来确定sql的执行次数，for循环里面循环比较少的次数，不然在--max-time之前执行不完   oltp_point_selects = oltp_point_selects or 10   oltp_simple_ranges = oltp_simple_ranges or 1   oltp_sum_ranges = oltp_sum_ranges or 1   oltp_order_ranges = oltp_order_ranges or 1   oltp_distinct_ranges = oltp_distinct_ranges or 1   oltp_index_updates = oltp_index_updates or 1   oltp_non_index_updates = oltp_non_index_updates or 1   --oltp_auto_inc：创建表是否有自增主键，我们自定义lua的话这个参数应该不要干扰我们的   if (oltp_auto_inc == 'off') then      oltp_auto_inc = false   else      oltp_auto_inc = true   end   --oltp_read_only:是否开启只读模式，如果是的话，就不测试update那些语句了   if (oltp_read_only == 'on') then      oltp_read_only = true   else      oltp_read_only = false   end   --oltp_skip_trx:是否跳过事务，如果不开启事务的话，应该是单条语句的执行，开启事务的话，就是所有的SQL放在一个事务中进行。   if (oltp_skip_trx == 'on') then      oltp_skip_trx = true   else      oltp_skip_trx = false   endend

插入的表数据如下：

root@localhost 15:37:01 sbtest> select * from sbtest1 limit 10;+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+| id | k     | c                                                                                                                       | pad                                                         |+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+|  1 | 50385 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 ||  2 | 50188 | 95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805 | 09512147864-77936258834-40901700703-13541171421-15205431759 ||  3 | 50193 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-17788128188-73465768032-24619558652 | 21979564480-87492594656-60524686334-78820761788-57684966682 ||  4 | 49641 | 72200234338-75748118569-08939863650-01688814841-36798767826-71560494483-89421079440-11810718401-29133837777-68338627243 | 80945118876-33522718290-51523426562-15979384524-91541356971 ||  5 | 49853 | 23749555118-82927985580-59934820346-38519110422-33958726372-68179434013-57381755780-85457880176-06440411187-75543443997 | 63721288074-65922289726-32275902397-42202048996-09343452123 ||  6 | 50424 | 77981152534-02426776427-14243102257-77939982840-33466068594-21561488340-10177591229-61783484727-02449865640-36716253914 | 82126617622-12647210221-34431310803-28592981065-36359546123 ||  7 | 46713 | 99754685588-47576951480-32708622771-83861221370-03797298475-60503371617-50159644690-11488793570-28225419667-59109289014 | 19994000982-50192009482-63005057222-82729312489-25570376232 ||  8 | 60994 | 88658076981-28257193684-53183042641-08566229847-92845627546-46433913626-82618684116-59416871281-45638910500-66696979407 | 16007104994-21315228328-96956154568-55286875724-10424521342 ||  9 | 50166 | 30259457399-49455699717-43210898264-46300466148-34254750860-44098710066-38295952016-90196077385-22332519290-06484158548 | 40929980986-33813039690-13155419391-97985458477-39771362212 || 10 | 49830 | 48090103407-09222928184-34050945574-85418069333-36966673537-23363106719-15284068881-04674238815-26203696337-24037044694 | 01495266405-82925129145-92643983850-90243995398-18709399387 |+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+10 rows in set (0.00 sec)

2.oltp_simple.lua

pathtest = string.match(test, "(.*/)") or ""dofile(pathtest .. "common.lua")function thread_init(thread_id)   set_vars()endfunction event(thread_id)   local table_name   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)   --简单的select语句，只执行(select c from sbtest1 where id=1780)这种简单语句   rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))end

3.oltp.lua

pathtest = string.match(test, "(.*/)") or ""dofile(pathtest .. "common.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"   endendfunction event(thread_id)   local rs   local i   local table_name   local range_start   local c_val   local pad_val   local query   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)   --如果没有开启跳过事务，那么就把所有的sql放在一个事务里面   if not oltp_skip_trx then      db_query(begin_query)   end   --点查询语句   for i=1, oltp_point_selects do      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))   end   --范围查询between...and   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   --distinct去重   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   --如果不是简单的只读，那么我们还要测试几个update操作   if not oltp_read_only then   --update操作，简单的update   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   --update含有索引的列，除了更新表的数据，还会造成索引的更新   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)   --delete操作   rs = db_query("DELETE FROM " .. table_name .. " WHERE id=" .. i)   c_val = sb_rand_str([[###########-###########-###########-###########-###########-###########-###########-###########-###########-###########]])   pad_val = sb_rand_str([[###########-###########-###########-###########-###########]])   --insert操作   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)   endend

自定义lua脚本的结构

pathtest = string.match(test, "(.*/)") or ""--dofile是lua中的函数，加载并运行脚本，我们需要使用common.lua中的set_vars()函数dofile(pathtest .. "common.lua")function thread_init(thread_id)   set_vars()   --其他一些参数的处理end--event(thread_id)   可以把sql逻辑写到这里，   --num-threads多少个，就会同时调用多少个，然后进行压测就okfunction event(thread_id)    --主要的测试oltp语句在这里end

自定义lua脚本的测试过程

1. 首先收集实际业务的访问数据库的sql；
2. 准备好一个备份库；
3. 将收集的sql写在lua脚本里；

4. 通过sysbench的–test参数和–mysql-db参数进行测试（因为已有测试目标数据库，就不需要prepare了）

   因为我们只是先出一个方案，所以数据库先使用用tpcc的数据库做示例，
   假设常用的sql如下：
   select first from customer where c_id = ?;
   select * from orders where c_id=?;
   语句使用比例2:5，
   那么测试脚本如下：

pathtest = string.match(test, "(.*/)") or ""dofile(pathtest .. "common.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"   endend--function event(thread_id)   --主要的测试oltp语句在这里   local rs   local c_id   c_id = sb_rand(1,300000)   if not oltp_skip_trx then      db_query(begin_query)   end   for i=1, 2 do       rs = db_query("SELECT c_first FROM customer WHERE c_id=" .. c_id)   end   for i=1, 5 do       rs = db_query("select * from orders where o_c_id=" .. c_id)   end   if not oltp_skip_trx then      db_query(commit_query)   endend

运行测试输出结果：

[root@localhost db]# sysbench --test=mytest.lua --mysql-table-engine=innodb --num-threads=8 --max-time=60 --report-interval=10 --oltp_table_size=1000 --oltp_skip_trx=off --mysql-user=root --mysql-password=root123.. --mysql-host=localhost --mysql-db=tpcc --mysql-socket=/data/mysqldata/3306/mysql.sock runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 8Report intermediate results every 10 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![  10s] threads: 8, tps: 0.80, reads: 10.36, writes: 0.00, response time: 5121.62ms (95%), errors: 0.00, reconnects:  0.00[  20s] threads: 8, tps: 1.60, reads: 11.20, writes: 0.00, response time: 5222.24ms (95%), errors: 0.00, reconnects:  0.00[  30s] threads: 8, tps: 1.60, reads: 11.00, writes: 0.00, response time: 5198.85ms (95%), errors: 0.00, reconnects:  0.00[  40s] threads: 8, tps: 1.60, reads: 10.90, writes: 0.00, response time: 5138.51ms (95%), errors: 0.00, reconnects:  0.00[  50s] threads: 8, tps: 1.60, reads: 11.20, writes: 0.00, response time: 5124.68ms (95%), errors: 0.00, reconnects:  0.00[  60s] threads: 8, tps: 1.60, reads: 11.00, writes: 0.00, response time: 5166.27ms (95%), errors: 0.00, reconnects:  0.00OLTP test statistics:    queries performed:        read:                            672        write:                           0        other:                           192        total:                           864    transactions:                        96     (1.57 per sec.)    read/write requests:                 672    (10.99 per sec.)    other operations:                    192    (3.14 per sec.)    ignored errors:                      0      (0.00 per sec.)    reconnects:                          0      (0.00 per sec.)General statistics:    total time:                          61.1549s    total number of events:              96    total time taken by event execution: 487.9106s    response time:         min:                               4733.24ms         avg:                               5082.40ms         max:                               5274.05ms         approx.  95 percentile:            5198.85msThreads fairness:    events (avg/stddev):           12.0000/0.00    execution time (avg/stddev):   60.9888/0.16

测试存储过程

编写一个简单的存储过程：

CREATE TABLE assist_nums(id INT(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY);DELIMITER //CREATE FUNCTION get_sequence(itype CHAR(1)) RETURNS VARCHAR(20)DETERMINISTICBEGIN    DECLARE turnover_no INT(8) UNSIGNED ZEROFILL;    DECLARE return_turnover VARCHAR(20);    INSERT INTO assist_nums VALUE ();    SELECT last_insert_id() INTO turnover_no FROM assist_nums;          SET return_turnover=CONCAT(itype,DATE_FORMAT(NOW(),'%Y%m%d'),turnover_no);    RETURN return_turnover; END // DELIMITER ;

这个存储过程只是合并了两个sql而已，分开执行的话，就是执行两个sql：
INSERT INTO assist_nums VALUE ();
select CONCAT(itype,DATE_FORMAT(NOW(),’%Y%m%d’),lpad(last_insert_id(),8,’0’))

使用sql的lua测试
lua脚本

pathtest = string.match(test, "(.*/)") or ""dofile(pathtest .. "common.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"   endend--function event(thread_id)   --主要的测试oltp语句在这里   local rs   local c_id   c_id = sb_rand(1,300000)   if not oltp_skip_trx then      db_query(begin_query)   end      rs = db_query(" INSERT INTO assist_nums VALUE ()");      rs = db_query(" select CONCAT('R',DATE_FORMAT(NOW(),'%Y%m%d'),lpad(last_insert_id(),8,'0'))");   if not oltp_skip_trx then      db_query(commit_query)   endend

测试结果

[root@localhost db]# sysbench --test=mytest_sql.lua --mysql-table-engine=innodb --num-threads=8 --max-time=60 --report-interval=10 --oltp_skip_trx=off --mysql-user=root --mysql-password=root123.. --mysql-host=localhost --mysql-db=tpcc --mysql-socket=/data/mysqldata/3306/mysql.sock runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 8Report intermediate results every 10 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started!OLTP test statistics:    queries performed:        read:                            10000        write:                           10000        other:                           20000        total:                           40000    transactions:                        10000  (1676.19 per sec.)    read/write requests:                 20000  (3352.38 per sec.)    other operations:                    20000  (3352.38 per sec.)    ignored errors:                      0      (0.00 per sec.)    reconnects:                          0      (0.00 per sec.)General statistics:    total time:                          5.9659s    total number of events:              10000    total time taken by event execution: 47.7054s    response time:         min:                                  1.52ms         avg:                                  4.77ms         max:                                265.22ms         approx.  95 percentile:               9.50msThreads fairness:    events (avg/stddev):           1250.0000/10.20    execution time (avg/stddev):   5.9632/0.00

使用存储过程的lua脚本：

pathtest = string.match(test, "(.*/)") or ""dofile(pathtest .. "common.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"   endend--function event(thread_id)   --主要的测试oltp语句在这里   local rs   local c_id   c_id = sb_rand(1,300000)   if not oltp_skip_trx then      db_query(begin_query)   end   rs = db_query("SELECT get_sequence('R')")   if not oltp_skip_trx then      db_query(commit_query)   endend

测试结果

[root@localhost db]# sysbench --test=mytest_func.lua --mysql-table-engine=innodb --num-threads=8 --max-time=60 --report-interval=10 --oltp_skip_trx=on --mysql-user=root --mysql-password=root123.. --mysql-host=localhost --mysql-db=tpcc --mysql-socket=/data/mysqldata/3306/mysql.sock runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 8Report intermediate results every 10 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started!OLTP test statistics:    queries performed:        read:                            10000        write:                           0        other:                           0        total:                           10000    transactions:                        0      (0.00 per sec.)    read/write requests:                 10000  (2095.49 per sec.)    other operations:                    0      (0.00 per sec.)    ignored errors:                      0      (0.00 per sec.)    reconnects:                          0      (0.00 per sec.)General statistics:    total time:                          4.7721s    total number of events:              10000    total time taken by event execution: 38.1182s    response time:         min:                                  0.88ms         avg:                                  3.81ms         max:                                 88.28ms         approx.  95 percentile:               7.12msThreads fairness:    events (avg/stddev):           1250.0000/6.36    execution time (avg/stddev):   4.7648/0.00

这个存储过程比较简单了，下一步就是逐渐加大–num-threads来测试。

最后再说一下方案：
1. 首先手机实际业务的访问数据库的sql；
2. 准备好一个备份库；
3. 将收集的sql写在lua脚本里；
4. 通过sysbench的–test参数和–mysql-db参数进行测试（因为已有测试目标数据库，就不需要prepare了）

参考：sysbench使用
自定义lua脚本的测试
sysbench oltp测试