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

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

安装sysbench

yum install m4  autoconf  automake libtool    #安装依赖包
解压进入路径
./autogen.sh
./configure
make    
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 prepare

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 run

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 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]# ll
total 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_IN