sysbench基准测试

下载sysbench:http://dev.mysql.com/downloads/benchmarks.html
解压安装：
tar -zxvf sysbench-0.4.12.10.tar.gz
cd sysbench-0.4.12.10
./configure
sudo make install
下面测试fileio，测试环境centos7，i7,2核，2G内存
创建文件：
[donald@test test]$ sysbench --test=fileio  --file-num=16 --file-total-size=2G prepare
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

16 files, 131072Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
Creating file test_file.8
Creating file test_file.9
Creating file test_file.10
Creating file test_file.11
Creating file test_file.12
Creating file test_file.13
Creating file test_file.14
Creating file test_file.15
2147483648 bytes written in 53.74 seconds (38.11 MB/sec).
创建16的文件，大小为2G
测试：
[donald@test test]$ sysbench --test=fileio  --file-num=16 --file-total-size=2G --file-test-mode=rndrd --max-time=180 --max-requests=100000000 --num-threads=16 --init-rng=on --file-num=16 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 run
sysbench 0.4.12.10:  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
16 files, 128Mb each
2Gb 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 read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

Operations performed:  31482 reads, 0 writes, 0 Other = 31482 Total
Read 491.91Mb  Written 0b  Total transferred 491.91Mb (2.7314Mb/sec)
  174.81 Requests/sec executed

General statistics:
    total time:                          180.0952s
    total number of events:              31482
    total time taken by event execution: 2880.2231
    response time:
         min:                                  0.26ms
         avg:                                 91.49ms
         max:                                745.05ms
         approx.  95 percentile:             264.88ms

Threads fairness:
    events (avg/stddev):           1967.6250/50.40
    execution time (avg/stddev):   180.0139/0.03
随机读取的性能为2.7314Mb/sec，随机读取的IOPS为174.81
清除测试文件：
[donald@test test]$ sysbench --test=fileio --file-num=16 --file-total-size=2G cleanup
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

Removing test files...

测试顺序读、写，随机读、写模式fileio性能：
vim test.sh
添加如下内容：
#!/bin/bash
#==============================================================================
#
#          FILE: sysbench_auto.sh
#
#         USAGE: ./sysbench_auto.sh
#
#   DESCRIPTION: This file is sysbench_auto.sh
#        AUTHOR: Kevin Lu (kevin), [e-mail]
#  ORGANIZATION: cmcc
#       CREATED: 02/26/2014 17:35
#      REVISION: v1.0.1
#==============================================================================
for size in {2G,4G}
do
for mode in {seqwr,seqrewr,seqrd,rndrd,rndwr,rndrw}
do
for blksize in {4096,16384}
do
sysbench --test=fileio --file-num=8 --file-total-size=$size prepare
for threads in {8,16}
do
echo "=============testing $blksize in $threads threads"
echo PARAS $size $mode $threads $blksize > sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize
for i in {1,2}
do
sysbench --test=fileio --file-total-size=$size --file-test-mode=$mode --max-time=180 --max-requests=100000 --num-threads=$threads --init-rng=on --file-num=8 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=$blksize run | tee -a sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize 2>&1
done
done
sysbench --test=fileio --file-total-size=$size cleanup
done
done
done

:wq
保存退出，添加可执行属性
注意命令过长的换行问题
chmod 744 test.sh
执行test.sh
[donald@test test]$ ./test.sh
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

8 files, 262144Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: 0
Reusing existing file test_file.0
Reusing existing file test_file.1
Reusing existing file test_file.2
Reusing existing file test_file.3
Reusing existing file test_file.4
Reusing existing file test_file.5
Reusing existing file test_file.6
Reusing existing file test_file.7
No bytes written.
=============testing 4096 in 8 threads
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

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

Random number generator seed is 0 and will be ignored


Extra file open flags: 4000
8 files, 256Mb each
2Gb total file size
Block size 4Kb
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential write (creation) test
Threads started!
执行以上test.sh根据不同文件totalsize，blocksize，thead-num测试fileio，每隔案例测试两边，产生相应的测试文件报告如下：
这里只是部分结果：
[donald@test test]$ ls -al
total 2097216
drwxr-xr-x   2 donald user      4096 Aug  1 16:23 .
drwx------. 18 donald user      4096 Aug  1 15:52 ..
-rw-r--r--   1 donald user      1841 Aug  1 16:26 sysbench-size-2G-mode-rndrd-threads-8-blksz-4096
-rw-r--r--   1 donald user      2274 Aug  1 16:23 sysbench-size-2G-mode-seqrd-threads-16-blksz-16384
-rw-r--r--   1 donald user      2275 Aug  1 16:20 sysbench-size-2G-mode-seqrd-threads-16-blksz-4096
-rw-r--r--   1 donald user      2273 Aug  1 16:21 sysbench-size-2G-mode-seqrd-threads-8-blksz-16384
-rw-r--r--   1 donald user      2272 Aug  1 16:18 sysbench-size-2G-mode-seqrd-threads-8-blksz-4096
-rw-r--r--   1 donald user      2282 Aug  1 16:16 sysbench-size-2G-mode-seqrewr-threads-16-blksz-16384
-rw-r--r--   1 donald user      2285 Aug  1 16:13 sysbench-size-2G-mode-seqrewr-threads-16-blksz-4096
-rw-r--r--   1 donald user      2281 Aug  1 16:15 sysbench-size-2G-mode-seqrewr-threads-8-blksz-16384
-rw-r--r--   1 donald user      2280 Aug  1 16:11 sysbench-size-2G-mode-seqrewr-threads-8-blksz-4096
-rw-r--r--   1 donald user      2301 Aug  1 16:08 sysbench-size-2G-mode-seqwr-threads-16-blksz-16384
-rw-r--r--   1 donald user      2307 Aug  1 16:03 sysbench-size-2G-mode-seqwr-threads-16-blksz-4096
-rw-r--r--   1 donald user      2299 Aug  1 16:06 sysbench-size-2G-mode-seqwr-threads-8-blksz-16384
-rw-r--r--   1 donald user      2449 Aug  1 15:58 sysbench-size-2G-mode-seqwr-threads-8-blksz-4096

浏览相关报告：
[donald@test test]$ cat sysbench-size-2G-mode-seqwr-threads-8-blksz-4096
PARAS 2G seqwr 8 4096
##顺序读写性能，totalsize，顺序读写，8线程，blocksize
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

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

Random number generator seed is 0 and will be ignored


Extra file open flags: 4000
8 files, 256Mb each
2Gb total file size
Block size 4Kb
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential write (creation) test
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

Operations performed:  0 reads, 439466 writes, 0 Other = 439466 Total
Read 0b  Written 1.6764Gb  Total transferred 1.6764Gb  (9.5369Mb/sec)
2441.45 Requests/sec executed

General statistics:
    total time:                          180.0021s
    total number of events:              439466
    total time taken by event execution: 1438.9983
    response time:
         min:                                  0.20ms
         avg:                                  3.27ms
         max:                                502.77ms
         approx.  95 percentile:              10.49ms

Threads fairness:
    events (avg/stddev):           54933.2500/906.99
    execution time (avg/stddev):   179.8748/0.00

sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

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

Random number generator seed is 0 and will be ignored


Extra file open flags: 4000
8 files, 256Mb each
2Gb total file size
Block size 4Kb
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential write (creation) test
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

Operations performed:  0 reads, 480782 writes, 0 Other = 480782 Total
Read 0b  Written 1.834Gb  Total transferred 1.834Gb (10.433Mb/sec)
2670.93 Requests/sec executed

General statistics:
    total time:                          180.0054s
    total number of events:              480782
    total time taken by event execution: 1438.9781
    response time:
         min:                                  0.18ms
         avg:                                  2.99ms
         max:                                362.46ms
         approx.  95 percentile:               9.06ms

Threads fairness:
    events (avg/stddev):           60097.7500/1382.01
    execution time (avg/stddev):   179.8723/0.00


测试mysql的OLTP性能
在test库下创建一张800万条记录的表sbtest：
[donald@test mysqldata]$ sysbench --test=oltp --oltp-table-size=8000000 --db-driver=mysql --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=donald  --mysql-password='123456' --mysql-db=test prepare
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...
Creating 8000000 records in table 'sbtest'...
执行测试run

[donald@test test]$ sysbench --test=oltp --oltp-table-size=8000000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=1800 --db-driver=mysql --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=donald  --mysql-password='123456' --mysql-db=test run > result.log
参数说明：
--max-time=1800 测试时长为半小时
--mysql-db=test 测试数据库名
查看测试结果：
[donald@test test]$ cat result.log
sysbench 0.4.12.10:  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


Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Using 1 test tables
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            141288
        write:                           50460
        other:                           20184
        total:                           211932
    transactions:                        10092  (5.60 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 191748 (106.35 per sec.)
    other operations:                    20184  (11.19 per sec.)

General statistics:
    total time:                          1803.0694s
    total number of events:              10092
    total time taken by event execution: 28832.9060
    response time:
         min:                                278.91ms
         avg:                               2857.01ms
         max:                              25551.90ms
         approx.  95 percentile:            6528.87ms

Threads fairness:
    events (avg/stddev):           630.7500/14.37
    execution time (avg/stddev):   1802.0566/0.49

从报告中，可以看出TPS为5.60 per sec。