Mysql 壓力測試工具sysbench
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]#
下載：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]#
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1799120/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- NewSQL資料庫壓力測試工具系列——SysbenchSQL資料庫
- mysql壓力測試在青雲PCIE盤sysbench版本MySql
- MySQL DB Server 上面安裝 sysbench 作壓力測試MySqlServer
- MySQL壓力測試中定製sysbench的Lua模板MySql
- [資料庫]000 - ?Sysbench 資料庫壓力測試工具資料庫
- mysql叢集壓力測試在京東雲盤：sysbench版本MySql
- mysql壓力測試在京東雲ssd雲盤sysbench版本MySql
- mysql之 sysbench1.0.3 安裝與系統壓力測試MySql
- 壓測工具之Sysbench1.0(2_MySQL壓測)MySql
- MySQL基準壓力測試工具MySQLSlapMySql
- MySQL基準測試工具sysbenchMySql
- 【Mysql】sysbench基準測試工具MySql
- 壓力測試工具
- MySQL 效能壓測工具-sysbench，從入門到自定義測試項MySql
- mysql效能測試工具之sysbench薦MySql
- 使用sysbench壓力測試MySQL（一）(r11筆記第3天)MySql筆記
- mysql之 mysql資料庫壓力測試工具（mysqlslap）MySql資料庫
- 壓力測試sysbench安裝及引數介紹
- sysbench 測試MySQLMySql
- 壓力測試工具之FIO
- 壓力測試工具之mysqlslapMySql
- 網站壓力測試工具網站
- sysbench測試mysql oltpMySql
- 壓測工具之Sysbench(1_系統壓測)
- 詳解 MySQL 基準測試和 sysbench 工具MySql
- apache ab壓力測試工具-批次壓測指令碼Apache指令碼
- 10大主流壓力測試工具
- HTTP/FTP壓力測試工具siegeHTTPFTP
- Nginx壓力測試工具之WebBenchNginxWeb
- Web網站壓力測試工具Web網站
- 超實用壓力測試工具－ab工具
- 使用sysbench測試Mysql效能MySql
- 讓測試事半功倍軟體壓力測試工具分享，壓力測試報告怎麼收費?測試報告
- 【工具】基準測試工具之sysbench
- 三種web效能壓力測試工具Web
- 分散式TCP壓力測試工具 tcpcopy分散式TCP
- 併發網站壓力測試工具網站
- MySQL字元函式的壓力測試MySql字元函式