sysbench用法

基础：

sysbench压力测试过程(分为三个阶段):
1、prepare阶段 这个阶段是用来做准备的、比如说建立好测试用的表、并向表中填充数据。
2、run阶段 这个阶段是才是去跑压力测试的SQL
3、cleanup阶段 这个阶段是去清除数据的、也就是prepare阶段初始化好的表要都drop掉

sysbench的测试类型可分为内置的和lua脚本自定义的测试:
1、内置:fileio、cpu、memory、threads、mutex
2、lua脚本自定义:在/usr/local/share/sysbench路径下，包括:
bulk_insert.lua oltp_insert.lua oltp_read_write.lua oltp_write_only.lua tests
oltp_common.lua oltp_point_select.lua oltp_update_index.lua select_random_points.lua
oltp_delete.lua oltp_read_only.lua oltp_update_non_index.lua select_random_ranges.lua

sysbench使用方法:
1、内置:
(1) fileio
[root@localhost ~]# sysbench fileio help

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
fileio options:
–file-num=N number of files to create [128]
–file-block-size=N block size to use in all IO operations [16384]
–file-total-size=SIZE total size of files to create [2G]
–file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
–file-io-mode=STRING file operations mode {sync,async,mmap} [sync]
–file-async-backlog=N number of asynchronous operatons to queue per thread [128]
–file-extra-flags=[LIST,…] list of additional flags to use to open files {sync,dsync,direct} []
–file-fsync-freq=N do fsync() after this number of requests (0 - don’t use fsync()) [100]
–file-fsync-all[=on|off] do fsync() after each write operation [off]
–file-fsync-end[=on|off] do fsync() at the end of test [on]
–file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
–file-merged-requests=N merge at most this number of IO requests if possible (0 - don’t merge) [0]
–file-rw-ratio=N reads/writes ratio for combined test [1.5]

(2) cpu
[root@localhost ~]# sysbench cpu help

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
cpu options:
–cpu-max-prime=N upper limit for primes generator [10000]

(3) memory
[root@localhost ~]# sysbench memory help

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
memory options:
–memory-block-size=SIZE size of memory block for test [1K]
–memory-total-size=SIZE total size of data to transfer [100G]
–memory-scope=STRING memory access scope {global,local} [global]
–memory-hugetlb[=on|off] allocate memory from HugeTLB pool [off]
–memory-oper=STRING type of memory operations {read, write, none} [write]
–memory-access-mode=STRING memory access mode {seq,rnd} [seq]

(4) threads
[root@localhost ~]# sysbench threads help

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
threads options:
–thread-yields=N number of yields to do per request [1000]
–thread-locks=N number of locks per thread [8]

(5) mutex
[root@localhost ~]# sysbench mutex help

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
mutex options:
–mutex-num=N total size of mutex array [4096]
–mutex-locks=N number of mutex locks to do per thread [50000]
–mutex-loops=N number of empty loops to do outside mutex lock [10000]

2、lua脚本自定义
(1) oltp_insert
[root@localhost ~]# sysbench oltp_insert help

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
oltp_insert options:
–auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
–create_secondary[=on|off] Create a secondary index in addition to the PRIMARY KEY [on]
–delete_inserts=N Number of DELETE/INSERT combinations per transaction [1]
–distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1]
–index_updates=N Number of UPDATE index queries per transaction [1]
–mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
–non_index_updates=N Number of UPDATE non-index queries per transaction [1]
–order_ranges=N Number of SELECT ORDER BY queries per transaction [1]
–pgsql_variant=STRING Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is ‘redshift’. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
–point_selects=N Number of point SELECT queries per transaction [10]
–range_selects[=on|off] Enable/disable all range SELECT queries [on]
–range_size=N Range size for range SELECT queries [100]
–secondary[=on|off] Use a secondary index in place of the PRIMARY KEY [off]
–simple_ranges=N Number of simple range SELECT queries per transaction [1]
–skip_trx[=on|off] Don’t start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
–sum_ranges=N Number of SELECT SUM() queries per transaction [1]
–table_size=N Number of rows per table [10000]
–tables=N Number of tables [1]

(2) read_write
[root@localhost ~]# sysbench oltp_read_write help

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)
oltp_read_write options:
–auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
–create_secondary[=on|off] Create a secondary index in addition to the PRIMARY KEY [on]
–delete_inserts=N Number of DELETE/INSERT combinations per transaction [1]
–distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1]
–index_updates=N Number of UPDATE index queries per transaction [1]
–mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
–non_index_updates=N Number of UPDATE non-index queries per transaction [1]
–order_ranges=N Number of SELECT ORDER BY queries per transaction [1]
–pgsql_variant=STRING Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is ‘redshift’. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
–point_selects=N Number of point SELECT queries per transaction [10]
–range_selects[=on|off] Enable/disable all range SELECT queries [on]
–range_size=N Range size for range SELECT queries [100]
–secondary[=on|off] Use a secondary index in place of the PRIMARY KEY [off]
–simple_ranges=N Number of simple range SELECT queries per transaction [1]
–skip_trx[=on|off] Don’t start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
–sum_ranges=N Number of SELECT SUM() queries per transaction [1]
–table_size=N Number of rows per table [10000]
–tables=N Number of tables [1]

…
其他的查看/usr/local/share/sysbench目录中的脚本

使用例子：

1、测试insert(并发16个线程)
prepare:

[root@localhost ~]# sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=123456 --mysql-db=test oltp_insert prepare

run:

[root@localhost ~]# sysbench --threads=16 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=123456 --mysql-db=test oltp_insert run

cleanup:

[root@localhost ~]# sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=123456 --mysql-db=test oltp_insert cleanup

2、测试read_write(并发16个线程)
prepare:

[root@localhost ~]# sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=123456 --mysql-db=test --table_size=100000 --tables=20 oltp_read_write prepare

run:

[root@localhost ~]# sysbench --threads=16 --time=30 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=123456 --mysql-db=test oltp_read_write run

cleanup:

[root@localhost ~]# sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test --mysql-password=123456 --mysql-db=test --tables=20 oltp_read_write cleanup