【MySQL】利用sysbench进行基准测试

/*sysbench安装篇------------------------------------------------------------------------------------------------------*/
⑴安装epel包后以便安装bzr客户端：
rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
⑵获取sysbench
cd /usr/local/src
 bzr branch lp:sysbench
⑶编译安装
[root@node1 sysbench]# ./autogen.sh
[root@node1 bin]#./configure --prefix=/usr/local/sysbench   --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
[root@node1 bin]#make && make install
编译出现错误
/usr/bin/ld: cannot find -lmysqlclient_r
collect2: ld returned 1 exit status
make[2]: *** [sysbench] Error 1
解决方法：
mv libmysqlclient_r.so.16.0.0 libmysqlclient_r.so



/*sysbench使用篇---------------------------------------------------------------------------------------------------*/

⑴全局性参数
root@node1 bin]# ./sysbench help
sysbench 0.5:  multi-threaded system evaluation benchmark

Usage:
  sysbench [general-options]... --test= [test-options]... command

General options:
  --num-threads=N             number of threads to use [1]//使用的线程数量
  --max-requests=N            limit for total number of requests [10000]//sysbench最大请求数，为0时，表示无限制使用
  --max-time=N                limit for total execution time in seconds [0]//sysbench执行最长的时间
  --forced-shutdown=STRING    amount of time to wait after --max-time before forcing shutdown [off]//超过--max-time强制结束sysbench
  --thread-stack-size=SIZE    size of stack per thread [64K]
  --tx-rate=N                 target transaction rate (tps) [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. []//dump统计数据并重新复位计数器
  --test=STRING               test to run//测试的内容项，cpu
  --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 [off]//是否随机初始化数据,如果不随机化那么初始好的数据每行内容除了主键不同外其他完全相同。
  --rand-init=[on|off]        initialize random number generator [off]//
  --rand-type=STRING          random numbers distribution {uniform,gaussian,special,pareto} [special]//数据分布-均匀分布，正态分布，特殊分布
  --rand-spec-iter=N          number of iterations used for numbers generation [12]
  --rand-spec-pct=N           percentage of values to be treated as 'special' (for special distribution) [1]//应该是百分一的值是特殊值
  --rand-spec-res=N           percentage of 'special' values to use (for special distribution) [75]//数据中百分之75是特殊值
  --rand-seed=N               seed for random number generator, ignored when 0 [0]
  --rand-pareto-h=N           parameter h for pareto distibution [0.2]

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

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

Commands: prepare run cleanup help version

See 'sysbench --test= help' for a list of options for each test.

⑵CPU参数
[root@node1 bin]# ./sysbench --test=cpu help
sysbench 0.5:  multi-threaded system evaluation benchmark

cpu options:
  --cpu-max-prime=N      upper limit for primes generator [10000]//求一万以内的素数（默认）

No help available for test 'cpu'.

来一次cpu的测试
[root@node1 bin]#
[root@node1 bin]# ./sysbench --test=cpu --cpu-max-prime=20000 run
sysbench 0.5:  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


Primer numbers limit: 20000

Threads started!
General statistics:
    total time:                          49.3380s
    total number of events:              10000
    total time taken by event execution: 49.3346s
    response time:
         min:                                  4.93ms
         avg:                                  4.93ms
         max:                                  5.05ms
         approx.  95 percentile:               4.94ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   49.3346/0.00

⑵IO参数
[root@node1 bin]# ./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-async-backlog=N        number of asynchronous operatons to queue per thread [128]//每个线程的队列多少个异步操作
  --file-extra-flags=STRING     additional flags to use on opening files {sync,dsync,direct} []//direct不写iocache，直接写磁盘
  --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]//执行完一次写操作，就执行一次fsync。
  --file-fsync-end=[on|off]     do fsync() at the end of test [on]// 每在测试结束时执行fsync函数
  --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]//尽可能的合并io操作
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]//读写比例1.5

No help available for test 'fileio'.
[root@node1 bin]#


来一次IO测试
准备数据：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-num=16 --file-total-size=10G prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

16 files, 655360Kb each, 10240Mb 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
10737418240 bytes written in 335.80 seconds (30.49 MB/sec).
看看是否有创建的16个文件(创建文件为你的当前目录)
[root@node1 test]# ls -l
total 10485872
-rw------- 1 root root 671088640 Jun 11 09:43 test_file.0
-rw------- 1 root root 671088640 Jun 11 09:43 test_file.1
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.10
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.11
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.12
-rw------- 1 root root 671088640 Jun 11 09:47 test_file.13
-rw------- 1 root root 671088640 Jun 11 09:48 test_file.14
-rw------- 1 root root 671088640 Jun 11 09:48 test_file.15
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.2
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.3
-rw------- 1 root root 671088640 Jun 11 09:44 test_file.4
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.5
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.6
-rw------- 1 root root 671088640 Jun 11 09:45 test_file.7
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.8
-rw------- 1 root root 671088640 Jun 11 09:46 test_file.9
[root@node1 test]#

测试：
[root@node1 test]# /usr/local/sysbench/bin/sysbench  --test=fileio --file-total-size=10G --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.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


Extra file open flags: 3
16 files, 640Mb each
10Gb total file size
Block size 16Kb
Number of IO requests: 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!

Operations performed:  21301 reads, 0 writes, 0 Other = 21301 Total
Read 332.83Mb  Written 0b  Total transferred 332.83Mb  (1.8478Mb/sec)--------------从此处可以看出此机器的性能较差,见过一台机器的75MB/sec,4895Request/sec，应该算性能比较好的
  118.26 Requests/sec executed

General statistics:
    total time:                          180.1207s
    total number of events:              21301
    total time taken by event execution: 2881.0238s
    response time:
         min:                                  0.38ms
         avg:                                135.25ms
         max:                                595.49ms
         approx.  95 percentile:             248.44ms

Threads fairness:
    events (avg/stddev):           1331.3125/190.50
    execution time (avg/stddev):   180.0640/0.03

[root@node1 test]#

删除测试数据
[root@node1 test]# /usr/local/sysbench/bin/sysbench --test=fileio --file-num=16 --file-total-size=10G cleanup
sysbench 0.5:  multi-threaded system evaluation benchmark

Removing test files...
[root@node1 test]# ls -l
total 0
[root@node1 test]#


⑶线程参数
[root@node1 bin]# ./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'.
[root@node1 bin]#


⑷在线事物处理参数oltp
[root@VM-MYSQL-TEST-LiDan ~]# sysbench --test=oltp help
sysbench 0.5:  multi-threaded system evaluation benchmark

PANIC: unprotected error in call to Lua API (cannot open oltp: No such file or directory)
sysbench-0.5已将oltp参数去掉,最新版开始采用lua脚本---当然本人很不习惯，但参数还是跟以前一样

http://blog.csdn.net/lidan3959/article/details/36876517
--oltp-test-mode=STRING         test type to use {simple,complex,nontrx,sp} [complex]//采用得测试模式
--oltp-reconnect-mode=STRING    reconnect mode {session,transaction,query,random} [session]//重新连接模式{session(不使用重新连接。每个线程断开只在测试结束),transaction(在每次事务结束后重新连接),query(在每个SQL语句执行完重新连接),random(对于每个事务随机选择以上重新连接模式)}。默认是session
--oltp-sp-name=STRING           name of store procedure to call in SP test mode []//跟上面的测试对应，采用自定义存储过程
--oltp-read-only=[on|off]       generate only 'read' queries (do not modify database) [off]//产生只读查询
--oltp-skip-trx=[on|off]        skip BEGIN/COMMIT statements [off]//感觉相当于事务没用
--oltp-range-size=N             range size for range queries [100]//查询的范围1-100行，可定小于--oltp-table-size
--oltp-point-selects=N          number of point selects [10]//一个事物中点（点指的是测试模式语句中的id=N）查询十次
--oltp-simple-ranges=N          number of simple ranges [1]//一个事务中的范围查询的次数（范围指的是between N and M）
--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       mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]//nontrx模式只用select语句
--oltp-auto-inc=[on|off]        whether AUTO_INCREMENT (or equivalent) should be used on id column [on]//AUTO_INCREMENT开启
--oltp-connect-delay=N          time in microseconds to sleep after connection to database [10000]//什么含义？
--oltp-user-delay-min=N         minimum time in microseconds to sleep after each request [0]
--oltp-user-delay-max=N         maximum time in microseconds to sleep after each request [0]
--oltp-table-name=STRING        name of test table [sbtest]//测试的表，默认sbtest
--oltp-table-size=N             number of records in test table [10000]//表的行数
--oltp-dist-type=STRING         random numbers distribution {uniform,gaussian,special} [special]----参考全局参数
--oltp-dist-iter=N              number of iterations used for numbers generation [12]
--oltp-dist-pct=N               percentage of values to be treated as 'special' (for special distribution) [1]
--oltp-dist-res=N               percentage of 'special' values to use (for special distribution) [75]
 --oltp-tables-count=N    表示生成几个测试表
--mysql-table-engine=innodb  指定表类型
                     
关于测试模式
测试模式有三种：
①simple mode，如果指定为此mode,那么在运行过程中， sysbench的每个thread将只会运行如下的语句: SELECT c FROM sbtest WHERE id=N  N值是个随机值，它的范围1...
②advanced transactional mode如果启用此模式，那么sysbench的每个thread将会在测试表上（sbtest）上执行事务。不过要注意的是，对于表(sbtest使用 的引擎)是否支持事务，其执    行的过程是不一样的。如果是innodb engine,那么它将会使用BEGIN/COMMIT 语句来start/stop一个事务；如果是MyISAM engine,sysbench将会使用LOCK TABLES/UNLOCK TABLES语句。    在一个事务中若是有一些记录被删除了，不用担心，在同一个事务中，会有相同的记录被补回。所以同一张sbtest测试表可以多次用来进 行测试。这个mode中包含执行的语句如下：
    Point queries: SELECT c FROM sbtest WHERE id=N
    Range queries: SELECT c FROM sbtest WHERE id BETWEEN N AND M
    Range SUM() queries: SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M
    Range ORDER BY queries: SELECT c FROM sbtest WHERE id between N and M ORDER BY c
    Range DISTINCT queries: SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c
    UPDATEs on index column: UPDATE sbtest SET k=k+1 WHERE id=N
    UPDATEs on non-index column: UPDATE sbtest SET c=N WHERE id=M
    DELETE queries:DELETE FROM sbtest WHERE id=N
    INSERT queries:INSERT INTO sbtest VALUES (...)  
③Non-transcational mode这个mode与simple mode相似，不同的地方是可以自主选择想要运行的query,这里要注意的是，它与Advanced transcational mode的区别，它在查询请求间不会    保护测试表中的数据，所以如果有多次测试的话，需要进行cleanup/prepare操作。它包含执行的语句如下：
     Point queries: SELECT pad FROM sbtest WHERE id=N
    UPDATEs on index column:UPDATE sbtest SET k=k+1 WHERE id=N
    UPDATEs on non-index column:UPDATE sbtest SET c=N WHERE id=M
    DELETE queries:DELETE FROM sbtest WHERE id=N
    The generated row IDs are unique over each test run, so no row is deleted twice.
    INSERT queries:INSERT INTO sbtest (k, c, pad) VALUES(N, M, S)  


来一次mysql的事务测试
准备数据：
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=50 --max-requests=100000000 --report-interval=1 --test=/usr/local/sysbench/oltp/oltp.lua prepare
sysbench 0.5:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 6000000 records into 'sbtest1'
测试数据
[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=50 --max-requests=100000000 --report-interval=1 --max-time=20  --test=/usr/local/sysbench/oltp/oltp.lua run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 50
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored


Threads started!

[   1s] threads: 50, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00
[   2s] threads: 50, tps: 0.00, reads: 235.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00
[   3s] threads: 50, tps: 0.00, reads: 86.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00
[   4s] threads: 50, tps: 0.00, reads: 99.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00
[   5s] threads: 50, tps: 0.00, reads: 107.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00
[   6s] threads: 50, tps: 0.00, reads: 53.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            246162
        write:                           70332
        other:                           35166
        total:                           351660
    transactions:                        17583  (17.54 per sec.)
    read/write requests:                 316494 (315.67 per sec.)
    other operations:                    35166  (35.07 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          1002.6230s
    total number of events:              17583
    total time taken by event execution: 50069.6673s
    response time:
         min:                                 76.80ms
         avg:                               2847.62ms
         max:                              11148.33ms
         approx.  95 percentile:            5788.65ms

Threads fairness:
    events (avg/stddev):           351.6600/9.02
    execution time (avg/stddev):   1001.3933/0.75

[root@node1 test]#

[root@node1 test]# /usr/local/sysbench/bin/sysbench --mysql-host=192.168.0.3 --mysql-port=3306 --mysql-user=root --mysql-password=tianqu --mysql-db=test --oltp-tables-count=10 --oltp-table-size=6000000 --num-threads=8 --max-requests=1024 --report-interval=1   --test=/usr/local/sysbench/oltp/oltp.lua  cleanup
sysbench 0.5:  multi-threaded system evaluation benchmark

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
[root@node1 test]#

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30221425/viewspace-1695811/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30221425/viewspace-1695811/