Mysql 壓力測試工具sysbench

下載：sysbench-0.4.10.tar.gz
/>
安裝步驟：

[root@mysqlsource]# tar -xvf sysbench-0.4.10.tar.gz
[root@mysqlsource]# cd sysbench-0.4.10
[root@mysqlsource sysbench-0.4.10]# mkdir ddsysbench

複製系統的libtool到sysbench的source code目錄下：
[root@mysqlsource sysbench-0.4.10]# cp /usr/bin/libtool /tools/sysbench-0.4.12

[root@mysqlsource sysbench-0.4.10]# ./configure --prefix=/root/sysbench-0.4.10/ddsysbench/ --with-mysql-includes=/mysql5.6.22/include/mysql --with-mysql-libs=/mysql5.6.22/lib64/mysql

./configure --prefix=/mysql/tools/sysbench \ --指定安裝目錄
--with-mysql-includes=/mysql/include \ --指定mysql資料庫的include目錄
--with-mysql-libs=/mysql/lib --指定mysql資料庫的lib目錄

[root@mysqlsource sysbench-0.4.10]# make && make install

報錯：
[root@mysqlsource ddsysbench]# ./bin/sysbench --help
./bin/sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
解決：
[root@mysqlsource sysbench-0.4.10]# export LD_LIBRARY_PATH=/mysql5.6.22/lib64/mysql/
[root@mysqlsource sysbench-0.4.10]# cd ddsysbench/
[root@mysqlsource ddsysbench]# ./bin/sysbench --help
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@mysqlsource ddsysbench]#

開始測試：
建表：
bin/sysbench --test=oltp \ --測試型別資料庫oltp
--mysql-host=127.0.0.1 \ --mysql主機
--mysql-port=3421 \ --mysql埠
--mysql-user=root \ --mysql登陸使用者
--mysql-password=$password \ --mysql密碼
--mysql-db=test \ --mysql測試資料庫名
--mysql-socket=/var/mysql.sock \ --socket位置
--oltp-table-name=test \ --mysql測試表名
--mysql-table-engine=innodb \ --mysql表使用儲存引擎
--oltp-table-size=1000000 prepare --表資料量

[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock --mysql-table-engine=innodb --oltp-table-name=test --oltp-table-size=1000000 prepare
sysbench 0.4.10: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'test'...
Creating 1000000 records in table 'test'...
[root@mysqlsource ddsysbench]#

模擬訪問：
bin/sysbench --test=oltp \
--mysql-host=127.0.0.1 \ --mysql主機
--mysql-port=3421 \ --mysql埠
--mysql-user=root \ --mysql登陸使用者
--mysql-password=$password \ --mysql密碼
--mysql-db=test \ --mysql測試資料庫名
--mysql-socket=/var/mysql.sock \ --socket位置
--oltp-table-name=test \ --mysql測試表名
--mysql-table-engine=innodb \ --mysql表使用儲存引擎
--max-requests=100000 \ --測試請求次數
--max-time=600 \ --測試最長時間
--num-threads=100 run --併發執行緒數

[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock --mysql-table-engine=innodb --oltp-table-name=test --max-requests=100000 --max-time=600 --num-threads=100 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 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 100000
Threads started!
Done.

OLTP test statistics:
queries performed:
read: 1647128
write: 542106
other: 217655
total: 2406889
transactions: 100003 (374.65 per sec.)
deadlocks: 17649 (66.12 per sec.)
read/write requests: 2189234 (8201.73 per sec.)
other operations: 217655 (815.42 per sec.)

Test execution summary:
total time: 266.9234s
total number of events: 100003
total time taken by event execution: 26677.3311
per-request statistics:
min: 4.92ms
avg: 266.77ms
max: 5116.57ms
approx. 95 percentile: 774.87ms

Threads fairness:
events (avg/stddev): 1000.0300/31.11
execution time (avg/stddev): 266.7733/0.06

清理資料：
[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock --mysql-table-engine=innodb --oltp-table-name=test cleanup
sysbench 0.4.10: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Dropping table 'test'...
Done.
[root@mysqlsource ddsysbench]#

測試例項：
表屬性compress對insert速度的影響。
第一步準備：
建立表結構
bin/sysbench --test=oltp \ --測試型別資料庫oltp
--mysql-host=127.0.0.1 \ --mysql主機
--mysql-port=3421 \ --mysql埠
--mysql-user=root \ --mysql登陸使用者
--mysql-password=$password \ --mysql密碼
--mysql-db=test \ --mysql測試資料庫名
--mysql-socket=/var/mysql.sock \ --socket位置
--oltp-table-name=sbtest \ --mysql測試表名
--mysql-table-engine=innodb \ --mysql表使用儲存引擎
--oltp-table-size=1 prepare --表資料量
將表名修改為uncompressed
rename table sbtest to uncompressed;
再次建立表sbtest，並將表名修改為compressed
rename table sbtest to compressed;
將表改為compressed
alter table compressed ROW_FORMAT=compressed;
第二步：向表uncompressed表中新增記錄
bin/sysbench --test=oltp \
--mysql-host=127.0.0.1 \ --mysql主機
--mysql-port=3421 \ --mysql埠
--mysql-user=root \ --mysql登陸使用者
--mysql-password=$password \ --mysql密碼
--mysql-db=test \ --mysql測試資料庫名
--mysql-socket=/var/mysql.sock \ --socket位置
--oltp-table-name=uncompressed \ --mysql測試表名
--mysql-table-engine=innodb \ --mysql表使用儲存引擎
--oltp-nontrx-mode=insert \
--oltp-test-mode=nontrx \
--max-requests=1000000 \
--max-time=600 \
--num-threads=100 run

測試結果：
[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123ql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock --mysql-name=test --oltp-table-size=1 pr-table-engine=innodb --oltp-table-name=uncompressed --oltp-nontrx-mode=insert --oltp-test-mode=nontrx --max-requests=1000000 --max-time=600 --num-threads=100 run
sysbench 0.4.10: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100

Doing OLTP test.
Running non-transactional 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 1000000
Threads started!
Done.

OLTP test statistics:
queries performed:
read: 0
write: 1000279
other: 0
total: 1000279
transactions: 1000279 (9492.38 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1000279 (9492.38 per sec.)
other operations: 0 (0.00 per sec.)

Test execution summary:
total time: 105.3771s
total number of events: 1000279
total time taken by event execution: 10534.3501
per-request statistics:
min: 1.28ms
avg: 10.53ms
max: 1001.98ms
approx. 95 percentile: 22.96ms

Threads fairness:
events (avg/stddev): 10002.7900/49.66
execution time (avg/stddev): 105.3435/0.01

[root@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --mysql-socket=/mysql5.6.22/tmp/mysql.sock --mysql-table-engine=innodb --oltp-table-name=compressed --oltp-nontrx-mode=insert --oltp-test-mode=nontrx --max-requests=1000000 --max-time=600 --num-threads=100 run
sysbench 0.4.10: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 100

Doing OLTP test.
Running non-transactional 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 1000000
Threads started!
Time limit exceeded, exiting...
(last message repeated 99 times)
Done.

OLTP test statistics:
queries performed:
read: 0
write: 986965
other: 0
total: 986965
transactions: 986965 (1643.82 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 986965 (1643.82 per sec.)
other operations: 0 (0.00 per sec.)

Test execution summary:
total time: 600.4096s
total number of events: 986965
total time taken by event execution: 60028.4337
per-request statistics:
min: 1.05ms
avg: 60.82ms
max: 17930.03ms
approx. 95 percentile: 223.06ms

Threads fairness:
events (avg/stddev): 9869.6500/52.19
execution time (avg/stddev): 600.2843/0.07

安裝mysql：

[root@mysqlsource mysql-5.6.22]# cmake .
-DCMAKE_INSTALL_PREFIX=/mysql5.6.22/
-DINSTALL_SBINDIR=/mysql5.6.22/sbin
-DMYSQL_DATADIR=/mysql5.6.22/data/
-DSYSCONFDIR=/mysql5.6.22/etc/
-DINSTALL_PLUGINDIR=lib64/mysql/plugin
-DINSTALL_MANDIR=share/man
-DINSTALL_SHAREDIR=share
-DINSTALL_LIBDIR=lib64/mysql
-DINSTALL_INCLUDEDIR=include/mysql
-DINSTALL_INFODIR=share/info
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0
-DMYSQL_UNIX_ADDR=/mysql5.6.22/tmp/mysqld.sock
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_EMBEDDED_SERVER=1
-DMYSQL_TCP_PORT=3306

均要在basedir下執行指令碼，不然會提示找不到檔案。
[root@mysqlsource mysql5.6.22]# ./scripts/mysql_install_db --user=mysql --basedir=/mysql5.6.22/ --datadir=/mysql5.6.22/data/ --defaults-file=/mysql5.6.22/etc/my.cnf --user=mysql

[root@mysqlsource mysql5.6.22]# ./bin/mysqld_safe --defaults-file=/mysql5.6.22/etc/my.cnf &
[root@mysqlsource bin]# ./mysqld_safe --defaults-file=/mysql5.6.22/etc/my.cnf &（會有報錯）
[1] 3301
[root@mysqlsource bin]# 150909 09:57:25 mysqld_safe Logging to '/mysql5.6.22/mysql-error.log'.
150909 09:57:25 mysqld_safe The file /mysql5.6.22//mysql5.6.22/sbin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information
^C

[root@mysqlsource bin]# ./mysqladmin shutdown -u root -p --socket=/mysql5.6.22/tmp/mysql.sock

工具使用：
[root@mysqlsource ddsysbench]# ./bin/sysbench --help
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@mysqlsource ddsysbench]# ./bin/sysbench --test=oltp --help
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@mysqlsource ddsysbench]# ./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@mysqlsource ddsysbench]#

Mysql 壓力測試工具sysbench

相關文章