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