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

最新推荐文章于 2024-10-01 23:40:25 发布

BOBO_Free

最新推荐文章于 2024-10-01 23:40:25 发布

阅读量1.8w

点赞数 10

分类专栏： Mysql基础文章标签： sysbench lua mysql测试平民软件

本文链接：https://blog.csdn.net/hubo890224/article/details/52076347

版权

Mysql基础专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一、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 help
sysbench 0.5:  multi-threaded system evaluation benchmark

fileio 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 benchmark

Running the test with following options:
Number of threads: 16
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Extra file open flags: 4000
10 files, 512Mb each
5Gb total file size
Block size 16Kb
Number of random requests for random IO: 100000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

Operations performed:  89247 reads, 59488 writes, 0 Other = 148735 Total
Read 1.3618Gb  Written 929.5Mb  Total transferred 2.2695Gb  (12.888Mb/sec)
  824.84 Requests/sec executed

General 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.81ms

Threads 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 cleanup
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Removing test files...

cpu-cpu性能测试

1、主要参数

[root@centostest sysbench]# sysbench --test=cpu help
sysbench 0.4.12:  multi-threaded system evaluation benchmark

cpu 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 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored


Doing CPU performance benchmark

Primer numbers limit: 20000

Threads 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.49ms

Threads 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 help
sysbench 0.5:  multi-threaded system evaluation benchmark

memory 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 run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored


Initializing 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.00ms

Threads 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 help
sysbench 0.5:  multi-threaded system evaluation benchmark

threads 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 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64
Random number generator seed is 0 and will be ignored


Doing thread subsystem performance test
Thread yields per test: 1000 Locks used: 8
Threads 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.27ms

Threads 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 help
sysbench 0.5:  multi-threaded system evaluation benchmark

mutex 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 run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored


Initializing 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.94ms

Threads 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 help
Usage:
  sysbench [general-options]... --test=<test-name> [test-options]... command

General 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 exit

Log 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 test

Commands: prepare run cleanup help version

See '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 prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating 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 run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 128
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing 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.00
OLTP 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.33ms

Threads 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]# ll
total 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 0
end

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

-- 初始化默认值
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
   end

end

这个脚本主要功能是用来准备测试的表、测试数据、初始化测试中需要的一些默认值、清楚数据。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"
   end

end

-- 开始测试
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)
   end

end

这个脚本就是默认的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 TRANSACTION
update 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"
   end
end

function 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