一、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
1
2
3
注意事项：./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脚本，这样的测试就能更精确的测试业务的性能。
1
三、开始进行测试
通用配置
接下来我们来分别看一下各个模式的相关参数、测试方法和结果分析。
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'.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
参数详解：
–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
1
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
1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
主要看这行输出的信息：
Read 1.3618Gb Written 929.5Mb Total transferred 2.2695Gb (12.888Mb/sec)
824.84 Requests/sec executed
1
2
这行信息表示：在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...
1
2
3
4
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]
1
2
3
4
5
参数详解：
–cpu-max-prime=N 用来选项指定最大的素数，具体参数可以根据CPU的性能来设置，默认为10000
2、测试实例
根据官网的介绍可知：CPU测试使用64位整数，测试计算素数直到某个最大值所需要的时间。
sysbench --test=cpu --cpu-max-prime=20000 run
1
输出如下：
[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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
我们只需要关心测试的总时间(total time)即可(越小越忧)。
CPU性能测试有一个需要注意的地方，上面的测试只使用了一个线程，如果在两个cpu processor不同的电脑上做比较，这是不公平的。公平的做法是指定合理的线程数，如下所示：
sysbench --test=cpu --num-threads=`grep "processor" /proc/cpuinfo | wc -l` --cpu-max-prime=20000 run
1
补充知识：
查看CPU核数的方法
查看物理cpu个数
grep "physical id" /proc/cpuinfo | sort -u | wc -l
1
查看核心数量
grep "core id" /proc/cpuinfo | sort -u | wc -l
1
查看线程数量
grep "processor" /proc/cpuinfo | sort -u | wc -l
1
在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'.
1
2
3
4
5
6
7
8
9
10
11
12
参数详解：
–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
1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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'.
1
2
3
4
5
6
7
8
参数详解：
–thread-yields=N 指定每个请求的压力，默认为1000
–thread-locks=N 指定每个线程的锁数量，默认为8
2、测试实例
测试线程调度器的性能。对于高负载情况下测试线程调度器的行为非常有用。
sysbench --test=threads --num-threads=64 run
1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
关注的性能指标也是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'.
1
2
3
4
5
6
7
8
9
参数详解：
–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
1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
参数详解：
–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'
……………………
1
2
3
4
5
6
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
1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
主要关注如下几个值：
transactions: 2428 (39.27 per sec.)
read/write requests: 43704 (706.81 per sec.)
other operations: 4856 (78.53 per sec.)
1
2
3
四、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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
首先来看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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
这个脚本主要功能是用来准备测试的表、测试数据、初始化测试中需要的一些默认值、清楚数据。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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
这个脚本就是默认的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);
1
2
3
4
5
6
7
8
9
10
那么我们的脚本就可以这么定制：
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
我们另存为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
1
思考：
现在多机房热备的构架已经比较普片了，那么跨机房部署还有一个比较影响性能的因素就是网络吞吐量，sysbench中是否可以加入网络吞吐量的测试呢？
————————————————
版权声明：本文为CSDN博主「BOBO_Free」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hubo890224/article/details/52076347
713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
