MySQL测试工具 sysbench简单安装使用测试~~

2022-03-09 03:31:42

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"

[root@localhost software]# ls
os_zabbix-agent sysbench-0.4.12.14.tar.gz
[root@localhost software]# tar xvf sysbench-0.4.12.14.tar.gz

安装必要的依赖库
[root@localhost sysbench-0.4.12.14]# yum -y install make automake libtool pkgconfig libaio-devel vim-common

开始安装：
[root@localhost software]# cd sysbench-0.4.12.14
[root@localhost 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

遇到报错：
[root@localhost 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
查阅资料，解决方法;
[root@localhost sysbench-0.4.12.14]# cat configure.ac | grep AC_LIB_PREFIX

AC_LIB_PREFIX()

    注释掉这个函数就可以了

[root@localhost 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

[root@localhost sysbench-0.4.12.14]# ./configure && make && make install
[root@localhost software]# sysbench --version
sysbench 0.4.12.10
安装完成！
3、测试MySQL的OLTP基准测试
[root@localhost 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简单生成基准测试表
[root@localhost 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测试结果
[root@localhost 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     --平均执行时间/标准偏差

码农公寓

AC_LIB_PREFIX()

相关文章