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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sysbench 壓力測試
- NewSQL資料庫壓力測試工具系列——SysbenchSQL資料庫
- [資料庫]000 - ?Sysbench 資料庫壓力測試工具資料庫
- mysql之 sysbench1.0.3 安裝與系統壓力測試MySql
- MySQL 效能壓測工具-sysbench，從入門到自定義測試項MySql
- 使用sysbench壓力測試MySQL（一）(r11筆記第3天)MySql筆記
- sysbench 測試MySQLMySql
- apache ab壓力測試工具-批次壓測指令碼Apache指令碼
- 10大主流壓力測試工具
- 軟體壓力測試知識分享，2022好用壓力測試工具有哪些?
- 超實用壓力測試工具－ab工具
- 讓測試事半功倍軟體壓力測試工具分享，壓力測試報告怎麼收費?測試報告
- sysbench壓測實踐
- Linux下使用壓力測試工具stressLinux
- nodejs版的websocket壓力測試工具NodeJSWeb
- Linux系統壓力測試工具(命令列工具)Linux命令列
- 壓力測試
- MySQL 5.7和8.0 MHA架構下sysbench壓測MySql架構
- 開源的負載測試/壓力測試工具 NBomber負載
- 軟體壓力測試流程和測試工具分享，讓你寫壓力測試報告再也不愁測試報告
- 通過sysbench工具實現MySQL資料庫的效能測試MySql資料庫
- 10大主流壓力測試工具推薦
- mysql ,tidb sysbench 測試結果記錄MySqlTiDB
- MACOSXApacheab壓力測試MacApache
- ORACLE壓力測試Oracle
- laravel壓力測試Laravel
- 資料庫基準測試工具 sysbench資料庫
- ActiveMQ壓力測試工具(emqtt_benchmark和jmeter)MQQTJMeter
- 壓力測試工具ab - Apache HTTP server benchmarking toolApacheHTTPServer
- sysbench壓測Oracle 12COracle
- sysbench壓測Oracle 11gOracle
- 利用sysbench進行MySQL OLTP基準測試MySql
- 使用sysbench測試mysql及postgresql(完整版)MySql
- 用雲壓力測試工具，如何完成一次測試任務
- Apache下壓力測試工具ab安裝與使用Apache
- 實現Python壓力測試工具｜Python 主題月Python
- （一）效能測試（壓力測試、負載測試）負載
- mysql之 sysbench0.4.12資料庫效能測試MySql資料庫
- RestCloud測試平臺，支援壓力測試RESTCloud