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

太过爱你忘了你带给我的痛 2022-09-24 12:22 682阅读 0赞

# 一、sysbench介绍 #

sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。  
目前sysbench代码托管在launchpad上，项目地址：[https://launchpad.net/sysbench][https_launchpad.net_sysbench]（原来的官网 [http://sysbench.sourceforge.net][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

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

[https_launchpad.net_sysbench]: https://launchpad.net/sysbench
[http_sysbench.sourceforge.net]: http://sysbench.sourceforge.net