使用

• 1 安装
• 2 使用
• 3 sysbench使用举例

1 安装

参照github README

1.1 二进制包安装

# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
# sudo yum -y install sysbench

1.2 源码安装

安装依赖

yum -y install make automake libtool pkgconfig libaio-devel
# For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install mariadb-devel openssl-devel
# For PostgreSQL support
yum -y install postgresql-devel

编译安装

./autogen.sh
# Add --with-pgsql to build with PostgreSQL support
./configure
make -j
make install

如果出现perl: warning: Falling back to the standard locale ("C"), 需要设置locale

echo "export LC_ALL=C" >> /root/.bashrc
source /root/.bashrc

要是没有安装开发包，即/usr/include/ 目录下面没有mysql文件夹。则需要执行安装:

2 使用

执行sysbench \–help，可以看到sysbench的详细使用方法。

[[email protected] ~]# sysbench --help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
  --threads=N                     number of threads to use [1]
  --events=N                      limit for total number of events [0]
  --time=N                        limit for total execution time in seconds [10]
  --warmup-time=N                 execute events for this many seconds with statistics disabled before the actual benchmark run with statistics enabled [0]
  --forced-shutdown=STRING        number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
  --thread-stack-size=SIZE        size of stack per thread [64K]
  --thread-init-timeout=N         wait time in seconds for worker threads to initialize [30]
  --rate=N                        average transactions rate. 0 for unlimited rate [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. []
  --debug[=on|off]                print more debugging info [off]
  --validate[=on|off]             perform validation checks where possible [off]
  --help[=on|off]                 print help and exit [off]
  --version[=on|off]              print version and exit [off]
  --config-file=FILENAME          File containing command line options
  --luajit-cmd=STRING             perform LuaJIT control command. This option is equivalent to 'luajit -j'. See LuaJIT documentation for more information

Pseudo-Random Numbers Generator options:
  --rand-type=STRING   random numbers distribution {uniform, gaussian, special, pareto, zipfian} to use by default [special]
  --rand-seed=N        seed for random number generator. When 0, the current time is used as an RNG seed. [0]
  --rand-spec-iter=N   number of iterations for the special distribution [12]
  --rand-spec-pct=N    percentage of the entire range where 'special' values will fall in the special distribution [1]
  --rand-spec-res=N    percentage of 'special' values to use for the special distribution [75]
  --rand-pareto-h=N    shape parameter for the Pareto distribution [0.2]
  --rand-zipfian-exp=N shape parameter (exponent, theta) for the Zipfian distribution [0.8]

Log options:
  --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]

  --percentile=N       percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
  --histogram[=on|off] print latency histogram in report [off]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers) [mysql]
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug[=on|off] print database-specific debug information [off]


Compiled-in database drivers:
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql options:
  --mysql-host=[LIST,...]          MySQL server host [localhost]
  --mysql-port=[LIST,...]          MySQL server port [3306]
  --mysql-socket=[LIST,...]        MySQL socket
  --mysql-user=STRING              MySQL user [sbtest]
  --mysql-password=STRING          MySQL password []
  --mysql-db=STRING                MySQL database name [sbtest]
  --mysql-ssl[=on|off]             use SSL connections, if available in the client library [off]
  --mysql-ssl-key=STRING           path name of the client private key file
  --mysql-ssl-ca=STRING            path name of the CA file
  --mysql-ssl-cert=STRING          path name of the client public key certificate file
  --mysql-ssl-cipher=STRING        use specific cipher for SSL connections []
  --mysql-compression[=on|off]     use compression, if available in the client library [off]
  --mysql-debug[=on|off]           trace all client library calls [off]
  --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
  --mysql-dry-run[=on|off]         Dry run, pretend that all MySQL client API calls are successful without executing them [off]

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]

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

See 'sysbench <testname> help' for a list of options for each test.

有几个大的测试主题,这个主要是针对os层面的。

• fileio - File I/O test(文件I/O性能）
• cpu - CPU performance test（CPU性能测试）
• memory - Memory functions speed test（内存访问）
• threads - Threads subsystem performance test基于线程的调度性能）
• mutex - Mutex performance test

oltp_*.lua: a collection of OLTP-like database benchmarks （OLTP数据库 如MySQL、Oracle、PostgreSQL）

sysbench [options]... [testname] [command] 

（1）command

command是sysbench要执行的命令，包括prepare、run、cleanup和help，顾名思义，

• prepare是为测试(某些测试才需要)提前准备数据，比如, 为fileio生成必要的文件, 为数据库benchmark填充测试数据
• run是执行正式的测试，
• cleanup是在测试完成后对产生的临时数据进行清理
• help: 针对testname显示帮助信息.

（2）testname

testname指定了要进行的测试，可以是内置的test名字(比如 fileio, memory, cpu等), sysbench内置的Lua脚本(比如oltp_read_only)或自己开发的. 如果没有指定testname, 或者testname是"-", sysbench会从标准输入获取一个Lua脚本

在老版本的sysbench中，可以通过--test参数指定测试的脚本；而在新版本中，--test参数已经声明为废弃，可以不使用--test，而是直接指定脚本。

例如，如下两种方法效果是一样的：

sysbench --test=./tests/include/oltp_legacy/oltp.lua
sysbench ./tests/include/oltp_legacy/oltp.lua

测试时使用的脚本为lua脚本，可以使用sysbench自带脚本，也可以自己开发。对于大多数应用，使用sysbench自带的脚本就足够了。不同版本的sysbench中，lua脚本的位置可能不同，可以自己在sysbench路径下使用find命令搜索oltp.lua。

P.S.：大多数数据服务都是oltp类型的，如果你不了解什么是oltp，那么大概率你的数据服务就是oltp类型的。

（3）options

options 包括通用的选项，另外不同的testname也包含特定的option，如mysql测试包含的数据库连接信息。特定测试的option可以通过sysbench testname help 查看。sysbench --help可以查看通用的option

2.1 通用选项

General options:
  --threads=N                     number of threads to use [1] #创建测试线程的数目。默认为1
  --events=N                      limit for total number of events [0] #请求的最大数目。默认为0，0代表不限制。
  --time=N                        limit for total execution time in seconds [10] #最大执行时间，单位是s。默认是0,不限制。
  --warmup-time=N                 execute events for this many seconds with statistics disabled before the actual benchmark run with statistics enabled [0]
  --forced-shutdown=STRING        number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off] #超过max-time强制中断。默认是off。
  --thread-stack-size=SIZE        size of stack per thread [64K] #每个线程的堆栈大小。默认是64K
  --thread-init-timeout=N         wait time in seconds for worker threads to initialize [30]
  --rate=N                        average transactions rate. 0 for unlimited rate [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. []
  --debug[=on|off]                print more debugging info [off] #是否显示更多的调试信息。默认是off
  --validate[=on|off]             perform validation checks where possible [off] #在可能情况下执行验证检查。默认是off
  --help[=on|off]                 print help and exit [off] #帮助信息
  --version[=on|off]              print version and exit [off] #版本信息
  --config-file=FILENAME          File containing command line options
  --luajit-cmd=STRING             perform LuaJIT control command. This option is equivalent to 'luajit -j'. See LuaJIT documentation for more information

注: 可通过加上相应后缀来指定所有size选项(比如--thread-stack-size)的数值, K表示Kilobytes, M, G, T.

2.2 随机数选项

sysbench提供了很多算法, 可以根据一个给定的概率分布生成分布的随机数.

  --rand-type=STRING   random numbers distribution {uniform, gaussian, special, pareto, zipfian} to use by default [special]
  --rand-seed=N        seed for random number generator. When 0, the current time is used as an RNG seed. [0]
  --rand-spec-iter=N   number of iterations for the special distribution [12]
  --rand-spec-pct=N    percentage of the entire range where 'special' values will fall in the special distribution [1]
  --rand-spec-res=N    percentage of 'special' values to use for the special distribution [75]
  --rand-pareto-h=N    shape parameter for the Pareto distribution [0.2]
  --rand-zipfian-exp=N shape parameter (exponent, theta) for the Zipfian distribution [0.8]

2.3 通用数据库选项

General database options:
  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers) [mysql]
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug[=on|off] print database-specific debug information [off]

2.3 MySQL连接信息参数

• --mysql-host：MySQL服务器主机名，默认localhost；如果在本机上使用localhost报错，提示无法连接MySQL服务器，改成本机的IP地址应该就可以了。
• --mysql-port：MySQL服务器端口，默认3306
• --mysql-user：用户名
• --mysql-password：密码

2.4 MySQL执行参数

2.5 pgsql选项

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]

2.6 sysbench fileio help

sysbench fileio help
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)
fileio options:
  --file-num=N              number of files to create [128]创建测试文件的数量。默认是128
  --file-block-size=N       block size to use in all IO operations [16384]测试时文件块的大小。默认是16384(16K)
  --file-total-size=SIZE    total size of files to create [2G]测试文件的总大小。默认是2G
  --file-test-mode=STRING   test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}文件测试模式{seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)}
  --file-io-mode=STRING     file operations mode {sync,async,mmap} [sync] 文件操作模式{sync(同步),async(异步),fastmmap(快速map映射),slowmmap(慢map映射)}。默认是sync
  --file-extra-flags=STRING additional flags to use on opening files {sync,dsync,direct} []使用额外的标志来打开文件{sync,dsync,direct} 。默认为空
  --file-fsync-freq=N       do fsync() after this number of requests (0 - don't use fsync()) [100]执行fsync()的频率。(0 – 不使用fsync())。默认是100
  --file-fsync-all[=on|off] do fsync() after each write operation [off]每执行完一次写操作就执行一次fsync。默认是off
  --file-fsync-end[=on|off] do fsync() at the end of test [on]在测试结束时才执行fsync。默认是on
  --file-fsync-mode=STRING  which method to use for synchronization {fsync, fdatasync} [fsync]使用哪种方法进行同步{fsync, fdatasync}。默认是fsync
  --file-merged-requests=N  merge at most this number of IO requests if possible (0 - don't merge) [0]如果可以，合并最多的IO请求数(0 – 表示不合并)。默认是0
  --file-rw-ratio=N         reads/writes ratio for combined test [1.5]     测试时的读写比例。默认是1.5

2.7 sysbench cpu help

sysbench  cpu help
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)
cpu options:
  --cpu-max-prime=N upper limit for primes generator [10000] 最大质数发生器数量。默认是10000

2.8 sysbench memory help

--memory-block-size=SIZE    size of memory block for test [1K]  测试时内存块大小。默认是1K
--memory-total-size=SIZE    total size of data to transfer [100G] 传输数据的总大小。默认是100G
--memory-scope=STRING       memory access scope {global,local} [global] 内存访问范围{global,local}。默认是global
--memory-oper=STRING        type of memory operations {read, write, none} [write]     内存操作类型。{read, write, none} 默认是write
--memory-access-mode=STRING memory access mode {seq,rnd} [seq] 存储器存取方式{seq,rnd} 默认是seq

2.9 sysbench threads help

# sysbench  threads help
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)
threads options:
  --thread-yields=N number of yields to do per request [1000] 每个请求产生多少个线程。默认是1000
  --thread-locks=N  number of locks per thread [8] 每个线程的锁的数量。默认是8

2.10 sysbench mutex help

# sysbench  mutex help
sysbench 1.0.7 (using bundled LuaJIT 2.1.0-beta2)
mutex options:
  --mutex-num=N   total size of mutex array [4096] 数组互斥的总大小。默认是4096
  --mutex-locks=N number of mutex locks to do per thread [50000] 每个线程互斥锁的数量。默认是50000
  --mutex-loops=N number of empty loops to do outside mutex lock [10000] 内部互斥锁的空循环数量。默认是10000

3 sysbench使用举例

在执行sysbench时，应该注意：

（1）尽量不要在MySQL服务器运行的机器上进行测试，一方面可能无法体现网络（哪怕是局域网）的影响，另一方面，sysbench的运行（尤其是设置的并发数较高时）会影响MySQL服务器的表现。

（2）可以逐步增加客户端的并发连接数（--thread参数），观察在连接数不同情况下，MySQL服务器的表现；如分别设置为10,20,50,100等。

（3）一般执行模式选择complex即可，如果需要特别测试服务器只读性能，或不使用事务时的性能，可以选择simple模式或nontrx模式。

（4）如果连续进行多次测试，注意确保之前测试的数据已经被清理干净。

下面是sysbench使用的一个例子：

（1）准备数据

# sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare

其中，执行模式为complex，使用了10个表，每个表有10万条数据，客户端的并发线程数为10，执行时间为120秒，每10秒生成一次报告。

（2）执行测试

将测试结果导出到文件中，便于后续分析。

# sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.10.10 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run >> /home/test/mysysbench.log

（3）清理数据

执行完测试后，清理数据，否则后面的测试会受到影响。

# sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.10.10 --mysql-port=3306 --mysql-user=root --mysql-password=123456 cleanup