使用sysbench进行压力测试
环境说明
之前尝试使用sysbench1.0+版本进行测试，但是装上去后不知道什么原因（可能是我环境不干净），出现Illegal instruction错误，放弃1.0，使用0.4.12版本，成功完成安装测试。
操作系统
[root@lyucan lib]# cat /etc/redhat-release
CentOS Linux release 7.1.1503 (Core)
mysql安装路径
[root@lyucan mysql]# pwd
/usr/local/mysql
[root@lyucan mysql]# ls -l
total 72
drwxr-xr-x. 2 root root 4096 Apr 9 01:01 bin
-rw-r--r--. 1 7161 31415 17987 Dec 28 11:46 COPYING
drwxr-xr-x. 2 root root 52 Apr 9 01:01 docs
drwxr-xr-x. 3 root root 4096 Apr 9 01:01 include
drwxr-xr-x. 5 root root 4096 Apr 9 01:01 lib
drwxr-xr-x. 4 root root 28 Apr 9 01:01 man
-rw-r-----. 1 mysql mysql 27793 Apr 9 19:45 mysqld.err
-rw-r-----. 1 mysql mysql 6 Apr 9 19:45 mysqld.pid
-rw-r--r--. 1 7161 31415 2478 Dec 28 11:46 README
drwxr-xr-x. 28 root root 4096 Apr 9 01:01 share
drwxr-xr-x. 2 root root 86 Apr 9 01:01 support-files
mysql数据文件路径
[root@lyucan mysql]# pwd
/data/mysql
[root@lyucan mysql]# ls -l
total 122928
-rw-r-----. 1 mysql mysql 56 Apr 9 01:07 auto.cnf
-rw-r-----. 1 mysql mysql 367 Apr 9 19:45 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Apr 9 19:58 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Apr 9 19:58 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Apr 9 01:07 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Apr 9 19:56 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Apr 9 01:07 mysql
drwxr-x---. 2 mysql mysql 8192 Apr 9 01:07 performance_schema
drwxr-x---. 2 mysql mysql 8192 Apr 9 01:07 sys
-rw-rw-rw-. 1 mysql mysql 57 Apr 9 19:46 t1_bak
-rw-rw-rw-. 1 mysql mysql 57 Apr 9 19:52 t1_bak_1
drwxr-x---. 2 mysql mysql 79 Apr 9 19:58 test
-rw-rw-rw-. 1 mysql mysql 346 Apr 9 19:45 user_bak
安装sysbench
软件地址 http://qncdn-open.baihuzi.com/files/sysbench-0.4.12.14.tar.gz
解压sysbench
[root@lyucan ~]# tar -xf sysbench-0.4.12.14.tar.gz
安装依赖库
[root@lyucan mysql]# yum install automake libtool -y
编译安装sysbench
[root@lyucan ~]# cd sysbench-0.4.12.14/
[root@lyucan sysbench-1.0]# ./configure --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/
[root@lyucan sysbench-1.0]# make && make install
安装完成后，可能出现下面的问题
[root@lyucan ~]# sysbench --help
sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
是由于找不到mysql 的libmysqlclient.so.20 文件导致的，执行下面命令可解决上述问题
[root@lyucan lib]# export LD_LIBRARY_PATH='/usr/local/mysql/lib/' ##这里的lib路径根据实际情况修改
开始测试
创建测试数据库
mysql> create database dbtest;
Query OK, 1 row affected (0.00 sec)
测试的时候可以把线程数设置成机器的线程个数：
#查看物理cpu个数
[root@lyucan test]# grep "physical id" /proc/cpuinfo | sort -u | wc -l
1
#查看核心数量
[root@lyucan test]# grep "core id" /proc/cpuinfo | sort -u | wc -l
1
#查看线程数量
[root@lyucan test]# grep "processor" /proc/cpuinfo | sort -u | wc -l
4
可以使用帮助命令查看测试不同的性能所需要的参数，cpu，内存等；
[root@lyucan ~]# sysbench --test=cpu help
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
cpu options:
--cpu-max-prime=N upper limit for primes generator [10000]
--cpu-max-prime=N 用来选项指定最大的素数，具体参数可以根据CPU的性能来设置，默认为10000
cpu测试实例
[root@lyucan ~]# sysbench --test=cpu --cpu-max-prime=10000 --num-threads=4 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored
Doing CPU performance benchmark
Primer numbers limit: 10000
Threads started!
Done.
General statistics:
total time: 3.2167s
total number of events: 10000
total time taken by event execution: 12.8329
response time:
min: 1.11ms
avg: 1.28ms
max: 2.56ms
approx. 95 percentile: 1.57ms
Threads fairness:
events (avg/stddev): 2500.0000/133.41
execution time (avg/stddev): 3.2082/0.00
内存
[root@lyucan ~]# sysbench --test=memory help
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
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]
参数详解：
--memory-block-size=SIZE 测试内存块的大小，默认为1K
--memory-total-size=SIZE 数据传输的总大小，默认为100G
--memory-scope=STRING 内存访问的范围，包括全局和本地范围，默认为global
--memory-hugetlb=[on|off] 是否从HugeTLB池分配内存的开关，默认为off
--memory-oper=STRING 内存操作的类型，包括read, write, none，默认为write
--memory-access-mode=STRING 内存访问模式，包括seq,rnd两种模式，默认为seq
内存测试实例：
[root@lyucan ~]# sysbench --test=memory --num-threads=2 --memory-block-size=8192 --memory-total-size=1G run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 2
Random number generator seed is 0 and will be ignored
Doing memory operations speed test
Memory block size: 8K
Memory transfer size: 1024M
Memory operations type: write
Memory scope type: global
Threads started!
Done.
Operations performed: 131072 (190211.43 ops/sec)
1024.00 MB transferred (1486.03 MB/sec)
General statistics:
total time: 0.6891s
total number of events: 131072
total time taken by event execution: 0.6851
response time:
min: 0.00ms
avg: 0.01ms
max: 0.10ms
approx. 95 percentile: 0.01ms
Threads fairness:
events (avg/stddev): 65536.0000/1391.00
execution time (avg/stddev): 0.3425/0.01
oltp事务
[root@lyucan ~]# sysbench --test=oltp help
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
oltp options:
--oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
--oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]
--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-avoid-deadlocks=[on|off] generate update keys in increasing order to avoid deadlocks [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-use-in-statement=N Use IN-statement with 10 PK lookups per query [0]
--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-comment-string=STRING comment on table []
--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]
--oltp-point-select-mysql-handler=[on|off]Use MySQL HANDLER for point select [off]
--oltp-point-select-all-cols=[on|off] select all columns for the point-select query [off]
--oltp-secondary=[on|off] Use a secondary index in place of the PRIMARY index [off]
--oltp-num-partitions=N Number of partitions used for test table [0]
--oltp-num-tables=N Number of test tables [1]
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 []
参数详解：
--memory-block-size=SIZE 测试内存块的大小，默认为1K
--oltp-user-delay-min=N 每个请求最短等待时间。单位是ms。默认是0
--oltp-user-delay-max=N 每个请求最长等待时间。单位是ms。默认是0
--oltp-table-name=STRING 测试时使用到的表名。默认是sbtest
--oltp-table-size=N 测试表的记录数。默认是10000
--oltp-dist-type=STRING 分布的随机数{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是special
--oltp-dist-iter=N 产生数的迭代次数。默认是12
--oltp-dist-pct=N 特殊分布的百分比值 (for special distribution)。 默认是1
--oltp-dist-res=N ‘special’的百分比值。默认是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
pgsql - PostgreSQL driver
mysql options:
--mysql-host=[LIST,...] MySQL server 地址 默认[localhost]
--mysql-port=N MySQL server 端口 默认[3306]
--mysql-socket=STRING MySQL socket
--mysql-user=STRING MySQL 用户名 默认[sbtest]
--mysql-password=STRING MySQL 密码 默认[]
--mysql-db=STRING MySQL database name [sbtest]
--mysql-table-engine=STRING 使用的存储引擎包括{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 []
pgsql options:
--pgsql-host=STRING PostgreSQL server host [localhost]
--pgsql-port=N PostgreSQL server port [5432]
--pgsql-user=STRING PostgreSQL user [sbtest]
--pgsql-password=STRING PostgreSQL password []
--pgsql-db=STRING PostgreSQL database name [sbtest]
事务测试实例：
#准备数据
[root@lyucan ~]# sysbench --test=oltp --mysql-db=dbtest --mysql-table-engine=innodb --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=echo123. --num-threads=4 --oltp-num-tables=10 --oltp-table-size=500000 prepare
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Creating table 'sbtest6'...
Creating table 'sbtest5'...
Creating table 'sbtest'...
Creating table 'sbtest4'...
Creating table 'sbtest1'...
Creating table 'sbtest3'...
Creating table 'sbtest2'...
Creating table 'sbtest7'...
Creating table 'sbtest9'...
Creating table 'sbtest8'...
Creating 500000 records in table 'sbtest3'...
Creating 500000 records in table 'sbtest6'...
Creating 500000 records in table 'sbtest'...
Creating 500000 records in table 'sbtest8'...
Creating 500000 records in table 'sbtest7'...
Creating 500000 records in table 'sbtest1'...
Creating 500000 records in table 'sbtest9'...
Creating 500000 records in table 'sbtest4'...
Creating 500000 records in table 'sbtest5'...
Creating 500000 records in table 'sbtest2'...
#运行测试
[root@lyucan ~]# sysbench --test=oltp --mysql-db=dbtest --mysql-table-engine=innodb --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=echo123. --num-threads=4 --oltp-num-tables=10 --oltp-table-size=500000 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored
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 10000
Using 10 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000 #读总数
write: 50000 #写总数
other: 20000 #其他操作总数
total: 210000 #全部总数
transactions: 10000 (565.77 per sec.) #总事务数（每秒处理事务数）
deadlocks: 0 (0.00 per sec.) #总死锁（每秒死锁）
read/write requests: 190000 (10749.72 per sec.) #总读写请求（每秒读写请求）
other operations: 20000 (1131.55 per sec.) #其他操作
General statistics:
total time: 17.6749s #总耗时
total number of events: 10000 #总事务数
total time taken by event execution: 70.5829 #所有事务耗时总时间
response time:
min: 4.21ms
avg: 7.06ms
max: 189.03ms
approx. 95 percentile: 9.24ms
Threads fairness:
events (avg/stddev): 2500.0000/2.45
execution time (avg/stddev): 17.6457/0.00
#清理数据
[root@lyucan ~]# sysbench --test=oltp --mysql-db=dbtest --mysql-table-engine=innodb --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password=echo123. --num-threads=4 --oltp-num-tables=10 --oltp-table-size=500000 cleanup
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Dropping table 'sbtest'...
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Done.
io
[root@lyucan ~]# sysbench --test=fileio help
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
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-extra-flags=STRING additional flags to use on opening 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]
参数详解：
--file-num=N 代表生成测试文件的数量，默认为128。
--file-block-size=N 测试时所使用文件块的大小，如果想磁盘针对innodb存储引擎进行测试，可以将其设置为16384，即innodb存储引擎页的大小。默认为16384。
--file-total-size=SIZE 创建测试文件的总大小，默认为2G大小。
--file-test-mode=STRING 文件测试模式，包含：seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)。
--file-io-mode=STRING 文件操作的模式，sync（同步）,async（异步）,fastmmap（快速mmap）,slowmmap（慢速mmap），默认为sync同步模式。
--file-async-backlog=N 对应每个线程队列的异步操作数，默认为128。
--file-extra-flags=STRING 打开文件时的选项，这是与API相关的参数。
--file-fsync-freq=N 执行fsync()函数的频率。fsync主要是同步磁盘文件，因为可能有系统和磁盘缓冲的关系。 0代表不使用fsync函数。默认值为100。
--file-fsync-all=[on|off] 每执行完一次写操作，就执行一次fsync。默认为off。
--file-fsync-end=[on|off] 在测试结束时执行fsync函数。默认为on。
--file-fsync-mode=STRING文件同步函数的选择，同样是和API相关的参数，由于多个操作系统对于fdatasync支持不同，因此不建议使用fdatasync。默认为fsync。
--file-merged-requests=N 大多情况下，合并可能的IO的请求数，默认为0。
--file-rw-ratio=N 测试时的读写比例，默认时为1.5，即可3：2。
io测试实例：
#准备数据
[root@lyucan test]# sysbench --test=fileio --file-total-size=2G --file-num=8 --file-test-mode=rndrw --num-threads=4 prepare
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
8 files, 262144Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
2147483648 bytes written in 18.36 seconds (111.52 MB/sec).
#运行测试
[root@lyucan test]# sysbench --test=fileio --file-total-size=2G --file-num=8 --file-test-mode=rndrw --num-threads=4 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored
Extra file open flags: 0
8 files, 256Mb each
2Gb 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 r/w test
Threads started!
Done.
Operations performed: 6000 reads, 4000 writes, 800 Other = 10800 Total
Read 93.75Mb Written 62.5Mb Total transferred 156.25Mb (10.638Mb/sec)
680.81 Requests/sec executed
General statistics:
total time: 14.6885s
total number of events: 10000
total time taken by event execution: 0.2046
response time:
min: 0.01ms
avg: 0.02ms
max: 0.14ms
approx. 95 percentile: 0.04ms
Threads fairness:
events (avg/stddev): 2500.0000/27.23
execution time (avg/stddev): 0.0511/0.00
#清理数据
[root@lyucan test]# sysbench --test=fileio --file-total-size=2G --file-num=8 --file-test-mode=rndrw --num-threads=4 cleanup
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Removing test files...
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com