基准测试工具之sysbench

sysbench是一个模块化的、跨平台、多线程 基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况

它主要包括以下几种方式的测试：

1、cpu性能

2、磁盘io性能

3、调度程序性能

4、内存分配及传输速度

5、POSIX线程性能

6、数据库性能(OLTP基准测试)

目前sysbench主要支持  mysql,pgsql,oracle 这3种数据库。

一 前期准备

1 下载 sysbench

  http://sf.net/projects/sysbench/

2 安装：

[root@rac3 ~]# tar zxvf sysbench-0.4.12.tar.gz         

[root@rac3 ~]# cd sysbench-0.4.12

[root@rac3 sysbench-0.4.12]# ls

acinclude.m4  autogen.sh  config     configure.ac  doc      install-sh   Makefile.in  mkinstalldirs  README-WIN.txt  TODO

aclocal.m4    ChangeLog   configure  COPYING       INSTALL  Makefile.am  missing      README         sysbench

[root@rac3 sysbench-0.4.12]# ./autogen.sh 

[root@rac3 sysbench-0.4.12]# 

[root@rac3 sysbench-0.4.12]# ./configure 

--prefix=/usr/local/sysbench \             --指定sysbench的安装目录

--with-mysql-includes=/usr/include/mysql \ --mysql的头文件

--with-mysql-libs=/var/lib/mysql           --mysql 链接库文件

默认是支持mysql的,如果要测试oracle或者pgsql 必须使用--with-oracle ,--with-pgsql 比如：

Note 

如果是oracle，在.bash_profile文件中添加：

export CC=cc

export CXX=c++

export CFLAGS="-m64 -I /opt/oracle/11.2.0/alifpre/rdbms/public"

export CXXFLAGS="$CFLAGS"

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib 

编译sysbench：

[oracle@rac3 sysbench-0.4.12]# ./autogen.sh 

[oracle@rac3 sysbench-0.4.12]# 

[oracle@rac3 sysbench-0.4.12]# ./configure

--prefix=/home/oracle/sysbench 

--with-oracle-libs=/opt/oracle/11.2.0/alifpre/lib 

--with-oracle 

--without-mysql  --可选

make ORA_LIBS=/opt/oracle/11.2.0/alifpre/lib/libclntsh.so && make install 

3 用法

 Usage:

  sysbench [general-options]... --test=<test-name> [test-options]... command

General options:

  --num-threads=N            number of threads to use [1] 默认为1 

  --max-requests=N           limit for total number of requests [10000] 最大的请求数

  --max-time=N               limit for total execution time in seconds [0] 默认为 0 为无限制

  --forced-shutdown=STRING   达到最大执行时间未结束,则强制关闭。默认是[off]

  --thread-stack-size=SIZE   size of stack per thread [32K]每个线程的stack的大小

  --init-rng=[on|off]        initialize random number generator [off] 

  --test=STRING              测试类型 cpu,fileio,oltp,mutex,memory

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

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

二 测试

前面介绍了sysbench 是一个可以测试 cpu性能,磁盘io性能,调度程序性能,内存分配及传输速度,POSIX线程性能,数据库性能.下面依次介绍

2.1 测试cup,cpu测试主要是进行素数的加法运算，例子中指定了最大的素数为 80000

[root@rac3 kernel]# sysbench --test=cpu --cpu-max-prime=80000 run  

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Doing CPU performance benchmark

Threads started!

Done.

Maximum prime number checked in CPU test: 80000

Test execution summary:

    total time:                          172.6663s

    total number of events:              10000

    total time taken by event execution: 172.6490

    per-request statistics:

         min:                                 17.07ms

         avg:                                 17.26ms

         max:                                 73.72ms

         approx.  95 percentile:              17.62ms

Threads fairness:

    events (avg/stddev):           10000.0000/0.00

    execution time (avg/stddev):   172.6490/0.00

2.2 测试fileio

 选项                      描述                   

--file-num       Number of files to create 默认为128

--file-block-size 读写操作是I/O的操作单位默认为16K

--file-total-size 测试文件的总大小 默认为2G

--file-io-mode   I/O mode. Possible values: sync, async, fastmmap, slowmmap (only if supported by the platform, see above). sync

--file-async-backlog Number of asynchronous operations to queue per thread (only for --file-io-mode=async, see above) 128

--file-extra-flags Additional flags to use with open(2)  

--file-fsync-freq Do fsync() after this number of requests (0 - don not use fsync()) 100

--file-fsync-all Do fsync() after each write operation no

--file-fsync-end Do fsync() at the end of the test yes

--file-fsync-mode Which method to use for synchronization. Possible values: fsync, fdatasync (see above) fsync

--file-merged-requests Merge at most this number of I/O requests if possible (0 - don not merge) 0

--file-rw-ratio  reads/writes ration for combined random read/write test 1.5

--file-test-mode  指定文件测试类型：

seqwr   sequential write

seqrewr sequential rewrite

seqrd   sequential read

rndrd   random read

rndwr   random write

rndrw   combined random read/write

下面的例子 prepare 命令创建了128个文件总共大小为10G ，文件读写模式为随机读写混合方式。run 命令则进行测试，并返回结果，cleanup 删除测试产生的文件！

[root@rac3 ~]# sysbench --num-threads=16 --test=fileio --file-total-size=10G --file-test-mode=rndrw prepare                                                   

sysbench 0.4.12:  multi-threaded system evaluation benchmark

128 files, 81920Kb each, 10240Mb total

Creating files for the test...

[root@rac3 ~]# sysbench --num-threads=16 --test=fileio --file-total-size=10G --file-test-mode=rndrw run

Operations performed:  6006 Read, 3994 Write, 12800 ther = 22800 Total

Read 93.844Mb  Written 62.406Mb  Total transferred 156.25Mb  (9.4882Mb/sec)

  607.24 Requests/sec executed

Test execution summary:

    total time:                          16.4678s

    total number of events:              10000

    total time taken by event execution: 225.4641

    per-request statistics:

         min:                                  0.01ms

         avg:                                 22.55ms

         max:                                498.74ms

         approx.  95 percentile:             101.48ms

Threads fairness:

    events (avg/stddev):           625.0000/29.59

    execution time (avg/stddev):   14.0915/0.30

[root@rac3 ~]# sysbench --num-threads=16 --test=fileio --file-total-size=10G --file-test-mode=rndrw cleanup

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Removing test files...

[root@rac3 ~]#   

2.3 memory 内存测试

sysbench 测试memory的时候是顺序读或写内存的。根据选项的不同，每次操作过程中，每个线程可以获取global或本地的数据块

[root@rac3 ~]# sysbench   --test=memory --memory-block-size=1k --memory-total-size=0.5G run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Doing memory operations speed test

Memory block size: 0K

Memory transfer size: 0M

Memory operations type: write

Memory scope type: global

Threads started!

Done.

Operations performed: 0 (    0.00 ops/sec)

0.00 MB transferred (0.00 MB/sec)

Test execution summary:

    total time:                          0.0002s

    total number of events:              0

    total time taken by event execution: 0.0000

    per-request statistics:

         min:                            18446744073709.55ms

         avg:                                  0.00ms

         max:                                  0.00ms

Threads fairness:

    events (avg/stddev):           0.0000/0.00

    execution time (avg/stddev):   0.0000/0.00

2.4 thread 测试

[root@rac3 ~]# sysbench --num-threads=64 --test=threads --thread-yields=100 --thread-locks=2 run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 64

Doing thread subsystem performance test

Thread yields per test: 100 Locks used: 2

Threads started!

Done.

Test execution summary:

    total time:                          2.0199s

    total number of events:              10000

    total time taken by event execution: 128.6847

    per-request statistics:

         min:                                  0.38ms

         avg:                                 12.87ms

         max:                               2016.38ms

         approx.  95 percentile:               0.42ms

Threads fairness:

    events (avg/stddev):           156.2500/262.89

    execution time (avg/stddev):   2.0107/0.00

2.5 mutex 测试

所有线程同时执行，获取短时间的mutex lock，以便测试mutex的实现！

[root@rac3 ~]# sysbench --test=mutex --mutex-num=4096 --mutex-locks=50000 --mutex-loops=20000 run 

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Doing mutex performance test

Threads started!

Done.

Test execution summary:

    total time:                          0.0053s

    total number of events:              1

    total time taken by event execution: 0.0051

    per-request statistics:

         min:                                  5.11ms

         avg:                                  5.11ms

         max:                                  5.11ms

         approx.  95 percentile:         10000000.00ms

Threads fairness:

    events (avg/stddev):           1.0000/0.00

    execution time (avg/stddev):   0.0051/0.00

2.6 oltp测试

sysbench 进行oltp 测试的之前，必须创建一个--mysql-db 指定的或者默认的sbtest 数据库，进行prepare的时候，sysbench自动创建一个sbtest表。

[root@rac3 ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-host=127.0.0.1  --mysql-db=test prepare

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql

Creating table 'sbtest'...

Creating 1000000 records in table 'sbtest'...  --创建了一个1000000行的表sbtest

进行测试 三种不同的测试模式：semple,complex,nontrx

[root@rac3 ~]# sysbench --test=oltp \

--mysql-table-engine=innodb \

--oltp-table-size=1000000 \

--mysql-user=root \

--mysql-host=127.0.0.1 \

--mysql-db=test run

OLTP test statistics:

    queries performed:

        read:                            140000

        write:                           50000

        other:                           20000

        total:                           210000

    transactions:                        10000  (292.09 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 190000 (5549.78 per sec.)

    other operations:                    20000  (584.19 per sec.)

Test execution summary:

    total time:                          34.2356s

    total number of events:              10000

    total time taken by event execution: 34.1541

    per-request statistics:

         min:                                  2.80ms

         avg:                                  3.42ms

         max:                                128.54ms

         approx.  95 percentile:               3.60ms

Threads fairness:

    events (avg/stddev):           10000.0000/0.00

    execution time (avg/stddev):   34.1541/0.00

[root@rac3 ~]# sysbench --test=oltp --mysql-table-engine=innodb \

--oltp-table-size=1000000 \

--mysql-user=root \

--mysql-host=127.0.0.1 \

--mysql-db=test \

--oltp-test-mode=complex run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

OLTP test statistics:

    queries performed:

        read:                            140000

        write:                           50000

        other:                           20000

        total:                           210000

    transactions:                        10000  (294.07 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 190000 (5587.40 per sec.)

    other operations:                    20000  (588.15 per sec.)

Test execution summary:

    total time:                          34.0051s

    total number of events:              10000

    total time taken by event execution: 33.9249

    per-request statistics:

         min:                                  2.78ms

         avg:                                  3.39ms

         max:                                114.85ms

         approx.  95 percentile:               3.57ms

Threads fairness:

    events (avg/stddev):           10000.0000/0.00

    execution time (avg/stddev):   33.9249/0.00

若进行mode为nontrx 的测试之前，已经进行了其他两种模式的测试，先执行cleanup 或者prepare

[root@rac3 ~]# sysbench --test=oltp \

--mysql-table-engine=innodb \

--oltp-table-size=1000000 \

--mysql-user=root \

--mysql-host=127.0.0.1  \

--mysql-db=test \

--oltp-test-mode=nontrx run 

OLTP test statistics:

    queries performed:

        read:                            10000

        write:                           0

        other:                           0

        total:                           10000

    transactions:                        10000  (12276.25 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 10000  (12276.25 per sec.)

    other operations:                    0      (0.00 per sec.)

Test execution summary:

    total time:                          0.8146s

    total number of events:              10000

    total time taken by event execution: 0.7892

    per-request statistics:

         min:                                  0.07ms

         avg:                                  0.08ms

         max:                                  0.41ms

         approx.  95 percentile:               0.09ms

Threads fairness:

    events (avg/stddev):           10000.0000/0.00

    execution time (avg/stddev):   0.7892/0.00

[root@rac3 ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-host=127.0.0.1  --mysql-db=test --oltp-test-mode=nontrx cleanup

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql

Dropping table 'sbtest'...

Done.