sysbench工具使用

简介

第一个

sysbench是一款压力测试工具，可以测试系统的硬件性能，也可以用来对数据库进行基准测试。sysbench 支持的测试有CPU运算性能测试、内存分配及传输速度测试、磁盘IO性能测试、POSIX线程性能测试、互斥性测试测试、数据库性能测试(OLTP基准测试)。目前支持的数据库主要是MySQL数据库和PG数据库。

在新服务器上线时，建议对服务器的性能做一次测试，最好与既往的同类型的服务器的性能测试报表做一个横线比较，发现潜在问题。及新机器上线前，对服务器做一次体检。

对数据库而言，我们可以通过sysbench工具实现对数据库的基准测试。在现在的系统架构中，前端都比较容易弹性水平拓展，数据库相对较难，因此，基准测试对数据库具有很重要的作用。而对数据库的基准测试的作用，就是分析在当前的配置下（包括硬件配置、OS、数据库设置等），数据库的性能表现，从而找出MySQL的性能阈值，并根据实际系统的要求调整配置。

参考：https://www.cnblogs.com/xuliuzai/p/11243376.html

第二个

sysbench是跨平台的基准测试工具，支持多线程，支持多种数据库；主要包括以下几种测试：

cpu性能
磁盘io性能
调度程序性能
内存分配及传输速度
POSIX线程性能
数据库性能(OLTP基准测试)

参考：https://blog.csdn.net/qq_36357820/article/details/80079012

安装

centos的一种：

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

其他略

参数介绍

执行sysbench –help，可以看到sysbench的详细使用方法。

先看下参数说明：

[root@mysql ~]# sysbench help
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

FATAL: Cannot find benchmark 'help': no such built-in test, file or module
[root@mysql ~]# sysbench --help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
  --threads=N                     number of threads to use [1]
  --events=N                      limit for total number of events [0]
  --time=N                        limit for total execution time in seconds [10]
  --forced-shutdown=STRING        number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
  --thread-stack-size=SIZE        size of stack per thread [64K]
  --rate=N                        average transactions rate. 0 for unlimited rate [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. []
  --debug[=on|off]                print more debugging info [off]
  --validate[=on|off]             perform validation checks where possible [off]
  --help[=on|off]                 print help and exit [off]
  --version[=on|off]              print version and exit [off]
  --config-file=FILENAME          File containing command line options
  --tx-rate=N                     deprecated alias for --rate [0]
  --max-requests=N                deprecated alias for --events [0]
  --max-time=N                    deprecated alias for --time [0]
  --num-threads=N                 deprecated alias for --threads [1]

Pseudo-Random Numbers Generator options:
  --rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
  --rand-spec-iter=N number of iterations used for numbers generation [12]
  --rand-spec-pct=N  percentage of values to be treated as 'special' (for special distribution) [1]
  --rand-spec-res=N  percentage of 'special' values to use (for special distribution) [75]
  --rand-seed=N      seed for random number generator. When 0, the current time is used as a RNG seed. [0]
  --rand-pareto-h=N  parameter h for pareto distribution [0.2]

Log options:
  --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]

  --percentile=N       percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
  --histogram[=on|off] print latency histogram in report [off]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers) [mysql]
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug[=on|off] print database-specific debug information [off]


Compiled-in database drivers:
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql options:
  --mysql-host=[LIST,...]          MySQL server host [localhost]
  --mysql-port=[LIST,...]          MySQL server port [3306]
  --mysql-socket=[LIST,...]        MySQL socket
  --mysql-user=STRING              MySQL user [sbtest]
  --mysql-password=STRING          MySQL password []
  --mysql-db=STRING                MySQL database name [sbtest]
  --mysql-ssl[=on|off]             use SSL connections, if available in the client library [off]
  --mysql-ssl-cipher=STRING        use specific cipher for SSL connections []
  --mysql-compression[=on|off]     use compression, if available in the client library [off]
  --mysql-debug[=on|off]           trace all client library calls [off]
  --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
  --mysql-dry-run[=on|off]         Dry run, pretend that all MySQL client API calls are successful without executing them [off]

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]

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

See 'sysbench <testname> help' for a list of options for each test.

可以看到支持 io,cpu,内存，线程 mysql ，PostgreSQL等的测试。

sysbench的基本语法如下：

sysbench [options]… [testname] [command]

下面说明实际使用中，常用的参数和命令。

（1）command

command是sysbench要执行的命令，包括prepare、run和cleanup，顾名思义，

• prepare是为测试提前准备数据，
• run是执行正式的测试，
• cleanup是在测试完成后对数据库进行清理。

（2）testname

testname指定了要进行的测试，在老版本的sysbench中，可以通过–test参数指定测试的脚本；而在新版本中，–test参数已经声明为废弃，可以不使用–test，而是直接指定脚本。

例如，如下两种方法效果是一样的：

sysbench --test=./tests/include/oltp_legacy/oltp.lua
sysbench ./tests/include/oltp_legacy/oltp.lua

测试时使用的脚本为lua脚本，可以使用sysbench自带脚本，也可以自己开发。对于大多数应用，使用sysbench自带的脚本就足够了。不同版本的sysbench中，lua脚本的位置可能不同，可以自己在sysbench路径下使用find命令搜索oltp.lua。P.S.：大多数数据服务都是oltp类型的，如果你不了解什么是oltp，那么大概率你的数据服务就是oltp类型的。

（3）options

sysbench的参数有很多，其中比较常用的包括：

MySQL连接信息参数

--mysql-host：MySQL服务器主机名，默认localhost；如果在本机上使用localhost报错，提示无法连接MySQL服务器，改成本机的IP地址应该就可以了。
--mysql-port：MySQL服务器端口，默认3306
--mysql-user：用户名
--mysql-password：密码

MySQL执行参数

--oltp-test-mode：执行模式，包括simple、nontrx和complex，默认是complex。simple模式下只测试简单的查询；nontrx不仅测试查询，还测试插入更新等，但是不使用事务；complex模式下测试最全面，会测试增删改查，而且会使用事务。可以根据自己的需要选择测试模式。
--oltp-tables-count：测试的表数量，根据实际情况选择
--oltp-table-size：测试的表的大小，根据实际情况选择
--threads：客户端的并发连接数
--time：测试执行的时间，单位是秒，该值不要太短，可以选择120
--report-interval：生成报告的时间间隔，单位是秒，如10

原文链接：https://blog.csdn.net/qq_36357820/article/details/80079012

1. mysql性能测试

比如测试一个数据库：

先新建 一个库

：tssysbench

create database tssysbench;

准备压测数据：

sysbench /usr/share/sysbench/oltp_insert.lua  --mysql-host=172.30.11.36  --mysql-port=3308 --mysql-user=root  --mysql-password='XXXX' --mysql-db=tssysbench --db-driver=mysql  --tables=15  --table-size=1000000  --report-interval=10 --threads=128   --time=180 prepare

具体参数细节,参考上文《参数介绍》章节。

简单来说，指定数据库名称，用户，密码，端口，驱动， 新建表数量，每个表的大小，笔者一开始指定15个表每个表一千万数据，导致好久没跑出来，所以大小看着设定即可。

会过段时间，执行完成以后，可以登录客户端查看数据量。

[root@mysql sysbench]# sysbench /usr/share/sysbench/oltp_insert.lua  --mysql-host=172.30.11.36  --mysql-port=3308 --mysql-user=root  --mysql-password='XXXX' --mysql-db=tssysbench --db-driver=mysql  --tables=15  --table-size=1000000  --report-interval=10 --threads=128   --time=180 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest14'...
Creating table 'sbtest1'...
Creating table 'sbtest12'...
Creating table 'sbtest7'...
Creating table 'sbtest3'...
Creating table 'sbtest10'...
Creating table 'sbtest4'...
Creating table 'sbtest6'...
Creating table 'sbtest2'...
Creating table 'sbtest5'...
Creating table 'sbtest15'...
Creating table 'sbtest11'...
Creating table 'sbtest13'...
Creating table 'sbtest8'...
Creating table 'sbtest9'...
Inserting 1000000 records into 'sbtest12'
Inserting 1000000 records into 'sbtest9'
Inserting 1000000 records into 'sbtest8'
Inserting 1000000 records into 'sbtest10'
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest1'
Inserting 1000000 records into 'sbtest5'
Inserting 1000000 records into 'sbtest13'
Inserting 1000000 records into 'sbtest6'
Inserting 1000000 records into 'sbtest14'
Inserting 1000000 records into 'sbtest15'
Inserting 1000000 records into 'sbtest7'
Inserting 1000000 records into 'sbtest3'
Inserting 1000000 records into 'sbtest11'
Inserting 1000000 records into 'sbtest4'
[root@mysql sysbench]#

运行测试

sysbench /usr/share/sysbench/oltp_insert.lua   --mysql-host=172.30.11.36  --mysql-port=3308 --mysql-user=root  --mysql-password='XXXX' --mysql-db=tssysbench --db-driver=mysql  --tables=15  --table-size=1000000   --report-interval=10 --threads=128   --time=120 run >> /mha/xtrabackup/mysysbench_run.log

笔者把测试的数据导出到了日志里面

也需要执行一会。

分析日期

TPS/QPS：衡量吞吐量。

• TPS：每秒事务处理量(TransactionPerSecond);
• 每秒查询率QPS是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准;
• 响应时间：包括平均响应时间、最小响应时间、最大响应时间、时间百分比等，其中时间百分比参考意义较大，如前95%的请求的最大响应时间;
• 并发量：同时处理的查询请求的数量;

贴一个日志：

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 128
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 128 tps: 630.87 qps: 630.87 (r/w/o: 0.00/630.87/0.00) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 128 tps: 526.55 qps: 526.55 (r/w/o: 0.00/526.55/0.00) lat (ms,95%): 909.80 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 128 tps: 412.10 qps: 412.10 (r/w/o: 0.00/412.10/0.00) lat (ms,95%): 1304.21 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 128 tps: 571.30 qps: 571.30 (r/w/o: 0.00/571.30/0.00) lat (ms,95%): 669.89 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 128 tps: 667.40 qps: 667.40 (r/w/o: 0.00/667.40/0.00) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 128 tps: 2812.30 qps: 2812.30 (r/w/o: 0.00/2812.30/0.00) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 128 tps: 5188.80 qps: 5188.80 (r/w/o: 0.00/5188.80/0.00) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 128 tps: 5414.21 qps: 5414.21 (r/w/o: 0.00/5414.21/0.00) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 128 tps: 5368.10 qps: 5368.10 (r/w/o: 0.00/5368.10/0.00) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 128 tps: 2434.20 qps: 2434.20 (r/w/o: 0.00/2434.20/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 128 tps: 1929.69 qps: 1929.69 (r/w/o: 0.00/1929.69/0.00) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 128 tps: 2319.89 qps: 2319.89 (r/w/o: 0.00/2319.89/0.00) lat (ms,95%): 215.44 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 128 tps: 4585.88 qps: 4585.88 (r/w/o: 0.00/4585.88/0.00) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 128 tps: 4795.96 qps: 4795.96 (r/w/o: 0.00/4795.96/0.00) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 128 tps: 4286.50 qps: 4286.50 (r/w/o: 0.00/4286.50/0.00) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 128 tps: 2449.90 qps: 2449.90 (r/w/o: 0.00/2449.90/0.00) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 128 tps: 924.70 qps: 924.70 (r/w/o: 0.00/924.70/0.00) lat (ms,95%): 397.39 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 128 tps: 1893.22 qps: 1893.22 (r/w/o: 0.00/1893.22/0.00) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           472243
        other:                           0
        total:                           472243
    transactions:                        472243 (2621.74 per sec.)
    queries:                             472243 (2621.74 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.1244s
    total number of events:              472243

Latency (ms):
         min:                                    6.57
         avg:                                   48.81
         max:                                 3830.01
         95th percentile:                      176.73
         sum:                             23049838.07

Threads fairness:
    events (avg/stddev):           3689.3984/18.91
    execution time (avg/stddev):   180.0769/0.03

看这里

 transactions:                        472243 (2621.74 per sec.)
    queries:                             472243 (2621.74 per sec.)

平均TPS:2621.74 per sec;

平均QPS:2621.74 per sec；

前95%最大响应时间：

95th percentile:                      176.73

默认是毫秒，所以，感觉很拉胯这个服务器，之前有个老师给我们展示他的运行 结果比这个大了好几倍，应该是有项目在上面运行吧。

2. 测试CPU

参考这个博客：https://blog.csdn.net/fycghy0803/article/details/102723284

sysbench cpu run：表示使用一个thread执行一次cpu性能测试;测试结果如下:

[root@mysql ~]# sysbench cpu run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Prime numbers limit: 10000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   915.47

General statistics:
    total time:                          10.0004s
    total number of events:              9157    每秒可以处理事件数量

Latency (ms):
         min:                                    1.08 每个事件最小处理时间(毫秒)
         avg:                                    1.09 平均每个事件的处理时间
         max:                                    1.47  //最大处理时间
         95th percentile:                        1.12  95%的事件的处理时间
         sum:                                 9993.66  总共的处理时间

Threads fairness:
    events (avg/stddev):           9157.0000/0.00  //每个线程处理的总事件；标准方差（单线程无意义）
    execution time (avg/stddev):   9.9937/0.00     //每个线程处理的总时间，标准方差(单线程无意义)

[root@mysql ~]#

sysbench --threads=2 cpu run: 使用两个线程测试cpu

…

参考博客：

https://blog.csdn.net/fycghy0803/article/details/102723284

https://blog.csdn.net/weixin_34122548/article/details/92936901

3. 测试线程

#老版本：
sysbench  --test=threads --num-threads=500 --thread-yields=100 --thread-locks=4 run
#新版本：
sysbench threads --threads=500 --thread-yields=100 --thread-locks=4 run

[root@mysql ~]# sysbench threads --threads=500 --thread-yields=100 --thread-locks=4 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 500
Initializing random number generator from current time


Initializing worker threads...

Threads started!


General statistics:
    total time:                          10.0380s
    total number of events:              121123

Latency (ms):
         min:                                    0.02
         avg:                                   41.37
         max:                                  513.99
         95th percentile:                      173.58
         sum:                              5011248.76

Threads fairness:
    events (avg/stddev):           242.2460/20.33
    execution time (avg/stddev):   10.0225/0.01

[root@mysql ~]#

参数和上面类似

参考博客：https://www.jianshu.com/p/7a80898c6866

参考博客

https://blog.csdn.net/u012423685/article/details/108617102

https://blog.csdn.net/oahz4699092zhao/article/details/53332105

https://www.jianshu.com/p/7a80898c6866

https://www.cnblogs.com/xuliuzai/p/11243376.html

https://blog.csdn.net/qq_36357820/article/details/80079012

https://blog.csdn.net/weixin_34122548/article/details/92936901

https://blog.csdn.net/fycghy0803/article/details/102723284

https://www.cnblogs.com/xiayi/p/9638878.html

https://www.imooc.com/article/41758