sysbench内置测试脚本测试

此文介绍的是sysbench对mysql的测试，必备mysql环境和sysbench环境，缺少sysbench请移步：

https://blog.csdn.net/pattan/article/details/81233747

 

sysbench 中的测试类型大致可以分成内置的测试，lua脚本自定义的测试：
1、内置：fileio 、cpu 、memory 、threads 、 mutex 
2、lua脚本自定义型：

sysbench 自身内置了一些测试脚本放在了安装目录下，本文重点介绍脚本测试：

我的linux查看sysbench测试脚本如下：

[root@localhost sysbench]# pwd -P
/usr/local/sysbench/share/sysbench
[root@localhost sysbench]# ll
total 60
-rwxr-xr-x 1 root root  1452 Jul 27 10:39 bulk_insert.lua
-rw-r--r-- 1 root root 14369 Jul 27 10:39 oltp_common.lua
-rwxr-xr-x 1 root root  1290 Jul 27 10:39 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 Jul 27 10:39 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 Jul 27 10:39 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 Jul 27 10:39 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 Jul 27 10:39 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 Jul 27 10:39 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 Jul 27 10:39 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 Jul 27 10:39 oltp_write_only.lua
-rwxr-xr-x 1 root root  1919 Jul 27 10:39 select_random_points.lua
-rwxr-xr-x 1 root root  2118 Jul 27 10:39 select_random_ranges.lua
drwxr-xr-x 4 root root    49 Jul 27 10:39 tests

lua脚本测试步骤：

一、建立测试数据库(这部分属于mysql简单操作)：

在此先说明一下，我的mysql配置：用户名root，密码root，并且已经开启了服务。

建立空的测试数据库，用于测试：
mysql -uroot -proot
mysql> create database sysbenchtest;
# 连接数据库
# 创建测试空库 sysbenchtest 显示 Query OK, 1 row affected 表示成功了。

查看数据库
mysql> show databases;

# 创建成功！
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sysbenchtest       |

好了，记得退出数据库；
mysql> exit;

二、sysbench连接数据库和测试：

lua脚本目录:/usr/local/sysbench/share/sysbench (我的sysbench安装位置是/usr/local/sysbench)

cd /usr/local/sysbench/share/sysbench

# 进入脚本目录

开始测试三部曲：prepare run cleanup (准备工作 测试开始 清除测试数据)

step1.prepare:

sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysbenchtest oltp_insert prepare

 

step2.run:

sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysbenchtest oltp_insert run

# 测试已经开始，数据库正在压力写入，请耐心等待几秒钟... 测试结束，测试数据如下:

Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            0
        write:                           8467
        other:                           0
        total:                           8467
    transactions:                        8467   (846.42 per sec.)
    queries:                             8467   (846.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0004s
    total number of events:              8467

Latency (ms):
         min:                                    0.52
         avg:                                    1.18
         max:                                   40.81
         95th percentile:                        2.26
         sum:                                 9957.20

Threads fairness:
    events (avg/stddev):           8467.0000/0.00
    execution time (avg/stddev):   9.9572/0.00

 

step3.cleanup:

sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sysbenchtest oltp_insert cleanup

# cleanup实质为删除测试表，注意别选错表了。

至此，测试完成！

sysbench --help
# sysbench的使用帮助如下，本文没有介绍fileio 、cpu 、memory 、threads 、 mutex 的使用，谢谢:

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.