mysql sysbench 使用方法!


mysql 压力测试之sysbench 


1.sysbench 的安装

 下载sysbench 源码包 http://down1.chinaunix.net/distfiles/sysbench-0.4.10.tar.gz

  

  下载下来之后会有一个sysbench-0.4.10.tar.gz  压缩包.

  解压后生成目录 sysbench-0.4.10 

  进入sysbench-0.4.10 这个目录 

  [root@linuxvm01 home]# cd  /home/sysbench-0.4.10

  

   编译安装sysbench 

  [root@linuxvm01 sysbench-0.4.10]# ./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib  && make && make install

checking build system type... x86_64-unknown-linux-gnu

checking host system type... x86_64-unknown-linux-gnu

checking target system type... x86_64-unknown-linux-gnu

checking for a BSD-compatible install... /usr/bin/install -c

checking whether build environment is sane... yes

checking for a thread-safe mkdir -p... /bin/mkdir -p

checking for gawk... gawk


TIP:

with-mysql-includes 指定上面确认的mysql-devel的路径,

with-mysql-libs 是安装mysql的路径,

#  当时这两个参数没弄明白编译安装的时候一直报错,


安装过程中出现的错误处理

(1).drv_mysql.c:32:19: error: mysql.h: No such file or directory

 参数 with-mysql-includes ,with-mysql-libs 不准确 

  


(2)./usr/bin/ld: cannot find -lmysqlclient_r

collect2: ld returned 1 exit status

make[2]: *** [sysbench] Error 1

make[2]: Leaving directory `/home/sysbench-0.4.10/sysbench‘

make[1]: *** [all-recursive] Error 1

make[1]: Leaving directory `/home/sysbench-0.4.10/sysbench‘

make: *** [all-recursive] Error 1 


安装下:rpm -ivh  MySQL-shared-5.6.22-1.el6.x86_64.rpm 


2.sysbench 的使用


sysbench 可以测试的项目有 

cpu的性能测试

内存性能测试

磁盘io吸能测试

oltp系统测试

最重要的还是 磁盘io吸能测试 和oltp 测试。


这里重点讲解下磁盘io吸能测试 和oltp 测试。 


=====磁盘io性能测试 ==============

IO测试分为三个阶段, 文件生成,执行测试,清理测试文件


首先生成需要测试的文件

sysbench --test=fileio --num-threads=2 --file-num=16 --file-total-size=30G --file-test-mode=rndrw --file-rw-ratio=2 prepare

[root@linuxvm01 ~]# sysbench --test=fileio --num-threads=2 --file-num=16 --file-total-size=30G --file-test-mode=rndrw --file-rw-ratio=2 prepare

sysbench 0.4.10:  multi-threaded system evaluation benchmark


16 files, 1966080Kb each, 30720Mb total

Creating files for the test...



然后执行测试

sysbench --test=fileio --num-threads=2 --file-num=16 --file-total-size=30G --file-test-mode=rndrw --file-rw-ratio=2 run

[root@linuxvm01 ~]# sysbench --test=fileio --num-threads=2 --file-num=16 --file-total-size=30G --file-test-mode=rndrw --file-rw-ratio=2 run

sysbench 0.4.10:  multi-threaded system evaluation benchmark


Running the test with following options:

Number of threads: 2


Extra file open flags: 0

16 files, 1.875Gb each

30Gb total file size

Block size 16Kb

Number of random requests for random IO: 10000

Read/Write ratio for combined random IO test: 2.00

Periodic FSYNC enabled, calling fsync() each 100 requests.

Calling fsync() at the end of test, Enabled.

Using synchronous I/O mode

Doing random r/w test

Threads started!

Done.


Operations performed:  6667 Read, 3333 Write, 1600 Other = 11600 Total

Read 104.17Mb  Written 52.078Mb  Total transferred 156.25Mb  (2.0936Mb/sec)

  133.99 Requests/sec executed


Test execution summary:

    total time:                          74.6328s

    total number of events:              10000

    total time taken by event execution: 142.3936

    per-request statistics:

         min:                                  0.01ms

         avg:                                 14.24ms

         max:                                305.56ms

         approx.  95 percentile:              37.48ms


Threads fairness:

    events (avg/stddev):           5000.0000/12.00

    execution time (avg/stddev):   71.1968/0.09


这个里面最重要的需要关注的 吞吐量(2.0936Mb/sec) ,

每秒请求数( 133.99 Requests/sec executed)

还有95%的请求耗时( approx.  95 percentile:              37.48ms) 

最后清理测试文件

sysbench --test=fileio --num-threads=2 --file-num=16 --file-total-size=30G --file-test-mode=rndrw --file-rw-ratio=2 cleanup





=====OLTP 系统性能测试 ==============

otlp性能测试分为两阶段.

1.准备阶段

2.执行阶段


--准备阶段

[root@linuxvm01 ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=4000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-db=test  --mysql-user=root  --mysql-password=anyu@2014 prepare

sysbench 0.4.10:  multi-threaded system evaluation benchmark


No DB drivers specified, using mysql

Creating table ‘sbtest‘...

Creating 4000000 records in table ‘sbtest‘...


-- 执行阶段


[root@linuxvm01 ~]# sysbench --num-threads=8 --max-requests=4000000 --test=oltp --mysql-table-engine=innodb --oltp-table-size=4000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-db=test --mysql-user=root  --mysql-password=anyu@2014 run

sysbench 0.4.10:  multi-threaded system evaluation benchmark


No DB drivers specified, using mysql

WARNING: Preparing of "BEGIN" is unsupported, using emulation

(last message repeated 7 times)

Running the test with following options:

Number of threads: 8


Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 4000000

Threads started!



Done.


OLTP test statistics:

    queries performed:

        read:                            56000000

        write:                           20000000

        other:                           8000000

        total:                           84000000

    transactions:                        4000000 (125.37 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 76000000 (2381.99 per sec.)

    other operations:                    8000000 (250.74 per sec.)


Test execution summary:

    total time:                          31906.1255s

    total number of events:              4000000

    total time taken by event execution: 255224.4528

    per-request statistics:

         min:                                  2.69ms

         avg:                                 63.81ms

         max:                               5557.09ms

         approx.  95 percentile:             178.90ms


Threads fairness:

    events (avg/stddev):           500000.0000/2282.56

    execution time (avg/stddev):   31903.0566/0.09



这段输出中包含了

QPS(transactions:                        4000000 (125.37 per sec.)) ,

tps( transactions:                        4000000 (125.37 per sec.))

百分之95的请求耗时在178.90ms : approx.  95 percentile:             178.90ms


参考链接:

http://blog.chinaunix.net/uid-177564-id-3715242.html

http://blog.chinaunix.net/uid-20639775-id-2955761.html

http://www.anbob.com/archives/2177.html

http://imysql.cn/node/312



本文出自 “SQLServer MySQL” 博客,请务必保留此出处http://dwchaoyue.blog.51cto.com/2826417/1597221

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。