mysql性能测试工具之sysbench

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

(1).cpu性能

(2).磁盘io性能

(3).调度程序性能

(4).内存分配及传输速度

(5).POSIX线程性能

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

mysql dba技术群 378190849

武汉-linux运维群 236415619

1.下载和安装sysbench工具

[root@centos179min home]# wget http://down1.chinaunix.net/distfiles/sysbench-0.4.10.tar.gz

[root@centos179min home]# cd sysbench-0.4.10

[root@centos179min sysbench-0.4.10]# ./configure  --help | grep mysql        --自己仔细查看安装帮助

  --with-mysql            compile with MySQL support (default is enabled)

  --with-mysql-includes   path to MySQL header files

  --with-mysql-libs       path to MySQL libraries

Report bugs to alexeyk@mysql.com.

[root@centos179min sysbench-0.4.10]# ./configure  --prefix=/usr/local/sysbench-0.4.10 --with-mysql --with-mysql-includes=/usr/local/mysql-5.7.4-m14-linux/include/ --with-mysql-libs=/usr/local/mysql-5.7.4-m14-linux/lib/

[root@centos179min sysbench-0.4.10]# make        --这时如果以下错误

drv_mysql.c: In function 'mysql_drv_store_results':

drv_mysql.c:784: error: 'MYSQL' undeclared (first use in this function)

drv_mysql.c:784: error: 'con' undeclared (first use in this function)

drv_mysql.c:785: error: 'MYSQL_RES' undeclared (first use in this function)

drv_mysql.c:785: error: 'res' undeclared (first use in this function)

drv_mysql.c:786: error: 'MYSQL_ROW' undeclared (first use in this function)

drv_mysql.c:786: error: expected ';' before 'row'

drv_mysql.c:834: error: 'ER_LOCK_DEADLOCK' undeclared (first use in this function)

drv_mysql.c:834: error: 'ER_LOCK_WAIT_TIMEOUT' undeclared (first use in this function)

drv_mysql.c:850: error: 'row' undeclared (first use in this function)

drv_mysql.c: In function 'mysql_drv_free_results':

drv_mysql.c:874: error: 'MYSQL_RES' undeclared (first use in this function)

drv_mysql.c:874: error: expected expression before ')' token

make[3]: *** [libsbmysql_a-drv_mysql.o] Error 1

make[3]: Leaving directory `/home/sysbench-0.4.10/sysbench/drivers/mysql'

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

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

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

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

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

[root@centos179min sysbench-0.4.10]# yum install mysql-devel      --解决方法,安装mysql-devel开发包

[root@centos179min sysbench-0.4.10]# rpm -ql mysql-devel | grep include     --查看开发包的路径

[root@centos179min sysbench-0.4.10]# ./configure  --prefix=/usr/local/sysbench-0.4.10 --with-mysql --with-mysql-includes=/usr/include/mysql/   --with-mysql-libs=/usr/local/mysql-5.7.4-m14-linux/lib/mysql   

[root@centos179min sysbench-0.4.10]# make        --这时如果再有以下错误

../libtool: line 841: X--tag=CC: command not found

../libtool: line 874: libtool: ignoring unknown tag : command not found

../libtool: line 841: X--mode=link: command not found

../libtool: line 1007: *** Warning: inferring the mode of operation is deprecated.: command not found

../libtool: line 1008: *** Future versions of Libtool will require --mode=MODE be specified.: command not found

../libtool: line 2234: X-g: command not found

../libtool: line 2234: X-O2: command not found

../libtool: line 1954: X-L/usr/local/mysql-5.7.4-m14-linux/lib/mysql: No such file or directory

../libtool: line 2403: Xsysbench: command not found

../libtool: line 2408: X: command not found

../libtool: line 2415: Xsysbench: command not found

../libtool: line 2550: X-lmysqlclient_r: command not found

../libtool: line 2550: X-lrt: command not found

../libtool: line 2550: X-lm: command not found

../libtool: line 2632: X-L/home/sysbench-0.4.10/sysbench: No such file or directory

../libtool: line 2550: X-lmysqlclient_r: command not found

../libtool: line 2550: X-lrt: command not found

../libtool: line 2550: X-lm: command not found

../libtool: line 2632: X-L/home/sysbench-0.4.10/sysbench: No such file or directory

[root@centos179min sysbench-0.4.10]# vim libtool       --解决方法(将libtool文件中的"ECHO="改为"echo=")

ECHO="echo"       修改为   echo="echo"

[root@centos179min sysbench-0.4.10]# make                    --此时不会再有错误了

[root@centos179min sysbench-0.4.10]# make install

[root@centos179min sysbench-0.4.10]# echo $?                 --检查是否安装成功

0

[root@centos179min sysbench-0.4.10]# ll /usr/local/sysbench-0.4.10/bin/

total 288

-rwxr-xr-x. 1 root root 293649 Oct  8 20:00 sysbench

[root@centos179min sysbench-0.4.10]#

2.测试CPU的性能

[root@centos179min bin]# ./sysbench  --help |more             --命令帮助                  

Usage:

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

General options:

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

  --max-requests=N           limit for total number of requests [10000]

  --max-time=N               limit for total execution time in seconds [0]

  --thread-stack-size=SIZE   size of stack per thread [32K]

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

  --test=STRING              test to run

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

Commands: prepare run cleanup help version

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

[root@centos179min bin]# ./sysbench  --test=cpu --cpu-max-prime=20000 run     --测试CPU的性能

sysbench 0.4.10:  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: 20000

Test execution summary:

    total time:                          23.3571s

    total number of events:              10000

    total time taken by event execution: 23.3527

    per-request statistics:

         min:                                  2.23ms

         avg:                                  2.34ms

         max:                                 11.08ms

         approx.  95 percentile:               2.58ms

Threads fairness:

    events (avg/stddev):           10000.0000/0.00

    execution time (avg/stddev):   23.3527/0.00

[root@centos179min bin]#

3.线程(threads)测试

[root@centos179min bin]# ./sysbench  --test=threads --num-threads=64 run 

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 64

Doing thread subsystem performance test

Thread yields per test: 1000 Locks used: 8

Threads started!

Done.

Test execution summary:

    total time:                          4.8920s

    total number of events:              10000

    total time taken by event execution: 311.4399

    per-request statistics:

         min:                                  0.39ms

         avg:                                 31.14ms

         max:                               1167.60ms

         approx.  95 percentile:              87.74ms

Threads fairness:

    events (avg/stddev):           156.2500/9.58

    execution time (avg/stddev):   4.8662/0.02

[root@centos179min bin]#

 4.内存(memory)测试

[root@centos179min bin]# ./sysbench --test=memory --memory-block-size=8K --memory-total-size=2G  --num-threads=16 run

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 16

Doing memory operations speed test

Memory block size: 8K

Memory transfer size: 2048M

Memory operations type: write

Memory scope type: global

Threads started!

Done.

Operations performed: 262144 (735089.70 ops/sec)

2048.00 MB transferred (5742.89 MB/sec)

Test execution summary:

    total time:                          0.3566s

    total number of events:              262144

    total time taken by event execution: 4.9494

    per-request statistics:

         min:                                  0.00ms

         avg:                                  0.02ms

         max:                                 47.02ms

         approx.  95 percentile:               0.00ms

Threads fairness:

    events (avg/stddev):           16384.0000/1285.55

    execution time (avg/stddev):   0.3093/0.01

[root@centos179min bin]#

5.文件(file)的输入和输出测试

[root@centos179min bin]# ./sysbench --test=fileio --file-total-size=20G prepare    --创建20G的文件大小

sysbench 0.4.10:  multi-threaded system evaluation benchmark

128 files, 163840Kb each, 20480Mb total

Creating files for the test...

[root@centos179min bin]#  ./sysbench --test=fileio --file-total-size=20G --file-test-mode=rndwr run

--读和写20G的文件输出IO值

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 1

Extra file open flags: 0

128 files, 160Mb each

20Gb total file size

Block size 16Kb

Number of random requests for random IO: 10000

Read/Write ratio for combined random IO test: 1.50

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

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

Using synchronous I/O mode

Doing random write test

Threads started!

Done.

Operations performed:  0 Read, 10000 Write, 12800 Other = 22800 Total

Read 0b  Written 156.25Mb  Total transferred 156.25Mb  (1.858Mb/sec)

  118.91 Requests/sec executed

Test execution summary:

    total time:                          84.0955s

    total number of events:              10000

    total time taken by event execution: 0.2631

    per-request statistics:

         min:                                  0.01ms

         avg:                                  0.03ms

         max:                                 11.91ms

         approx.  95 percentile:               0.05ms

Threads fairness:

    events (avg/stddev):           10000.0000/0.00

    execution time (avg/stddev):   0.2631/0.00

[root@centos179min bin]#  ./sysbench --test=fileio --file-total-size=20G --file-test-mode=rndwr cleanup     --删除刚才创建的文件

6.数据库mysql的测试

[root@centos179min bin]# ./sysbench  --test=oltp help           --查看帮助

sysbench 0.4.10:  multi-threaded system evaluation benchmark

oltp options:

  --oltp-test-mode=STRING         test type to use {simple,complex,nontrx,sp} [complex]

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

  --oltp-point-selects=N          number of point selects [10]

  --oltp-simple-ranges=N          number of simple ranges [1]

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

  --oltp-auto-inc=[on|off]        whether AUTO_INCREMENT (or equivalent) should be used on id column [on]

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

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

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)

  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]

Compiled-in database drivers:

  mysql - MySQL driver

mysql options:

  --mysql-host=[LIST,...]       MySQL server host [localhost]

  --mysql-port=N                MySQL server port [3306]

  --mysql-socket=STRING         MySQL socket

  --mysql-user=STRING           MySQL user [sbtest]

  --mysql-password=STRING       MySQL password []

  --mysql-db=STRING             MySQL database name [sbtest]

  --mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]

  --mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]

  --mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]

  --myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]

  --mysql-create-options=STRING additional options passed to CREATE TABLE []

[root@centos179min bin]# ./sysbench --test=oltp --mysql-user=root --mysql-password=system --mysql-db=tong --oltp-table-name=sbtest --mysql-table-engine=innodb --oltp-table-size=1000000 prepare          --准备测试的数据

sysbench 0.4.10:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql

Creating table 'sbtest'...

Creating 1000000 records in table 'sbtest'...

[root@centos179min bin]# ./sysbench --test=oltp --mysql-port=3306 --mysql-user=root --mysql-password=system --mysql-db=tong --oltp-table-name=sbtest --mysql-table-engine=innodb  --oltp-table-size=1000000 --max-requests=1000 --num-threads=100 run    --测试mysql的并发和压力

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 99 times)

Running the test with following options:

Number of threads: 100

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 1000

Threads started!

Done.

OLTP test statistics:

    queries performed:

        read:                            14000

        write:                           5000

        other:                           2000

        total:                           21000

    transactions:                        1000   (143.56 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 19000  (2727.71 per sec.)

    other operations:                    2000   (287.13 per sec.)

Test execution summary:

    total time:                          6.9655s

    total number of events:              1000

    total time taken by event execution: 683.5913

    per-request statistics:

         min:                                 88.12ms

         avg:                                683.59ms

         max:                               3688.93ms

         approx.  95 percentile:            1362.33ms

Threads fairness:

    events (avg/stddev):           10.0000/1.23

    execution time (avg/stddev):   6.8359/0.10

[root@centos179min bin]#

©著作权归作者所有：来自51CTO博客作者z597011036的原创作品，谢绝转载，否则将追究法律责任

mysqlsysbench性能测试工具mysql