sysbench使用及自定义oltp测试lua脚本

一、sysbench介绍

sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。
目前sysbench代码托管在launchpad上，项目地址：https://launchpad.net/sysbench（原来的官网 http://sysbench.sourceforge.net 已经不可用），源码采用bazaar管理。
注：本文所有的测试都是基于linux操作系统和mysql数据库的。

二、sysbench安装

1、依赖包：

• autoconf
• automake
• cdbs
• debhelper (>= 9)
• docbook-xml
• docbook-xsl
• libmysqlclient15-dev
• libtool
• xsltproc

2、编译安装：

#./autogen.sh#./configure#make

注意事项：./configure命令，sysbench默认是支持MySQL的benchmarking的，如果不加任何选项则要求保证MySQL的安装路径都是默认的标准路径，headfile位于/usr/include目录下，libraries位于/usr/lib/目录下。因为我的MySQL是源码编译安装的，安装路径是放在/usr/local/mysql下，所以这里要添加相应的选项命令：
./configure --prefix=/usr/local/sysbench --with-mysql=/usr/local/mysql \
--with-mysql-includes=/usr/local/mysql/include/ \
--with-mysql-libs=/usr/local/mysql/lib/

这样就可以看到/usr/local/sysbench下有一个可执行文件sysbench，这就是sysbench的主程序。

3、功能简介

sysbench目前可以进行如下几个方面的性能测试：
- fileio - File I/O test #磁盘io性能
- cpu - CPU performance test #CUP性能
- memory - Memory functions speed test #内存性能
- threads - Threads subsystem performance test #POSIX线程性能
- mutex - Mutex performance test #调度程序性能
- oltp - OLTP test #数据库性能(OLTP基准测试)

注：在0.4版本的--test选项中是可以直接选用oltp模式，但是在0.4.12.1.1以后oltp测试就转换成调用lua脚本来进行测试了，脚本主要存放在tests/db目录下。这样用户就可以根据自己的系统定制lua脚本，这样的测试就能更精确的测试业务的性能。

三、开始进行测试

通用配置

接下来我们来分别看一下各个模式的相关参数、测试方法和结果分析。
sysbench的基本命令格式为：
sysbench –test=< test-name> [options]… < command>
主要分为三个部分：

1、–test=< test-name>

这部分是指定测试类型，基本类型有fileio，cpu，memory，threads，mutex，oltp(或者指定lua脚本)

2、[options]…

这部分包括测试需要的各种选项，有全局的也有每个测试模式自由的选项
（每个测试模式的选项可以用./sysbench –test=< test-name> help来获取）

3、< command>

控制命令，总共有五个
prepare #准备测试，主要是生成测试数据
run #执行测试，根据选项限制来执行测试
cleanup #清除准备阶段生成的测试数据
help #获取帮助文档
version #获取版本信息

几个重要的全局参数：

–num-threads=N number of threads to use [1] #测试时使用的线程数
–max-requests=N limit for total number of requests [10000] #测试过程最多执行多少次请求
–max-time=N limit for total execution time in seconds [0] #测试过程总共执行多长时间（和–max-requests效果同样，但是两个同时限定的时候谁优先还没有测试）
–report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0] #每隔多少秒输出测试概况（这个过程你可以观察到mysql redolog的切换情况）
–db-driver=STRING specifies database driver to use (‘help’ to get list of available drivers) #指定需求测试的数据库类型，默认是mysql
#mysql链接选项
–mysql-host=[LIST,…] MySQL server host [localhost] #mysql主机地址
–mysql-port=N MySQL server port [3306] #mysql端口
–mysql-socket=[LIST,…] MySQL socket #mysql socket文件位置，指定这个之后 其他的链接选项均可以不指定
–mysql-user=STRING MySQL user [sbtest] #用来测试的mysql用户名
–mysql-password=STRING MySQL password [] #密码
–mysql-db=STRING MySQL database name [sbtest] #测试数据库名 默认sbtest

接下来进入各个测试模式的测试方法

fileio-磁盘io性能

1、主要参数

[root@centostest sysbench]# ./sysbench --test=fileio helpsysbench 0.5:  multi-threaded system evaluation benchmarkfileio options:  --file-num=N                  number of files to create [128]  --file-block-size=N           block size to use in all IO operations [16384]  --file-total-size=SIZE        total size of files to create [2G]  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]  --file-extra-flags=STRING     additional flags to use on opening files {sync,dsync,direct} []  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]  --file-fsync-all=[on|off]     do fsync() after each write operation [off]  --file-fsync-end=[on|off]     do fsync() at the end of test [on]  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]No help available for test 'fileio'.

参数详解：
–file-num=N 代表生成测试文件的数量，默认为128。
–file-block-size=N 测试时所使用文件块的大小，如果想磁盘针对innodb存储引擎进行测试，可以将其设置为16384，即innodb存储引擎页的大小。默认为16384。
–file-total-size=SIZE 创建测试文件的总大小，默认为2G大小。
–file-test-mode=STRING 文件测试模式，包含：seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)。
–file-io-mode=STRING 文件操作的模式，sync（同步）,async（异步）,fastmmap（快速mmap）,slowmmap（慢速mmap），默认为sync同步模式。
–file-async-backlog=N 对应每个线程队列的异步操作数，默认为128。
–file-extra-flags=STRING 打开文件时的选项，这是与API相关的参数。
–file-fsync-freq=N 执行fsync()函数的频率。fsync主要是同步磁盘文件，因为可能有系统和磁盘缓冲的关系。 0代表不使用fsync函数。默认值为100。
–file-fsync-all=[on|off] 每执行完一次写操作，就执行一次fsync。默认为off。
–file-fsync-end=[on|off] 在测试结束时执行fsync函数。默认为on。
–file-fsync-mode=STRING文件同步函数的选择，同样是和API相关的参数，由于多个操作系统对于fdatasync支持不同，因此不建议使用fdatasync。默认为fsync。
–file-merged-requests=N 大多情况下，合并可能的IO的请求数，默认为0。
–file-rw-ratio=N 测试时的读写比例，默认时为1.5，即可3：2。

2、测试实例

测试总大小为5G的10个文件的随机读写性能：

1>先生成测试文件

sysbench --test=fileio --file-num=10 --file-total-size=5G prepare

2>开始测试

sysbench --test=fileio --file-total-size=5G --file-test-mode=rndrw --max-time=180 --max-requests=100000000 --num-threads=16 --init-rng=on --file-num=10 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 run

3>结果分析

sysbench 0.4.12:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 16Initializing random number generator from timer.Random number generator seed is 0 and will be ignoredExtra file open flags: 400010 files, 512Mb each5Gb total file sizeBlock size 16KbNumber of random requests for random IO: 100000000Read/Write ratio for combined random IO test: 1.50Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing random r/w testThreads started!Time limit exceeded, exiting...(last message repeated 15 times)Done.Operations performed:  89247 reads, 59488 writes, 0 Other = 148735 TotalRead 1.3618Gb  Written 929.5Mb  Total transferred 2.2695Gb  (12.888Mb/sec)  824.84 Requests/sec executedGeneral statistics:    total time:                          180.3188s    total number of events:              148735    total time taken by event execution: 2882.8395    response time:         min:                                  0.08ms         avg:                                 19.38ms         max:                                953.75ms         approx.  95 percentile:             158.81msThreads fairness:    events (avg/stddev):           9295.9375/371.20    execution time (avg/stddev):   180.1775/0.07

主要看这行输出的信息：

Read 1.3618Gb  Written 929.5Mb  Total transferred 2.2695Gb  (12.888Mb/sec)  824.84 Requests/sec executed

这行信息表示：在180秒时间里面总共完成随机读取1.3618G数据，写入929.5Mb数据，平均每秒随机读写的效率为12.888Mb/秒，IOPS为824.84 Requests/sec
因为是虚拟机，所有磁盘的表现还是比较差的。

4>清除测试数据

[root@centostest sysbench]# sysbench --test=fileio --file-num=10 --file-total-size=5G cleanupsysbench 0.4.12:  multi-threaded system evaluation benchmarkRemoving test files...

cpu-cpu性能测试

1、主要参数

[root@centostest sysbench]# sysbench --test=cpu helpsysbench 0.4.12:  multi-threaded system evaluation benchmarkcpu options:  --cpu-max-prime=N      upper limit for primes generator [10000]

参数详解：
–cpu-max-prime=N 用来选项指定最大的素数，具体参数可以根据CPU的性能来设置，默认为10000

2、测试实例

根据官网的介绍可知：CPU测试使用64位整数，测试计算素数直到某个最大值所需要的时间。

sysbench --test=cpu --cpu-max-prime=20000 run

输出如下：

[root@centostest sysbench]# sysbench --test=cpu --cpu-max-prime=20000 runsysbench 0.4.12:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredDoing CPU performance benchmarkPrimer numbers limit: 20000Threads started!Done.General statistics:    total time:                          28.9293s    total number of events:              10000    total time taken by event execution: 28.8916    response time:         min:                                  2.51ms         avg:                                  2.89ms         max:                                  7.46ms         approx.  95 percentile:               3.49msThreads fairness:    events (avg/stddev):           10000.0000/0.00    execution time (avg/stddev):   28.8916/0.00

我们只需要关心测试的总时间(total time)即可(越小越忧)。
CPU性能测试有一个需要注意的地方，上面的测试只使用了一个线程，如果在两个cpu processor不同的电脑上做比较，这是不公平的。公平的做法是指定合理的线程数，如下所示：

sysbench --test=cpu --num-threads=`grep "processor" /proc/cpuinfo | wc -l` --cpu-max-prime=20000 run

补充知识：
查看CPU核数的方法
查看物理cpu个数

grep "physical id" /proc/cpuinfo | sort -u | wc -l

查看核心数量

grep "core id" /proc/cpuinfo | sort -u | wc -l

查看线程数量

grep "processor" /proc/cpuinfo | sort -u | wc -l

在sysbench的测试中，–num-threads取值为”线程数量”即可，再大的值没有什么意义，对测试结果也没有什么影响。

memory-内存分配及传输速度

1、主要参数

[root@centostest sysbench]# ./sysbench --test=memory helpsysbench 0.5:  multi-threaded system evaluation benchmarkmemory options:  --memory-block-size=SIZE    size of memory block for test [1K]  --memory-total-size=SIZE    total size of data to transfer [100G]  --memory-scope=STRING       memory access scope {global,local} [global]  --memory-hugetlb=[on|off]   allocate memory from HugeTLB pool [off]  --memory-oper=STRING        type of memory operations {read, write, none} [write]  --memory-access-mode=STRING memory access mode {seq,rnd} [seq]No help available for test 'memory'.

参数详解：
–memory-block-size=SIZE 测试内存块的大小，默认为1K
–memory-total-size=SIZE 数据传输的总大小，默认为100G
–memory-scope=STRING 内存访问的范围，包括全局和本地范围，默认为global
–memory-hugetlb=[on|off] 是否从HugeTLB池分配内存的开关，默认为off
–memory-oper=STRING 内存操作的类型，包括read, write, none，默认为write
–memory-access-mode=STRING 内存访问模式，包括seq,rnd两种模式，默认为seq

2、测试实例

内存测试测试了内存的连续读写性能。

./sysbench --test=memory --memory-block-size=8K --memory-total-size=1G  --num-threads=16 run

3、结果分析

输出结果如下：

[root@centostest sysbench]# ./sysbench --test=memory --memory-block-size=8K --memory-total-size=1G  --num-threads=16 runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 16Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started!Operations performed: 131072 (626689.49 ops/sec)1024.00 MB transferred (4896.01 MB/sec)General statistics:    total time:                          0.2091s    total number of events:              131072    total time taken by event execution: 2.3239s    response time:         min:                                  0.00ms         avg:                                  0.02ms         max:                                 50.38ms         approx.  95 percentile:               0.00msThreads fairness:    events (avg/stddev):           8192.0000/937.69    execution time (avg/stddev):   0.1452/0.03

threads-POSIX线程性能

1、主要参数

[root@centostest sysbench]# ./sysbench --test=threads helpsysbench 0.5:  multi-threaded system evaluation benchmarkthreads options:  --thread-yields=N      number of yields to do per request [1000]  --thread-locks=N       number of locks per thread [8]No help available for test 'threads'.

参数详解：
–thread-yields=N 指定每个请求的压力，默认为1000
–thread-locks=N 指定每个线程的锁数量，默认为8

2、测试实例

测试线程调度器的性能。对于高负载情况下测试线程调度器的行为非常有用。

sysbench --test=threads --num-threads=64 run

3、结果分析

下面是输出结果：

[root@centostest sysbench]# sysbench --test=threads --num-threads=64 runsysbench 0.4.12:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 64Random number generator seed is 0 and will be ignoredDoing thread subsystem performance testThread yields per test: 1000 Locks used: 8Threads started!Done.General statistics:    total time:                          9.4415s    total number of events:              10000    total time taken by event execution: 602.1637    response time:         min:                                  0.35ms         avg:                                 60.22ms         max:                                479.03ms         approx.  95 percentile:             126.27msThreads fairness:    events (avg/stddev):           156.2500/4.82    execution time (avg/stddev):   9.4088/0.02

关注的性能指标也是total time越小越忧。

mutex-调度程序性能

1、主要参数

[root@centostest sysbench]# ./sysbench --test=mutex helpsysbench 0.5:  multi-threaded system evaluation benchmarkmutex options:  --mutex-num=N        total size of mutex array [4096]  --mutex-locks=N      number of mutex locks to do per thread [50000]  --mutex-loops=N      number of empty loops to do inside mutex lock [10000]No help available for test 'mutex'.

参数详解：
–mutex-num=N 数组互斥的总大小。默认是4096
–mutex-locks=N 每个线程互斥锁的数量。默认是50000
–mutex-loops=N 内部互斥锁的空循环数量。默认是10000

2、测试实例

测试互斥锁的性能，方式是模拟所有线程在同一时刻并发运行，并都短暂请求互斥锁。

./sysbench --test=mutex --num-threads=16 --mutex-num=2048 --mutex-locks=1000000 --mutex-loops=5000 run

3、结果分析

结果输出如下：

[root@centostest sysbench]# ./sysbench --test=mutex --num-threads=16 --mutex-num=2048 --mutex-locks=1000000 --mutex-loops=5000 runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 16Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started!General statistics:    total time:                          0.8856s    total number of events:              16    total time taken by event execution: 13.7792s    response time:         min:                                832.79ms         avg:                                861.20ms         max:                                880.81ms         approx.  95 percentile:             878.94msThreads fairness:    events (avg/stddev):           1.0000/0.00    execution time (avg/stddev):   0.8612/0.01

oltp-数据库性能测试

1、主要参数

由于sysbench0.5中已经不存在oltp测试模式了，所以使用命令./sysbench --test=oltp help无法获取好帮助信息，还好0.4和0.5的参数都是兼容的，所以我们这里用0.4的帮助信息来说明：

[root@centostest sysbench]# ./sysbench --help=oltp helpUsage:  sysbench [general-options]... --test=<test-name> [test-options]... commandGeneral options:  --num-threads=N             number of threads to use [1]  --max-requests=N            limit for total number of requests [10000]  --max-time=N                limit for total execution time in seconds [0]  --forced-shutdown=STRING    amount of time to wait after --max-time before forcing shutdown [off]  --thread-stack-size=SIZE    size of stack per thread [32K]  --init-rng=[on|off]         initialize random number generator [off]  --seed-rng=N                seed for random number generator, ignored when 0 [0]  --tx-rate=N                 target transaction rate (tps) [0]  --tx-jitter=N               target transaction variation, in microseconds [0]  --report-interval=N         periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]  --report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []  --test=STRING               test to run  --debug=[on|off]            print more debugging info [off]  --validate=[on|off]         perform validation checks where possible [off]  --help=[on|off]             print help and exit  --version=[on|off]          print version and exitLog options:  --verbosity=N      verbosity level {5 - debug, 0 - only critical messages} [4]  --percentile=N      percentile rank of query response times to count [95]Compiled-in tests:  fileio - File I/O test  cpu - CPU performance test  memory - Memory functions speed test  threads - Threads subsystem performance test  mutex - Mutex performance test  oltp - OLTP testCommands: prepare run cleanup help versionSee 'sysbench --test=<name> help' for a list of options for each test.

参数详解：
–oltp-test-mode=STRING 执行模式{simple,complex(advanced transactional),nontrx(non-transactional),sp}。默认是complex
–oltp-reconnect-mode=STRING 重新连接模式{session(不使用重新连接。每个线程断开只在测试结束),transaction(在每次事务结束后重新连接),query(在每个SQL语句执行完重新连接),random(对于每个事务随机选择以上重新连接模式)}。默认是session
–oltp-sp-name=STRING 存储过程的名称。默认为空
–oltp-read-only=[on|off] 只读模式。Update，delete，insert语句不可执行。默认是off
–oltp-skip-trx=[on|off] 省略begin/commit语句。默认是off
–oltp-range-size=N 查询范围。默认是100
–oltp-point-selects=N number of point selects [10]
–oltp-simple-ranges=N number of simple ranges [1]
–oltp-sum-ranges=N number of sum ranges [1]
–oltp-order-ranges=N number of ordered ranges [1]
–oltp-distinct-ranges=N number of distinct ranges [1]
–oltp-index-updates=N number of index update [1]
–oltp-non-index-updates=N number of non-index updates [1]
–oltp-nontrx-mode=STRING 查询类型对于非事务执行模式{select, update_key, update_nokey, insert, delete} [select]
–oltp-auto-inc=[on|off] AUTO_INCREMENT是否开启。默认是on
–oltp-connect-delay=N 在多少微秒后连接数据库。默认是10000
–oltp-user-delay-min=N 每个请求最短等待时间。单位是ms。默认是0
–oltp-user-delay-max=N 每个请求最长等待时间。单位是ms。默认是0
–oltp-table-name=STRING 测试时使用到的表名。默认是sbtest
–oltp-table-size=N 测试表的记录数。默认是10000
–oltp-dist-type=STRING 分布的随机数{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是special
–oltp-dist-iter=N 产生数的迭代次数。默认是12
–oltp-dist-pct=N 值的百分比被视为’special’ (for special distribution)。默认是1
–oltp-dist-res=N ‘special’的百分比值。默认是75

2、测试实例

1>生成测试数据，1个表，每个表100000数据

[root@centostest sysbench]# ./sysbench --mysql-host=127.0.0.1 --mysql-port=3166 --mysql-user=root --mysql-password=123456  --test=tests/db/oltp.lua --oltp_tables_count=1 --oltp-table-size=1000000 --rand-init=on preparesysbench 0.5:  multi-threaded system evaluation benchmarkCreating table 'sbtest1'...Inserting 1000000 records into 'sbtest1'……………………

2>开始测试

使用128线程(–num-threads=128)测试60秒(–max-time=60)每10秒输出一次测试信息(–report-interval=10)

./sysbench --mysql-host=127.0.0.1 --mysql-port=3166 --mysql-user=root --mysql-password=123456 --test=tests/db/oltp.lua --oltp_tables_count=1 --oltp-table-size=1000000 --num-threads=128 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=60 --max-requests=0 run

3>结果分析

测试结果输出如下：

[root@centostest sysbench]# ./sysbench --mysql-host=127.0.0.1 --mysql-port=3166 --mysql-user=root --mysql-password=123456 --test=tests/db/oltp.lua --oltp_tables_count=1 --oltp-table-size=1000000 --num-threads=128 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=60 --max-requests=0 runsysbench 0.5:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 128Report intermediate results every 10 second(s)Random number generator seed is 0 and will be ignoredInitializing worker threads...Threads started![  10s] threads: 128, tps: 63.17, reads: 957.71, writes: 253.70, response time: 6659.14ms (95%), errors: 0.00, reconnects:  0.00[  20s] threads: 128, tps: 38.10, reads: 615.56, writes: 169.59, response time: 4702.80ms (95%), errors: 0.00, reconnects:  0.00[  30s] threads: 128, tps: 43.10, reads: 627.16, writes: 154.61, response time: 7544.47ms (95%), errors: 0.00, reconnects:  0.00[  40s] threads: 128, tps: 27.70, reads: 364.70, writes: 119.80, response time: 7278.28ms (95%), errors: 0.00, reconnects:  0.00[  50s] threads: 128, tps: 36.10, reads: 399.28, writes: 135.69, response time: 8400.39ms (95%), errors: 0.00, reconnects:  0.00[  60s] threads: 128, tps: 21.80, reads: 434.42, writes: 90.30, response time: 11977.08ms (95%), errors: 0.00, reconnects:  0.00OLTP test statistics:    queries performed:        read:                            33992        write:                           9712        other:                           4856        total:                           48560    transactions:                        2428   (39.27 per sec.)    read/write requests:                 43704  (706.81 per sec.)    other operations:                    4856   (78.53 per sec.)    ignored errors:                      0      (0.00 per sec.)    reconnects:                          0      (0.00 per sec.)General statistics:    total time:                          61.8331s    total number of events:              2428    total time taken by event execution: 7830.4825s    response time:         min:                                210.50ms         avg:                               3225.08ms         max:                              11983.87ms         approx.  95 percentile:            8024.33msThreads fairness:    events (avg/stddev):           18.9688/2.14    execution time (avg/stddev):   61.1756/0.40

主要关注如下几个值：

    transactions:                        2428   (39.27 per sec.)    read/write requests:                 43704  (706.81 per sec.)    other operations:                    4856   (78.53 per sec.)

四、oltp-数据库性能测试中lua脚本分析

在sysbench0.4的后期版本中sysbench已经取消了test中的oltp模式，换而代之的是oltp的lua脚本。这一改变大大的提升了sysbench的灵活性。用户可以结合业务来定制lua脚本，这样能更精确的测试出适用于此业务的数据库性能指标。

这次我们使用sysbench-0.4.12-1.1来看看默认的lua脚本做了哪些工作，以及我们怎么来定制lua脚本。

oltp的测试脚本默认存放在tests/db下，这个目录下有很多脚本，在oltp基准测试中我们用到比较多的是common.lua和oltp.lua

[root@ol5-112 db]# pwd/root/sysbench-0.4.12-1.1/sysbench/tests/db[root@ol5-112 db]# lltotal 72-rw-r--r-- 1 root root  3585 Jul 28 09:32 common.lua-rw-r--r-- 1 root root   340 Jul 28 09:32 delete.lua-rw-r--r-- 1 root root   830 Jul 28 09:32 insert.lua-rw-r--r-- 1 root root 12088 Jul 28 09:32 Makefile-rw-r--r-- 1 root root  1020 Jul 28 09:32 Makefile.am-rw-r--r-- 1 root root 11569 Jul 28 09:32 Makefile.in-rw-r--r-- 1 root root  2925 Jul 28 09:32 oltp.lua-rw-r--r-- 1 root root   342 Jul 28 09:32 oltp_simple.lua-rw-r--r-- 1 root root   425 Jul 28 09:32 parallel_prepare.lua-rw-r--r-- 1 root root   343 Jul 28 09:32 select.lua-rw-r--r-- 1 root root  3964 Jul 28 09:32 select_random_points.lua-rw-r--r-- 1 root root  4066 Jul 28 09:32 select_random_ranges.lua-rw-r--r-- 1 root root   343 Jul 28 09:32 update_index.lua-rw-r--r-- 1 root root   552 Jul 28 09:32 update_non_index.lua

首先来看common.lua

-- Input parameters-- oltp-tables-count - number of tables to create-- oltp-secondary - use secondary key instead PRIMARY key for id column------ 创建表，插入测试数据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   print("Creating table 'sbtest" .. i .. "'...")   if (db_driver == "mysql") 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 .. " */"   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-- prepare阶段执行的操作function prepare()   local query   local i   local j   set_vars()   db_connect()   for i = 1,oltp_tables_count do     create_insert(i)   end   return 0end-- cleanup阶段执行的操作function cleanup()   local i   set_vars()   for i = 1,oltp_tables_count do   print("Dropping table 'sbtest" .. i .. "'...")   db_query("DROP TABLE sbtest".. i )   endend-- 初始化默认值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   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   if (oltp_auto_inc == 'off') then      oltp_auto_inc = false   else      oltp_auto_inc = true   end   if (oltp_read_only == 'on') then      oltp_read_only = true   else      oltp_read_only = false   end   if (oltp_skip_trx == 'on') then      oltp_skip_trx = true   else      oltp_skip_trx = false   endend

这个脚本主要功能是用来准备测试的表、测试数据、初始化测试中需要的一些默认值、清楚数据。function prepare()会在全局command为perpare时调用，function cleanup()会在全局command为cleanup时调用，而function set_vars()会被引用至自己本身以及其他lua脚本中。

接下来我们来看oltp.lua脚本。

pathtest = string.match(test, "(.*/)") or ""-- 引入common.lua脚本dofile(pathtest .. "common.lua")-- 申明在满足db_driver == "mysql" and mysql_table_engine == "myisam"条件时测试使用锁表开头，解除锁表结尾。其他全部使用BEGIN开始，commit结束（事务）function thread_init(thread_id)   set_vars()   if (db_driver == "mysql" 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)   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)   -- 如果指定跳过事务参数为ON 则不调用事务开始标示   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   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   -- 如果oltp_read_only=on 则跳过DML语句   if not oltp_read_only then   -- 开始执行DML语句   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   -- 如果指定跳过事务参数为ON，测不执行commit   if not oltp_skip_trx then      db_query(commit_query)   endend

这个脚本就是默认的oltp测试时使用的测试脚本。结合common.lua中初始化的参数，此脚本每个事务中执行了10次基于主键的简单查询，1次范围查询，一次求和计算，一次排序查询，一次去重加排序查询，一次小字段更新，一次长字段更新，一次插入。而且在这个脚本中根据oltp_skip_trx oltp_read_only 这两个参数的限制不同需要执行的语句块也不同。

五、自定义lua脚本进行oltp性能测试

那么了解了这两个脚本之后我们就可以根据业务来定制自己的lua脚本了。
场景：在一个业务中有几个简单的主键查询，一个转账扣款的事务，一个插入语句语句分别是：

select id from test1 where id=:vid;select id from test2 where id=:vid;select id from test3 where id=:vid;start TRANSACTIONupdate test4 set k=k-1 where id=:vid;update test5 set k=k+1 where id=:vid;commit;insert into test6 (k,xv) values (:vk,:vxv);

那么我们的脚本就可以这么定制：

pathtest = string.match(test, "(.*/)") or ""dofile(pathtest .. "common.lua")function thread_init(thread_id)   set_vars()   if (db_driver == "mysql" 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 vid1   local vid2   local vid3   local vid4   local vid5   local vk   local vxv   vid1 = sb_rand_uniform(1,10000)   vid2 = sb_rand_uniform(1,10000)   vid3 = sb_rand_uniform(1,10000)   vid4 = sb_rand_uniform(1,10000)   vid5 = sb_rand_uniform(1,10000)   vk = sb_rand_uniform(10,10000)   vxv = sb_rand_str([[###########-###########-###########-###########-###########]])   rs = db_query("SELECT pad FROM test1 WHERE id=" .. vid1)   rs = db_query("SELECT pad FROM test2 WHERE id=" .. vid2)   rs = db_query("SELECT pad FROM test3 WHERE id=" .. vid3)   db_query(begin_query)   rs = db_query("update test4 set k=k-1 where id=" .. vid4)   rs = db_query("update test5 set k=k+1 where id=" .. vid5)   db_query(commit_query)   rs = db_query("insert into test6 (k,xv) values " .. string.format("(%d , '%s')",vk,vxv));end

我们另存为mytest.lua
然后我们就可以使用如下命令来进行测试了：

./sysbench --mysql-host=127.0.0.1 --mysql-port=3166 --mysql-user=root --mysql-password=123456 --test=tests/db/mytest.lua --num-threads=128 --report-interval=10 --rand-type=uniform --max-time=600 --max-requests=0 run

思考：
现在多机房热备的构架已经比较普片了，那么跨机房部署还有一个比较影响性能的因素就是网络吞吐量，sysbench中是否可以加入网络吞吐量的测试呢？