MySQL测试工具 sysbench简单安装使用测试~~
1、 sysbench简介
sysbench是一款多线程系统压测工具。它可以根据影响数据库服务性能的各种因素来评估系统的性能。例如，可以用来测试软件I/O、操作系统调度器、内存分配和传输速度、POSIX线程，以及数据库服务器等。Sysbench支持Lua脚本语言，Lua对于各种测试场景的设置可以非常灵活。Sysbench是一种全能测试工具，支持MySQL、操作系统和硬件的硬件测试
虽然mysql默认的有mysqlslap这个性能测试工具，跟sysbench比还是sysbench的功能齐全，测试准确，是最有用的MySQL工具之一。
2、 下载安装
https://dev.mysql.com/downloads/benchmarks.html
或者
wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
[[email protected] software]# ls
os_zabbix-agent sysbench-0.4.12.14.tar.gz
[[email protected] software]# tar xvf sysbench-0.4.12.14.tar.gz
安装必要的依赖库
[[email protected] sysbench-0.4.12.14]# yum -y install make automake libtool pkgconfig libaio-devel vim-common
开始安装：
[[email protected] software]# cd sysbench-0.4.12.14
[[email protected] sysbench-0.4.12.14]# ls
acinclude.m4 autom4te.cache config.log configure.ac INSTALL m4 Makefile.in README TODO
aclocal.m4 ChangeLog config.status COPYING install-sh Makefile missing README-WIN.txt
autogen.sh config configure doc libtool Makefile.am mkinstalldirs sysbench
遇到报错：
[[email protected] sysbench-0.4.12.14]# ./autogen.sh
./autogen.sh: running `libtoolize --copy --force'
libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.
libtoolize: copying file `config/ltmain.sh'
libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.
libtoolize: copying file `m4/libtool.m4'
libtoolize: copying file `m4/ltoptions.m4'
libtoolize: copying file `m4/ltsugar.m4'
libtoolize: copying file `m4/ltversion.m4'
libtoolize: copying file `m4/lt~obsolete.m4'
./autogen.sh: running `aclocal -I m4'
./autogen.sh: running `autoheader'
./autogen.sh: running `automake -c --foreign --add-missing'
./autogen.sh: running `autoconf'
configure.ac:49: error: possibly undefined macro: AC_LIB_PREFIX
If this token and others are legitimate, please use m4_pattern_allow.
See the Autoconf documentation.Can't execute autoconf
查阅资料，解决方法;
[[email protected] sysbench-0.4.12.14]# cat configure.ac | grep AC_LIB_PREFIX
AC_LIB_PREFIX()
注释掉这个函数就可以了
[[email protected] sysbench-0.4.12.14]# ./autogen.sh
./autogen.sh: running `libtoolize --copy --force'
libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.
libtoolize: copying file `config/ltmain.sh'
libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.
libtoolize: copying file `m4/libtool.m4'
libtoolize: copying file `m4/ltoptions.m4'
libtoolize: copying file `m4/ltsugar.m4'
libtoolize: copying file `m4/ltversion.m4'
libtoolize: copying file `m4/lt~obsolete.m4'
./autogen.sh: running `aclocal -I m4'
./autogen.sh: running `autoheader'
./autogen.sh: running `automake -c --foreign --add-missing'
./autogen.sh: running `autoconf'
Libtoolized with: libtoolize (GNU libtool) 2.4.2
Automade with: automake (GNU automake) 1.13.4
Configured with: autoconf (GNU Autoconf) 2.69
[[email protected] sysbench-0.4.12.14]# ./configure && make && make install
[[email protected] software]# sysbench --version
sysbench 0.4.12.10
安装完成！
3、 测试MySQL的OLTP基准测试
[[email protected] software]# sysbench --help
Usage:
sysbench [general-options]... --test= [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]
--forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off]
--thread-stack-size=SIZE size of stack per thread [32K]
--init-rng=[on|off] initialize random number generator [off]
--seed-rng=N seed for random number generator, ignored when 0 [0]
--tx-rate=N target transaction rate (tps) [0]
--tx-jitter=N target transaction variation, in microseconds [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
--report-checkpoints=[LIST,...]dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
--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
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [4]
--percentile=N percentile rank of query response times to count [95]
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= help' for a list of options for each test.
这里可以看到sysbench支持文件I/O，CPU，内存，线程，mutex互斥锁，OLTP等基准测试
3.1简单生成基准测试表
[[email protected] sysbench-0.4.12.14]# sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-password='123456' --mysql-socket='/tmp/mysql.sock' prepare
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 100000 records in table 'sbtest'...
各种命令格式都在帮助里面可以找到，默认是MySQL数据库，这里已经创建了一个10万行的测试表
3.2测试结果
[[email protected] sysbench-0.4.12.14]# sysbench --test=oltp --oltp-table-size=100000 --mysql-db=test --mysql-user=root --mysql-password='123456' --mysql-socket='/tmp/mysql.sock' --max-time=60 --oltp-read-only=on --max-requests=100 --num-threads=8 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored
Doing OLTP test.
Running mixed OLTP test
Doing read-only 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 100
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 1400 --读总数
write: 0 --写总数
other: 200 --其他操作
total: 1600 --全部
transactions: 100 (610.21 per sec.) --事务总数
deadlocks: 0 (0.00 per sec.) --死锁
read/write requests: 1400 (8542.96 per sec.) --读写请求
other operations: 200 (1220.42 per sec.) --其他操作
General statistics: --常规统计
total time: 0.1639s --所有时间
total number of events: 100 --所有的事务数
total time taken by event execution: 1.2855 --所有事务耗时相加
response time: --响应时间
min: 5.48ms --最小耗时
avg: 12.85ms --平均耗时
max: 28.09ms --最大耗时
approx. 95 percentile: 19.89ms --超95%平均耗时
Threads fairness:
events (avg/stddev): 12.5000/0.50 --平均处理事务数/标准偏差
execution time (avg/stddev): 0.1607/0.00 --平均执行时间/标准偏差